1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
| using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
private SQLiteConnection db;
public Form1()
{
InitializeComponent();
}
private void ConnectToDB()
{
string dbPath = Path.Combine(Environment.CurrentDirectory, "testdb.sqlite");
string connString = string.Format("Data Source={0}", dbPath);
db = new SQLiteConnection(connString);
db.Open();
// удалить, если нужно чтобы информация добавлялась к существующей...
SQLiteCommand command = new SQLiteCommand("DROP TABLE IF EXISTS Log;", db);
command.ExecuteNonQuery();
SQLiteCommand command2 = new SQLiteCommand("CREATE TABLE IF NOT EXISTS Log (callsign TEXT NOT NULL, data TEXT NOT NULL, time TEXT NOT NULL, op TEXT NOT NULL, qth TEXT NOT NULL, rstin TEXT NOT NULL, rstout TEXT NOT NULL, mode TEXT NOT NULL, freq TEXT NOT NULL, qslin TEXT NOT NULL, qslout TEXT NOT NULL, qsltype TEXT NOT NULL, locator TEXT NOT NULL, state TEXT NOT NULL, iota TEXT NOT NULL, notes TEXT NOT NULL);", db);
command2.ExecuteNonQuery();
}
private async void button1_Click(object sender, EventArgs e)
{
const string InsertDataString = "INSERT INTO [Log] (callsign, data, time, op, qth, rstin, rstout, mode, freq, qslin, qslout, qsltype, locator, state, iota, notes) VALUES (@callsign, @data, @time, @op, @qth, @rstin, @rstout, @mode, @freq, @qslin, @qslout, @qsltype, @locator, @state, @iota, @notes)";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
button1.Enabled = false;
Stopwatch sw = new Stopwatch();
sw.Start();
IProgress<int> progress = new Progress<int>(p => progressBar1.Value = p);
await Task.Run(async () =>
{
// to make .NET Core happy about encodings
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
string[] load = File.ReadAllLines(openFileDialog1.FileName, Encoding.GetEncoding("Windows-1251"));
ConnectToDB();
SQLiteCommand cmd = db.CreateCommand();
cmd.CommandText = InsertDataString;
Regex regex = new Regex(@"(?<=>).*?(?=<)", RegexOptions.Compiled);
using (SQLiteTransaction trans = db.BeginTransaction())
{
for (int i = 0; i < load.Length; i++)
{
var matches = regex.Matches(load[i]);
if (matches.Count >= 10) // prevent crash
{
string mycal = matches[0].Value;
string call = matches[1].Value;
string date = matches[2].Value;
string time = matches[3].Value;
string band = matches[4].Value;
string mode = matches[5].Value;
string rx = matches[6].Value;
string tx = matches[7].Value;
string name = matches[8].Value;
string qth = matches[9].Value;
string data_save = DateTime.ParseExact(date, "yyyyMMdd", CultureInfo.InvariantCulture).ToString("dd.MM.yyyy");
string corr_time = DateTime.ParseExact(time, "HHmm", CultureInfo.InvariantCulture).ToString("HH:mm");
cmd.Parameters.Add("@callsign", DbType.String).Value = call;
cmd.Parameters.Add("@data", DbType.String).Value = data_save;
cmd.Parameters.Add("@time", DbType.String).Value = corr_time;
cmd.Parameters.Add("@op", DbType.String).Value = name;
cmd.Parameters.Add("@qth", DbType.String).Value = qth;
cmd.Parameters.Add("@rstin", DbType.String).Value = rx;
cmd.Parameters.Add("@rstout", DbType.String).Value = tx;
cmd.Parameters.Add("@mode", DbType.String).Value = mode;
cmd.Parameters.Add("@freq", DbType.String).Value = band;
cmd.Parameters.Add("@qslin", DbType.String).Value = "";
cmd.Parameters.Add("@qslout", DbType.String).Value = "Send";
cmd.Parameters.Add("@qsltype", DbType.String).Value = "E-QSL";
cmd.Parameters.Add("@locator", DbType.String).Value = "";
cmd.Parameters.Add("@state", DbType.String).Value = "";
cmd.Parameters.Add("@iota", DbType.String).Value = "";
cmd.Parameters.Add("@notes", DbType.String).Value = "";
await cmd.ExecuteNonQueryAsync();
progress.Report((int)((i + 1) * 100f / load.Length));
}
}
trans.Commit();
}
});
// просто вывод отладочной инфы..
sw.Stop();
textBox1.AppendText("Added data to Table [Log], spent " + sw.Elapsed + Environment.NewLine);
SQLiteCommand cmd2 = db.CreateCommand();
cmd2.CommandText = "SELECT COUNT(*) from Log";
textBox1.AppendText("Table [Log] now has " + cmd2.ExecuteScalar() + " rows" + Environment.NewLine);
db.Close();
}
//Application.Restart();
}
}
} |
Для таких вещей есть gist.github.com
Оформи код
