1: using System;
2: using System.Web;
3: using System.Xml;
4: using System.Xml.Linq;
5: using System.Configuration;
6: using System.Data;
7: using System.Data.SqlServerCe;
8: using System.Collections;
9: using System.Text;
10: using System.Text.RegularExpressions;
11:
12: namespace Ia.Cs.Db
13: {
14: ////////////////////////////////////////////////////////////////////////////
15:
16: /// <summary publish="true">
17: /// SQL Server CE support class.
18: /// </summary>
19: /// <remarks>
20: /// Copyright © 2008-2013 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
21: ///
22: /// 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
23: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
24: ///
25: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
26: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
27: ///
28: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
29: ///
30: /// Copyright notice: This notice may not be removed or altered from any source distribution.
31: /// </remarks>
32: public class SqlServerCe
33: {
34: private string connectionString;
35: private ArrayList al, from_al, insert_al, delete_al;
36: private SqlCeConnection sc;
37: private SqlCeCommand sco;
38:
39: ////////////////////////////////////////////////////////////////////////////
40:
41: /// <summary>
42: ///
43: /// </summary>
44: public enum F
45: {
46: /// <summary/>
47: Bit,
48: /// <summary/>
49: In,
50: /// <summary/>
51: St,
52: /// <summary/>
53: Sdt,
54: /// <summary/>
55: Sdt_Keep_Latest,
56: /// <summary/>
57: Cr,
58: /// <summary/>
59: Up,
60: /// <summary/>
61: Ni
62: };
63:
64: ////////////////////////////////////////////////////////////////////////////
65:
66: /// <summary>
67: /// Initialize database with connection string from app.config.
68: /// </summary>
69: public SqlServerCe()
70: {
71: connectionString = ConfigurationManager.ConnectionStrings["SqlServerCeConnectionString"].ConnectionString;
72: }
73:
74: ////////////////////////////////////////////////////////////////////////////
75:
76: /// <summary>
77: /// Initialize database with connection string from app.config but with the passed database file name.
78: /// </summary>
79: public SqlServerCe(string _databaseName)
80: {
81: connectionString = Connection_String_Specific(_databaseName);
82: }
83:
84: ////////////////////////////////////////////////////////////////////////////
85:
86: /// <summary>
87: ///
88: /// </summary>
89: public bool Database_Exist()
90: {
91: // below: check if database exists
92: bool b;
93:
94: b = true;
95:
96: try
97: {
98: Sql("SELECT GETDATE() AS date");
99: }
100: catch (Exception ex)
101: {
102: if (ex.Message.Contains("The database file cannot be found. ")) b = false;
103: }
104:
105: return b;
106: }
107:
108: ////////////////////////////////////////////////////////////////////////////
109:
110: /// <summary>
111: ///
112: /// </summary>
113: public bool Create_Database()
114: {
115: // below:
116: bool b;
117: SqlCeEngine sce;
118:
119: b = true;
120:
121: try
122: {
123: sce = new SqlCeEngine(connectionString);
124: sce.CreateDatabase();
125: sce.Dispose();
126: }
127: catch (Exception)
128: {
129: b = false;
130: }
131:
132: return b;
133: }
134:
135: ////////////////////////////////////////////////////////////////////////////
136:
137: /// <summary>
138: /// Return the connection string given the database file name passed.
139: /// </summary>
140: private string Connection_String_Specific(string _database_file_name)
141: {
142: string s;
143:
144: // below: this will copy and replace the original database file name with the provided one.
145: s = Regex.Replace(connectionString, @"(\w+?\.sdf)", _database_file_name);
146:
147: return s;
148: }
149:
150: ////////////////////////////////////////////////////////////////////////////
151:
152: /// <summary>
153: ///
154: /// </summary>
155: public bool Sql(string sql)
156: {
157: // below: execute an SQL command
158: bool b = true;
159:
160: sc = new SqlCeConnection(connectionString);
161: sco = new SqlCeCommand();
162:
163: sco.CommandType = Server.HtmlEncode(CommandType.Text); // default
164: sco.CommandText = sql;
165: sco.Connection = sc;
166: sc.Open();
167: sco.ExecuteNonQuery();
168: sc.Close();
169:
170: return b;
171: }
172:
173: ////////////////////////////////////////////////////////////////////////////
174:
175: /// <summary>
176: /// Execute an SQL statement over a database file. The files is assumed to be in the data directory.
177: /// </summary>
178: public bool Sql(string sql, string database_file)
179: {
180: // below: execute an SQL command
181: bool b = true;
182:
183: sc = new SqlCeConnection(Connection_String_Specific(database_file));
184: sco = new SqlCeCommand();
185:
186: sco.CommandType = Server.HtmlEncode(CommandType.Text); // default
187: sco.CommandText = sql;
188: sco.Connection = sc;
189: sc.Open();
190: sco.ExecuteNonQuery();
191: sc.Close();
192:
193: return b;
194: }
195:
196: ////////////////////////////////////////////////////////////////////////////
197:
198: /// <summary>
199: ///
200: /// </summary>
201: public DataTable Sp(string sp_name, params object[] list)
202: {
203: // below: return data from a stored procedure
204:
205: // ERRORS
206:
207: object o;
208: int i;
209:
210: DataSet ds = new DataSet();
211: DataTable dt = new DataTable();
212: DataRow dr = null;
213: SqlCeDataReader sdr = null;
214:
215: try
216: {
217: sc = new SqlCeConnection(connectionString);
218: sco = new SqlCeCommand(sp_name, sc);
219:
220: sco.CommandType = CommandType.StoredProcedure;
221:
222: for (i = 0; i < list.Length; i += 2)
223: {
224: o = list[i];
225: if (o.GetType() == typeof(string))
226: {
227: o = list[i + 1];
228: if (o.GetType() == typeof(string))
229: {
230: sco.Parameters.Add(new SqlCeParameter(list[i].ToString(), list[i + 1].ToString()));
231: dt.Columns.Add(new DataColumn(list[i].ToString().Replace("@", ""), System.Type.GetType("System.String")));
232: }
233: else if (o.GetType() == typeof(int))
234: {
235: sco.Parameters.Add(new SqlCeParameter(list[i].ToString(), (int)list[i + 1]));
236: dt.Columns.Add(new DataColumn(list[i].ToString().Replace("@", ""), System.Type.GetType("System.Int32")));
237: }
238: }
239: }
240:
241: sc.Open();
242:
243: sdr = sco.ExecuteReader();
244:
245: while (sdr.Read())
246: {
247: dr = dt.NewRow();
248:
249: for (i = 0; i < dt.Columns.Count; i++)
250: {
251: dr[i] = sdr[dt.Columns[i].ColumnName];
252: }
253: }
254:
255: sc.Close();
256: }
257: finally
258: {
259:
260: if (sc != null) sc.Close();
261:
262: if (sdr != null) sdr.Close();
263: }
264:
265: return dt;
266: }
267:
268: ////////////////////////////////////////////////////////////////////////////
269:
270: /// <summary>
271: ///
272: /// </summary>
273: public DataTable Select(string sql)
274: {
275: // below: return a DataTable of result rows
276:
277: DataSet ds = new DataSet();
278: DataTable dt = new DataTable();
279: SqlCeDataAdapter da = new SqlCeDataAdapter();
280:
281: try
282: {
283: sc = new SqlCeConnection(connectionString);
284: sco = new SqlCeCommand(sql, sc);
285:
286: sc.Open();
287:
288: da.SelectCommand = sco;
289:
290: da.Fill(ds);
291:
292: sc.Close();
293:
294: dt = ds.Tables[0];
295: }
296: catch { dt = null; }
297:
298: return dt;
299: }
300:
301: ////////////////////////////////////////////////////////////////////////////
302:
303: /// <summary>
304: /// Return a DataTable of result rows and use a database_file. This will assume the file to be in the data directory.
305: /// </summary>
306: public DataTable Select(string sql, string database_file)
307: {
308: // below:
309: DataSet ds = new DataSet();
310: DataTable dt = new DataTable();
311: SqlCeDataAdapter da = new SqlCeDataAdapter();
312:
313: try
314: {
315: sc = new SqlCeConnection(Connection_String_Specific(database_file));
316: sco = new SqlCeCommand(sql, sc);
317:
318: sc.Open();
319:
320: da.SelectCommand = sco;
321:
322: da.Fill(ds);
323:
324: sc.Close();
325:
326: dt = ds.Tables[0];
327: }
328: catch (Exception)
329: {
330: dt = null;
331: }
332:
333: return dt;
334: }
335:
336: ////////////////////////////////////////////////////////////////////////////
337:
338: /// <summary>
339: ///
340: /// </summary>
341: public string Misc_Select(string name)
342: {
343: string s;
344:
345: s = Scalar(@"SELECT value FROM ia_misc WHERE name = '" + name + "'");
346:
347: return s;
348: }
349:
350: ////////////////////////////////////////////////////////////////////////////
351:
352: /// <summary>
353: ///
354: /// </summary>
355: public int Misc_Select(string name, out ArrayList al)
356: {
357: int op;
358: string s;
359: string[] st;
360:
361: op = 0;
362:
363: al = new ArrayList(1);
364:
365: s = Scalar(@"SELECT value FROM ia_misc WHERE name = '" + name + "'");
366:
367: if (s != null)
368: {
369: st = s.Split('|');
370: al = new ArrayList(st.Length);
371: al.Clear();
372:
373: foreach (string t in st) al.Add(t);
374:
375: if (al.Count > 0) op = 1;
376: else op = 0;
377: }
378: else op = -1;
379:
380: return op;
381: }
382:
383: ////////////////////////////////////////////////////////////////////////////
384:
385: /// <summary>
386: ///
387: /// </summary>
388: public int Misc_Select(string name, out DataTable dt)
389: {
390: int op;
391: string s;
392: DataRow dr;
393: XmlNode xn;
394: XmlDocument xd;
395:
396: op = 0;
397:
398: dt = new DataTable(name);
399:
400: s = Scalar(@"SELECT value_xml FROM ia_misc WHERE name = '" + name + "'");
401:
402: if (s != null && s != "")
403: {
404: xd = new XmlDocument();
405: xd.LoadXml(s);
406:
407: dt = new DataTable(xd.DocumentElement.Name);
408:
409: // below: collect table columns
410: xn = xd.DocumentElement.FirstChild;
411: foreach (XmlNode n in xn.ChildNodes)
412: {
413: if (n.Name == "user_id") dt.Columns.Add("user_id", typeof(System.Guid));
414: else dt.Columns.Add(n.Name);
415: }
416:
417: // below: collect row values
418: foreach (XmlNode n in xd.DocumentElement.ChildNodes)
419: {
420: dr = dt.NewRow();
421:
422: foreach (XmlNode o in n.ChildNodes)
423: {
424: if (o.Name == "user_id")
425: {
426: if (o.InnerText == "") dr[o.Name] = DBNull.Value;
427: else dr[o.Name] = o.InnerText;
428: }
429: else dr[o.Name] = o.InnerText;
430: }
431:
432: dt.Rows.Add(dr);
433: }
434:
435: dt.AcceptChanges();
436:
437: if (dt.Rows.Count > 0) op = 1;
438: else op = 0;
439: }
440: else op = -1;
441:
442: return op;
443: }
444:
445: ////////////////////////////////////////////////////////////////////////////
446:
447: /// <summary>
448: ///
449: /// </summary>
450: public void Misc_Update(string name, string value)
451: {
452: Sql(@"UPDATE ia_misc SET value = '" + value + "' WHERE name = '" + name + "'");
453: }
454:
455: ////////////////////////////////////////////////////////////////////////////
456:
457: /// <summary>
458: ///
459: /// </summary>
460: public void Misc_Update(string name, ArrayList al)
461: {
462: StringBuilder sb;
463:
464: if (al.Count > 0)
465: {
466: sb = new StringBuilder(al.Count + 1);
467: sb.Length = 0;
468:
469: foreach (string t in al) sb.Append(t + "|");
470: sb = sb.Remove(sb.Length - 1, 1);
471: }
472: else
473: {
474: sb = new StringBuilder(1);
475: sb.Length = 0;
476: }
477:
478: Sql(@"UPDATE ia_misc SET value = '" + sb.ToString() + "' WHERE name = '" + name + "'");
479: }
480:
481: ////////////////////////////////////////////////////////////////////////////
482:
483: /// <summary>
484: ///
485: /// </summary>
486: public void Misc_Update(string name, SortedList sl)
487: {
488: StringBuilder sb;
489:
490: if (sl.Count > 0)
491: {
492: sb = new StringBuilder(sl.Count + 1);
493: sb.Length = 0;
494:
495: foreach (string t in sl.Keys) sb.Append(t + "|");
496: sb = sb.Remove(sb.Length - 1, 1);
497: }
498: else
499: {
500: sb = new StringBuilder(1);
501: sb.Length = 0;
502: }
503:
504: Sql(@"UPDATE ia_misc SET value = '" + sb.ToString() + "' WHERE name = '" + name + "'");
505: }
506:
507: ////////////////////////////////////////////////////////////////////////////
508:
509: /// <summary>
510: ///
511: /// </summary>
512: public void Misc_Update(string name, DataTable dt)
513: {
514: StringBuilder sb;
515:
516: if (dt.Rows.Count > 0)
517: {
518: sb = new StringBuilder(dt.Rows.Count + 1);
519: sb.Length = 0;
520:
521: sb = sb.Append("<" + name + ">");
522:
523: foreach (DataRow r in dt.Rows)
524: {
525: sb = sb.Append("<row>");
526:
527: foreach (DataColumn c in dt.Columns)
528: {
529: sb = sb.Append("<" + c.ColumnName + ">");
530:
531: sb.Append(r[c.ColumnName].ToString());
532:
533: sb = sb.Append("</" + c.ColumnName + ">");
534: }
535:
536: sb = sb.Append("</row>");
537: }
538:
539: sb = sb.Append("</" + name + ">");
540: }
541: else
542: {
543: sb = new StringBuilder(1);
544: sb.Length = 0;
545: }
546:
547: Sql(@"UPDATE ia_misc SET value_xml = '" + sb.ToString() + "' WHERE name = '" + name + "'");
548: }
549:
550: ////////////////////////////////////////////////////////////////////////////
551:
552: /// <summary>
553: ///
554: /// </summary>
555: public void Misc_Delete(string name)
556: {
557: Sql(@"DELETE FROM ia_misc WHERE name = '" + name + "'");
558: }
559:
560: ////////////////////////////////////////////////////////////////////////////
561:
562: /// <summary>
563: ///
564: /// </summary>
565: public void Misc_Insert(string name)
566: {
567: Sql(@"INSERT INTO ia_misc (name) VALUES ('" + name + "')");
568: }
569:
570: ////////////////////////////////////////////////////////////////////////////
571:
572: /// <summary>
573: ///
574: /// </summary>
575: public string Scalar(string sql)
576: {
577: // below: return a scaler
578: string s;
579:
580: sc = new SqlCeConnection(connectionString);
581: sco = new SqlCeCommand(sql, sc);
582:
583: sc.Open();
584:
585: try { s = sco.ExecuteScalar().ToString(); }
586: catch { s = null; }
587:
588: sc.Close();
589:
590: return s;
591: }
592:
593: ////////////////////////////////////////////////////////////////////////////
594:
595: /// <summary>
596: ///
597: /// </summary>
598: public int Scalar_Integer(string sql)
599: {
600: // below: return a DataTable of result rows
601: int n;
602:
603: sc = new SqlCeConnection(connectionString);
604: sco = new SqlCeCommand(sql, sc);
605:
606: sc.Open();
607:
608: try
609: {
610: n = (System.Int32)sco.ExecuteScalar();
611: }
612: catch
613: {
614: n = 0;
615: }
616:
617: sc.Close();
618:
619: return n;
620: }
621:
622: ////////////////////////////////////////////////////////////////////////////
623:
624: /// <summary>
625: ///
626: /// </summary>
627: public int Scalar_SmallInteger(string sql)
628: {
629: // below: return a DataTable of result rows
630: int n;
631:
632: sc = new SqlCeConnection(connectionString);
633: sco = new SqlCeCommand(sql, sc);
634:
635: sc.Open();
636:
637: try
638: {
639: n = (System.Int16)sco.ExecuteScalar();
640: }
641: catch
642: {
643: n = 0;
644: }
645:
646: sc.Close();
647:
648: return n;
649: }
650:
651: ////////////////////////////////////////////////////////////////////////////
652:
653: /// <summary>
654: ///
655: /// </summary>
656: public string SmallDateTime(DateTime dt)
657: {
658: // below: return an SQL Server friendly string of a smalldatetime value
659: string s;
660:
661: //s = "'" + dt.ToString("yyyy-MM-ddTHH:mm:ss") + "'";
662: s = dt.ToString("yyyy-MM-ddTHH:mm:ss");
663:
664: return s;
665: }
666:
667: ////////////////////////////////////////////////////////////////////////////
668:
669: /// <summary>
670: ///
671: /// </summary>
672: public string Range_Sql(ArrayList al, string term)
673: {
674: // below: this will take a special ArrayList of ranges and will construct an SQL format that correspond to the array.
675: // input will look like al = [1,2,4,6,7,15,20-22,34-36,38], output will look like "(sr.dn>16 AND sr.dn<19) OR sr.dn=22"
676:
677: string sql, u, v;
678: string[] sp;
679: StringBuilder sb;
680:
681: sql = "";
682:
683: if (al.Count > 0)
684: {
685: sb = new StringBuilder((term.Length + 15) * al.Count);
686: sb.Length = 0;
687:
688: foreach (string s in al)
689: {
690: sp = s.Split('-');
691:
692: if (sp.Length == 1)
693: {
694: // single value
695: sb.Append(term + "=" + s + " OR ");
696: }
697: else if (sp.Length == 2)
698: {
699: // range
700: u = sp[0]; v = sp[1];
701: sb.Append("(" + term + ">=" + u + " AND " + term + "<=" + v + ") OR ");
702: }
703: }
704:
705: sql = sb.ToString();
706: sql = sql.Remove(sql.Length - 4, 4);
707: }
708:
709:
710: return sql;
711: }
712:
713: ////////////////////////////////////////////////////////////////////////////
714:
715: /// <summary>
716: ///
717: /// </summary>
718: public bool Xml_Import(string tableName, XDocument xd, out string r)
719: {
720: bool b;
721: string parameters, values;
722: string c;//, path;
723: //DataSet ds;
724: SqlCeDataAdapter sda;
725:
726: r = "";
727:
728: // below: first we delete all records in table
729: Sql(@"DELETE FROM " + tableName);
730:
731: // below: iterate through Xml records and insert into database
732:
733: //ds.ReadXml(file);
734:
735: sc = new SqlCeConnection(connectionString);
736: sc.Open();
737:
738: c = @"SELECT * FROM [" + tableName + @"]";
739: sda = new SqlCeDataAdapter(c, sc);
740: //scb = new SqlCeCommandBuilder(sda);
741:
742: try
743: {
744: foreach (XElement xe in xd.Descendants("row"))
745: {
746: parameters = values = null;
747:
748: foreach (XElement xe2 in xe.Descendants())
749: {
750: if (xe2.Name != "id")
751: {
752: parameters += xe2.Name + ",";
753: values += "'" + xe2.Value + "',";
754: }
755: }
756:
757: parameters = parameters.Remove(parameters.Length - 1, 1);
758: values = values.Remove(values.Length - 1, 1);
759:
760: Sql(@"INSERT INTO " + tableName + "(" + parameters + ") VALUES (" + values + ")");
761: }
762:
763: b = true;
764: }
765: catch (Exception ex)
766: {
767: r = "Exception: in Xml_Import(): " + ex.Message;
768: b = false;
769: }
770: finally
771: {
772: }
773:
774: return b;
775: }
776:
777: ////////////////////////////////////////////////////////////////////////////
778:
779: /// <summary>
780: ///
781: /// </summary>
782: public bool Xml_Export(string tableName, string file)
783: {
784: // below: perform dump or backup of database table data into an XML document
785: bool b;
786: string c, path;
787: DataSet ds;
788: SqlCeDataAdapter sda;
789:
790: c = @"SELECT * FROM [" + tableName + @"]";
791: sc = new SqlCeConnection(connectionString);
792: sc.Open();
793:
794: ds = new DataSet("ia_ngn");
795: sda = new SqlCeDataAdapter(c, sc);
796:
797: try
798: {
799: sda.Fill(ds, tableName);
800:
801: path = Ia.Cs.Default.Absolute_Path();
802:
803: file = path + file;
804: ds.WriteXml(file, XmlWriteMode.WriteSchema);
805:
806: b = true;
807: }
808: catch (Exception) { b = false; }
809: finally
810: {
811: sc.Close();
812: }
813:
814: return b;
815: }
816:
817: ////////////////////////////////////////////////////////////////////////////
818:
819: /// <summary>
820: /// Log a standard logging entry into a special database table
821: /// </summary>
822: public void Log(int type_id, int direction_type_id, int system_id, int process_id, int function_id, string detail, DateTime created)
823: {
824: string sql;
825:
826: // See table ia_log and log.xml
827:
828: /*
829: CREATE TABLE [dbo].[ia_log]
830: (
831: [id] int IDENTITY(1,1) CONSTRAINT [ia_log_id_pk] PRIMARY KEY,
832: [type_id] tinyint NULL,
833: [direction_type_id] tinyint NULL,
834: [system_id] smallint NULL,
835: [process_id] smallint NULL,
836: [function_id] smallint NULL,
837: [detail] ntext NULL,
838: [created] smalldatetime NULL
839: )
840: */
841:
842: #if WINDOWS_FORM
843: sql = "INSERT INTO [ia_log] ([type_id],[direction_type_id],[system_id],[process_id],[function_id],[detail],[created]) VALUES (" + type_id + "," + direction_type_id + "," + system_id + "," + process_id + "," + function_id + ",'" + detail + "','" + SmallDateTime(created) + "')";
844: #else
845: sql = "INSERT INTO [ia_log] ([type_id],[direction_type_id],[system_id],[process_id],[function_id],[detail],[created]) VALUES (" + type_id + "," + direction_type_id + "," + system_id + "," + process_id + "," + function_id + ",'" + HttpUtility.HtmlEncode(detail) + "','" + SmallDateTime(created) + "')";
846: #endif
847:
848: Sql(sql);
849: }
850:
851: ////////////////////////////////////////////////////////////////////////////
852: ////////////////////////////////////////////////////////////////////////////
853:
854: /// <summary>
855: ///
856: /// </summary>
857: public int Update(DataTable in_dt, string tableName, string select_command, string primary_key, string[] in_field, string[] field, F[] field_rule, bool synch, string delete_rule, out string result)
858: {
859: bool identical, keep_latest;
860: int op, c, count, count_in, count_delete;
861: F rule;
862: string command;
863:
864: // TEMP
865: //string temp = "", temp_dt_str, temp_in_dt_str; // TEMP
866:
867: // TEMP
868: //string temp = "";
869:
870: int i, j;
871: Hashtable ht;
872:
873: keep_latest = false;
874:
875: al = new ArrayList(1000);
876: from_al = new ArrayList(1000);
877: insert_al = new ArrayList(1000);
878: delete_al = new ArrayList(1000);
879:
880: ht = new Hashtable(1000);
881:
882: DateTime sdt, in_sdt;
883:
884: DataRow dr;
885: DataTable dt;
886: DataSet ds;
887: SqlCeDataAdapter sda;
888: SqlCeCommandBuilder scb;
889:
890: op = 0;
891: c = count = count_in = count_delete = 0;
892:
893: //progress = 0;
894:
895: ds = new DataSet("ia");
896: sc = new SqlCeConnection(connectionString);
897:
898: sc.Open();
899: command = select_command;
900: sda = new SqlCeDataAdapter();
901: scb = new SqlCeCommandBuilder(sda);
902: sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
903: sda.SelectCommand = new SqlCeCommand(command, sc);
904:
905: result = "";
906:
907: dt = null;
908:
909: // below: I will check if the records have a keep_latest field. This field means I will ignore the new record if the keep_latest date is older then
910: // the existing record.
911: for (int n = 0; n < in_field.Length; n++)
912: {
913: rule = field_rule[n];
914:
915: if (rule == F.Sdt_Keep_Latest)
916: {
917: keep_latest = true; break;
918: }
919: }
920:
921: try
922: {
923: sda.Fill(ds, tableName);
924:
925: dt = ds.Tables[0];
926:
927: if (in_dt != null)
928: {
929: count_in = in_dt.Rows.Count;
930:
931: if (dt != null)
932: {
933: count = dt.Rows.Count;
934:
935: if (in_dt.Rows.Count > 0)
936: {
937: if (synch)
938: {
939: // below: compair two lists to find records in in_dt that are not in dt
940: foreach (DataRow r in dt.Rows) al.Add(r[primary_key].ToString());
941: foreach (DataRow r in in_dt.Rows) from_al.Add(r[primary_key].ToString());
942:
943: al.Sort();
944: from_al.Sort();
945:
946: i = j = 0;
947:
948: ArrayList list1, list2;
949:
950: list1 = from_al;
951: list2 = al;
952:
953: while (i < list1.Count)
954: {
955: if (j == list2.Count) break;
956: IComparable obj1 = list1[i] as IComparable;
957: IComparable obj2 = list2[j] as IComparable;
958:
959: int cmp = obj1.CompareTo(obj2);
960:
961: switch (Math.Sign(cmp))
962: {
963: case 0: ++i; ++j; break;
964: case 1: delete_al.Add(list2[j].ToString()); ++j; break;
965: case -1: insert_al.Add(list1[i].ToString()); ++i; break;
966: }
967: }
968:
969: while (i < list1.Count) // we reached the end of list 2 first.
970: {
971: insert_al.Add(list1[i].ToString()); ++i;
972: }
973:
974: while (j < list2.Count) // we reached the end of list 1 first.
975: {
976: delete_al.Add(list2[j].ToString()); ++j;
977: }
978:
979: if (delete_al.Count > 0)
980: {
981: for (i = 0; i < delete_al.Count && i < 100; i++)
982: {
983: // We will delete it, or its contents according to the deletion rules of the tableName
984:
985: dr = dt.Rows.Find(delete_al[i].ToString());
986:
987: if (delete_rule == "all")
988: {
989: dr.Delete();
990: }
991: else if (delete_rule == "keep primary key")
992: {
993: // below: this will delete everything but keep only the primary key
994:
995: for (int n = 0; n < in_field.Length; n++)
996: {
997: if (field[n].ToString() != primary_key)
998: {
999: rule = field_rule[n];
if (rule == F.Bit || rule == F.In || rule == F.St || rule == F.Sdt || rule == F.Sdt_Keep_Latest)
{
dr[field[n].ToString()] = DBNull.Value;
}
else if (rule == F.Up)
{
dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
}
}
}
}
count_delete++;
}
}
}
foreach (DataRow in_dr in in_dt.Rows)
{
// below: collect relevent values:
dr = dt.Rows.Find(long.Parse(in_dr[primary_key].ToString()));
//progress = c / count;
if (dr != null)
{
// below: check if rows are identical
identical = true;
for (int n = 0; n < in_field.Length; n++)
{
rule = field_rule[n];
if (rule == F.Bit)
{
try
{
if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
{
identical = false;
break;
}
}
catch (InvalidCastException)
{
identical = false;
break;
}
}
else if (rule == F.In)
{
try
{
if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
{
identical = false;
break;
}
}
catch (FormatException)
{
identical = false;
break;
}
}
else if (rule == F.St)
{
if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { }
else if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] != DBNull.Value) { identical = false; break; }
else if (dr[field[n].ToString()] != DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { identical = false; break; }
else if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
{
identical = false;
break;
}
}
else if (rule == F.Sdt)
{
sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
// below: if in_sdt lays within 1 minute of sdt they are identical
if (in_sdt > sdt.AddMinutes(1) || in_sdt < sdt.AddMinutes(-1))
{
identical = false;
break;
}
}
else { }
}
//if (dr["dn"].ToString() == "25645818") { }
if (keep_latest)
{
// identical = true;
for (int n = 0; n < in_field.Length; n++)
{
rule = field_rule[n];
if (rule == F.Sdt_Keep_Latest)
{
// below: this will keep the record as same with no change if the new date is older than the old date
sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
// below: if in_sdt is less than sdt they are identical
if (in_sdt >= sdt) identical = false;
else identical = true;
break;
}
}
}
if (identical)
{
// below: rows are the exact same. Do nothing
}
else
{
// below: row was updated
for (int n = 0; n < in_field.Length; n++)
{
rule = field_rule[n];
if (rule == F.Bit)
{
try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
}
else if (rule == F.In)
{
try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
}
else if (rule == F.St)
{
if (in_dr[in_field[n].ToString()] == DBNull.Value) dr[field[n].ToString()] = DBNull.Value;
else dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
}
else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
{
in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
dr[field[n].ToString()] = SmallDateTime(in_sdt);
}
else if (rule == F.Up)
{
dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
}
}
c++;
}
}
else
{
// below: row does not exists, we will add it to database
dr = dt.NewRow();
for (int n = 0; n < in_field.Length; n++)
{
rule = field_rule[n];
if (rule == F.Bit)
{
try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
}
else if (rule == F.In)
{
try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
}
else if (rule == F.St)
{
dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
}
else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
{
in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
dr[field[n].ToString()] = SmallDateTime(in_sdt);
}
else if (rule == F.Cr || rule == F.Up)
{
dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
}
}
dt.Rows.Add(dr);
c++;
}
}
scb.GetUpdateCommand();
sda.Update(ds, tableName);
sc.Close();
result = "(" + c + "-" + count_delete + "/" + count_in + "/" + count + ")";
if (c > 0 || count_delete > 0) op = 1;
}
else
{
result += "(0-0/0/*)";
op = 0;
}
}
else
{
result += "(0-0/*/null)";
op = -1;
}
}
else
{
result += "(0-0/null/*)";
op = -1;
}
}
catch (Exception ex)
{
#if DEBUG
result = "Ia.Ngn.Cs.Application.Update(): " + ex.ToString(); // +" TEMP=[" + temp + "]"; // TEMP
#else
result = "Ia.Ngn.Cs.Application.Update(): " + ex.Message;
#endif
op = -1;
}
return op;
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
}
}
/*
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Globalization" %>
<%@ Import Namespace="System.ComponentModel" %>
<script runat="server">
public void Page_Load(Object sender, EventArgs e) {
//QUICK OVERVIEW OF SYNTAX:
/ *
int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
DataTable dtEmployees = GetDataTable("SELECT * FROM Employees WHERE Country=@currentCountry AND BirthDate<@cutOffDate AND EmployeeId>@employeeId","@currentCountry,varchar/255,USA;@cutOffDate,date," + cutOffDate.ToString() + ";@employeeId,int,2");
DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
* /
//get an integer
int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
litShow1.Text = "There are " + numberOfEmployees.ToString() + " employees";
//get an integer with parameters
string selectedCountry = "UK";
int numberOfEmployees2 = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
litShow2.Text = "There are " + numberOfEmployees2.ToString() + " employees from: " + selectedCountry;
//get a string
string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
litShow3.Text = "The last name of the person selected is <b>" + currentLastName + "</b>. ";
//get a string with parameters
int selectedId = 4;
string currentLastName2 = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
litShow4.Text = "The last name of the person with id " + selectedId.ToString() + " is <b>" + currentLastName2 + "</b>. ";
//get a date
CultureInfo ci = new CultureInfo("en-US");
DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
litShow5.Text = "Your order shipped on <b>" + shippedDate.ToString("dddd, MMMM d, yyyy",ci) + "</b>. ";
//get a date with parameters
int currentOrderId = 10264;
CultureInfo ci2 = new CultureInfo("en-US");
DateTime shippedDate2 = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
litShow6.Text = "Your order shipped on <b>" + shippedDate2.ToString("dddd, MMMM d, yyyy",ci2) + "</b>. ";
//get one record
DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
litShow7.Text = "The selected employee is <b>" + drEmployee["FirstName"].ToString() + " " + drEmployee["LastName"].ToString() + "</b>";
//get one record with parameters
int currentEmployeeId = 8;
DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
litShow8.Text = "The employee with id#" + currentEmployeeId.ToString() + " is <b>" + drEmployee2["FirstName"].ToString() + " " + drEmployee2["LastName"].ToString() + "</b>";
//get several records
DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
foreach(DataRow row in dtEmployees.Rows) {
litShow9.Text += row["LastName"].ToString() + ", " + row["FirstName"].ToString() + "<br>";
}
//get several records with parameters
DateTime cutOffDate = DateTime.Parse("1/1/1963");
DataTable dtEmployees2 = GetDataTable("SELECT * FROM Employees WHERE Country=@currentCountry AND BirthDate<@cutOffDate AND EmployeeId>@employeeId","@currentCountry,varchar/255,USA;@cutOffDate,date," + cutOffDate.ToString() + ";@employeeId,int,2");
foreach(DataRow row in dtEmployees2.Rows) {
litShow10.Text += row["EmployeeId"].ToString() + ". " + row["LastName"].ToString() + ", " + row["FirstName"].ToString() + " (" + row["Country"].ToString() + ")<br>";
}
//perform an SQL command
DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
//perform an SQL command
int theId = 1;
DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
}
//EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
public int GetInteger(string sql) {
int r = 0;
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
con.Open();
SqlCeCommand cmd = new SqlCeCommand(sql,con);
r = (int)cmd.ExecuteScalar();
con.Close();
return r;
}
//EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255,UK");
public int GetInteger(string sql, string parameterList) {
int r = 0;
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
con.Open();
SqlCeCommand cmd = new SqlCeCommand(sql,con);
GetParameters(ref cmd, parameterList);
r = (int)cmd.ExecuteScalar();
con.Close();
return r;
}
//EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
public string GetString(string sql) {
string rs = "";
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
con.Open();
SqlCeCommand cmd = new SqlCeCommand(sql,con);
rs = (string)cmd.ExecuteScalar();
con.Close();
return rs;
}
//EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeedId,int," + selectedId.ToString());
public string GetString(string sql, string parameterList) {
string rs = "";
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
con.Open();
SqlCeCommand cmd = new SqlCeCommand(sql,con);
GetParameters(ref cmd, parameterList);
rs = (string)cmd.ExecuteScalar();
con.Close();
return rs;
}
//EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
public DateTime GetDate(string sql) {
DateTime rd;
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
con.Open();
SqlCeCommand cmd = new SqlCeCommand(sql,con);
rd = (DateTime)cmd.ExecuteScalar();
con.Close();
return rd;
}
//EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
public DateTime GetDate(string sql, string parameterList) {
DateTime rd;
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
con.Open();
SqlCeCommand cmd = new SqlCeCommand(sql,con);
GetParameters(ref cmd, parameterList);
rd = (DateTime)cmd.ExecuteScalar();
con.Close();
return rd;
}
//EXAMPLE: DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
public DataRow GetDataRow(string sql) {
DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlCeDataAdapter da = new SqlCeDataAdapter();
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
SqlCeCommand cmd = new SqlCeCommand(sql, con);
da.SelectCommand = cmd;
da.Fill(ds);
try {
dt = ds.Tables[0];
return dt.Rows[0];
}
catch {
return null;
}
}
//EXAMPLE: DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
public DataRow GetDataRow(string sql,string parameterList) {
DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlCeDataAdapter da = new SqlCeDataAdapter();
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
SqlCeCommand cmd = new SqlCeCommand(sql, con);
GetParameters(ref cmd, parameterList);
da.SelectCommand = cmd;
da.Fill(ds);
try {
dt = ds.Tables[0];
return dt.Rows[0];
}
catch {
return null;
}
}
//EXAMPLE: DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
public DataTable GetDataTable(string sql) {
DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlCeDataAdapter da = new SqlCeDataAdapter();
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
SqlCeCommand cmd = new SqlCeCommand(sql, con);
da.SelectCommand = cmd;
da.Fill(ds);
try {
dt = ds.Tables[0];
return dt;
}
catch {
return null;
}
}
//EXAMPLE: DataTable dtEmployees2 = GetDataTable("SELECT * FROM Employees WHERE Country=@currentCountry AND BirthDate<@cutOffDate AND EmployeeId>@employeeId","@currentCountry,varchar/255,USA;@cutOffDate,date," + cutOffDate.ToString() + ";@employeeId,int,2");
public DataTable GetDataTable(string sql, string parameterList) {
//parameterList will be in this form: "@currentCountry,varchar/255,USA;@cutOffDate,date,1/1/1963"
DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlCeDataAdapter da = new SqlCeDataAdapter();
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
SqlCeCommand cmd = new SqlCeCommand(sql, con);
GetParameters(ref cmd, parameterList);
da.SelectCommand = cmd;
da.Fill(ds);
try {
dt = ds.Tables[0];
return dt;
}
catch {
return null;
}
}
//EXAMPLE: DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
public void DoCommand(string sql) {
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandText = sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
//EXAMPLE: DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
public void DoCommand(string sql, string parameterList) {
SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandText = sql;
GetParameters(ref cmd, parameterList);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
//used by the other methods
public void GetParameters(ref SqlCeCommand cmd, string parameterList) {
//build parameters from the parameter list
string[] parameterLines = parameterList.Split(';');
foreach(string parameterLine in parameterLines) {
//break up individual line
string[] parts = parameterLine.Split(',');
switch(parts[1].ToString().Substring(0,3).ToUpper()) {
case "VAR":
//get the size from e.g. "varchar/255"
string[] half = parts[1].ToString().Split('/');
int size = Int32.Parse(half[1]);
cmd.Parameters.Add(parts[0],SqlDbType.VarChar,size).Value = parts[2];
break;
case "DAT":
cmd.Parameters.Add(parts[0],SqlDbType.DateTime).Value = DateTime.Parse(parts[2]);
break;
case "INT":
cmd.Parameters.Add(parts[0],SqlDbType.Int).Value = Int32.Parse(parts[2]);
break;
}
}
}
*/