1: using System;
2: using System.Collections.Generic;
3: using System.Configuration;
4: using System.Data;
5: using System.Data.SQLite;
6: using System.Linq;
7:
8: namespace Ia.Cl.Model.Db
9: {
10: ////////////////////////////////////////////////////////////////////////////
11:
12: /// <summary publish="true">
13: /// SQLite support class.
14: /// </summary>
15: /// <remarks>
16: /// Copyright © 2008-2016 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
17: ///
18: /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
19: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
20: ///
21: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
22: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
23: ///
24: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
25: ///
26: /// Copyright notice: This notice may not be removed or altered from any source distribution.
27: /// </remarks>
28:
29: public class Sqlite
30: {
31: private static string connectionString;
32:
33: ////////////////////////////////////////////////////////////////////////////
34:
35: /// <summary>
36: ///
37: /// </summary>
38: public Sqlite()
39: {
40: connectionString = ConfigurationManager.ConnectionStrings["SqLiteConnectionString"].ToString();
41: }
42:
43: ////////////////////////////////////////////////////////////////////////////
44:
45: /// <summary>
46: ///
47: /// </summary>
48: public Sqlite(string dataSource)
49: {
50: string version;
51:
52: version = "3";
53:
54: connectionString = "Data Source=" + dataSource + ";Version=" + version + ";";
55: }
56:
57: /*
58: ////////////////////////////////////////////////////////////////////////////
59:
60: /// <summary>
61: ///
62: /// </summary>
63: public Sqlite(string dataSource, string version)
64: {
65: version = "3";
66:
67: connectionString = "Data Source=" + dataSource + ";Version=" + version + ";";
68: }
69:
70: ////////////////////////////////////////////////////////////////////////////
71:
72: /// <summary>
73: ///
74: /// </summary>
75: public Sqlite(string dataSource, string version, string password)
76: {
77: version = "3";
78:
79:
80: connectionString = "Data Source=" + dataSource + ";Version=" + version + ";Password=" + password + ";";
81: }
82: */
83:
84: ////////////////////////////////////////////////////////////////////////////
85: ////////////////////////////////////////////////////////////////////////////
86:
87: /// <summary>
88: ///
89: /// </summary>
90: public void Sql(string sql)
91: {
92: SQLiteCommand sco;
93:
94: using (var sc = new SQLiteConnection(connectionString))
95: {
96: sc.Open();
97:
98: sco = new SQLiteCommand(sql, sc);
99: sco.CommandType = CommandType.Text; // default
100:
101: sco.ExecuteNonQuery();
102: //sc.Close();
103: }
104:
105: GC.Collect();
106: }
107:
108: ////////////////////////////////////////////////////////////////////////////
109:
110: /// <summary>
111: ///
112: /// </summary>
113: public void Sql(List<string> sqlList)
114: {
115: SQLiteCommand sco;
116:
117: using (var sc = new SQLiteConnection(connectionString))
118: {
119: sc.Open();
120:
121: foreach (string sql in sqlList)
122: {
123: sco = new SQLiteCommand(sql, sc);
124: sco.CommandType = CommandType.Text; // default
125:
126: sco.ExecuteNonQuery();
127: }
128:
129: //sc.Close();
130: }
131:
132: GC.Collect();
133: }
134:
135: ////////////////////////////////////////////////////////////////////////////
136:
137: /// <summary>
138: /// Copy dataTable values to SQLite database. Not that we will change all Id primary keys to _id
139: /// </summary>
140: public int SqlBulkCopy(DataTable dataTable, out Ia.Cl.Model.Result result)
141: {
142: var results = new List<int>();
143:
144: result = new Ia.Cl.Model.Result();
145:
146: using (SQLiteConnection sc = new SQLiteConnection(connectionString))
147: {
148: sc.Open();
149:
150: using (var transaction = sc.BeginTransaction())
151: {
152: using (var cmd = sc.CreateCommand())
153: {
154: if (dataTable != null && dataTable.Rows.Count > 0)
155: {
156: cmd.CommandText = CommandText(dataTable);
157:
158: if (!string.IsNullOrEmpty(cmd.CommandText))
159: {
160: foreach (DataRow dr in dataTable.Rows)
161: {
162: foreach (DataColumn dc in dataTable.Columns)
163: {
164: // cmd.Parameters["@Name"] = user.Name;
165:
166: // For Andriod, the database tables should use the identifier _id for the primary key of the table.Several Android functions rely on this standard.
167: if (dc.ColumnName == "Id") cmd.Parameters.AddWithValue("@_id", dr[dc.ColumnName]);
168: else cmd.Parameters.AddWithValue("@" + dc.ColumnName, dr[dc.ColumnName]);
169: }
170:
171: results.Add(cmd.ExecuteNonQuery());
172: }
173:
174: result.AddSuccess("DataTable: " + dataTable.TableName + " had " + dataTable.Rows.Count + " records. ");
175: }
176: else
177: {
178: result.AddError("CommandText is null. ");
179: }
180: }
181: else
182: {
183: result.AddError("Data table: " + dataTable.TableName + " is null or row count is zero. ");
184: }
185: }
186:
187: transaction.Commit();
188: }
189: }
190:
191: GC.Collect();
192:
193: return results.Sum();
194: }
195:
196: ////////////////////////////////////////////////////////////////////////////
197:
198: private string CommandText(DataTable dataTable)
199: {
200: string s, names, values;
201:
202: if (dataTable != null && dataTable.Columns.Count > 0)
203: {
204: names = values = string.Empty;
205:
206: foreach (DataColumn dc in dataTable.Columns)
207: {
208: // ([Name]) VALUES (@Name);"
209: if (dc.ColumnName == "Id")
210: {
211: // For Andriod, the database tables should use the identifier _id for the primary key of the table.Several Android functions rely on this standard.
212: names += "[_id],";
213: values += "@_id,";
214: }
215: else
216: {
217: names += "[" + dc.ColumnName + "],";
218: values += "@" + dc.ColumnName + ",";
219: }
220: }
221:
222: if (!string.IsNullOrEmpty(names) && !string.IsNullOrEmpty(values))
223: {
224: names = names.Remove(names.Length - 1, 1); // remove last ','
225: values = values.Remove(values.Length - 1, 1); // remove last ','
226:
227: s = @"insert into [" + dataTable.TableName + "] (" + names + ") VALUES (" + values + ");";
228: }
229: else s = null;
230: }
231: else s = null;
232:
233: return s;
234: }
235:
236: ////////////////////////////////////////////////////////////////////////////
237: ////////////////////////////////////////////////////////////////////////////
238:
239: /// <summary>
240: ///
241: /// </summary>
242: public DataTable Select(string sql)
243: {
244: DataSet dataSet;
245: DataTable dataTable;
246: SQLiteCommand sco;
247: SQLiteDataAdapter da;
248:
249: dataTable = null;
250:
251: dataSet = new DataSet();
252: dataTable = new DataTable();
253: da = new SQLiteDataAdapter();
254:
255: using (var sc = new SQLiteConnection(connectionString))
256: {
257: sc.Open();
258:
259: sco = new SQLiteCommand(sql, sc);
260: sco.CommandType = CommandType.Text; // default
261:
262: da.SelectCommand = sco;
263: da.Fill(dataSet);
264:
265: dataTable = dataSet.Tables[0];
266: //sc.Close();
267: }
268:
269: GC.Collect();
270:
271: return dataTable;
272: }
273:
274: ////////////////////////////////////////////////////////////////////////////
275:
276: /// <summary>
277: ///
278: /// </summary>
279: public static string DateTimeFormat(DateTime dateTime)
280: {
281: // below: return an SQLite friendly string without "'"
282: string s;
283:
284: s = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); // '2007-01-01 10:00:00'
285:
286: return s;
287: }
288:
289: ////////////////////////////////////////////////////////////////////////////
290:
291: /// <summary>
292: ///
293: /// </summary>
294: public string Scalar(string sql)
295: {
296: string scalar;
297: DataSet ds;
298: DataTable dt;
299: SQLiteCommand sco;
300: SQLiteDataAdapter da;
301:
302: dt = null;
303:
304: ds = new DataSet();
305: dt = new DataTable();
306: da = new SQLiteDataAdapter();
307:
308: using (var sc = new SQLiteConnection(connectionString))
309: {
310: sc.Open();
311:
312: sco = new SQLiteCommand(sql, sc);
313: sco.CommandType = CommandType.Text; // default
314:
315: try { scalar = sco.ExecuteScalar().ToString(); }
316: catch { scalar = null; }
317:
318: //sc.Close();
319: }
320:
321: GC.Collect();
322:
323: return scalar;
324: }
325:
326: ////////////////////////////////////////////////////////////////////////////
327:
328: /// <summary>
329: /// Delete contents of a table.
330: /// </summary>
331: public void Delete(string dataTableName)
332: {
333: Sql("delete from table " + dataTableName);
334: }
335:
336: /*
337: ////////////////////////////////////////////////////////////////////////////
338:
339: /// <summary>
340: ///
341: /// </summary>
342: public int IntegerScalar(string sql)
343: {
344: // below: return a DataTable of result rows
345: int n;
346:
347: SQLiteConnection sc = null;
348: SQLiteCommand sco;
349:
350: sc = new SQLiteConnection(connectionString);
351: sco = new SQLiteCommand(sql, sc);
352:
353: sc.Open();
354:
355: try
356: {
357: n = (System.Int32)sco.ExecuteScalar();
358: }
359: catch (Exception)
360: {
361: n = 0;
362: }
363:
364: sc.Close();
365:
366: return n;
367: }
368:
369: ////////////////////////////////////////////////////////////////////////////
370:
371: /// <summary>
372: ///
373: /// </summary>
374: public int SmallIntegerScalar(string sql)
375: {
376: // below: return a DataTable of result rows
377: int n;
378:
379: SQLiteConnection sc = null;
380: SQLiteCommand sco;
381:
382: sc = new SQLiteConnection(connectionString);
383: sco = new SQLiteCommand(sql, sc);
384:
385: sc.Open();
386:
387: try
388: {
389: n = (System.Int16)sco.ExecuteScalar();
390: }
391: catch (Exception)
392: {
393: n = 0;
394: }
395:
396: sc.Close();
397:
398: return n;
399: }
400: */
401:
402: ////////////////////////////////////////////////////////////////////////////
403: ////////////////////////////////////////////////////////////////////////////
404:
405: // Create database file
406: //SQLiteConnection.CreateFile(dataSource); not needed see below
407:
408: /*
409:
410:
411: string password, sql, dataSource;
412: DataTable dt;
413: SQLiteCommand command;
414: SQLiteDataReader reader;
415: Ia.Cl.Model.Db.Sqlite s;
416: List<string> sqlList;
417:
418: password = "00000";
419: dataSource = "2.sqlite";
420:
421: s = new Ia.Cl.Model.Db.Sqlite(dataSource);
422: sqlList = new List<string>();
423:
424: sql = "drop table if exists highscores";
425: s.Sql(sql);
426:
427: sql = "create table highscores (name varchar(20), score int)";
428: s.Sql(sql);
429:
430: sql = "insert into highscores (name, score) values ('Me', 3000)";
431: s.Sql(sql);
432:
433: sqlList.Add("insert into highscores (name, score) values ('Me', 1)");
434: sqlList.Add("insert into highscores (name, score) values ('Me', 2)");
435: sqlList.Add("insert into highscores (name, score) values ('Me', 3)");
436: sqlList.Add("insert into highscores (name, score) values ('Me', 4)");
437: sqlList.Add("insert into highscores (name, score) values ('Me', 5)");
438: sqlList.Add("insert into highscores (name, score) values ('Me', 6)");
439: sqlList.Add("insert into highscores (name, score) values ('Me', 7)");
440: sqlList.Add("insert into highscores (name, score) values ('Me', 8)");
441: sqlList.Add("insert into highscores (name, score) values ('Me', 9)");
442: sqlList.Add("insert into highscores (name, score) values ('Me', 1)");
443: sqlList.Add("insert into highscores (name, score) values ('Me', 2)");
444: sqlList.Add("insert into highscores (name, score) values ('Me', 3)");
445:
446: s.Sql(sqlList);
447:
448: sql = "select * from highscores order by score desc";
449: dt = s.Select(sql);
450:
451:
452:
453:
454:
455: using (var connection = new SQLiteConnection("Data Source="+ dataSource + ";Version=3;Password=" + password + ";"))
456: {
457: connection.Open();
458:
459: // create
460: sql = "create table highscores (name varchar(20), score int)";
461: command = new SQLiteCommand(sql, connection);
462: command.ExecuteNonQuery();
463:
464: // insert
465: sql = "insert into highscores (name, score) values ('And I', 9001)";
466: command = new SQLiteCommand(sql, connection);
467: command.ExecuteNonQuery();
468:
469: // select
470: sql = "select * from highscores order by score desc";
471: command = new SQLiteCommand(sql, connection);
472: reader = command.ExecuteReader();
473:
474: while (reader.Read())
475: Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
476:
477: // connection.Close(); not needed in a using() block
478: }
479: */
480: }
481: }