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];
1000:
1001: if (rule == F.Bit || rule == F.In || rule == F.St || rule == F.Sdt || rule == F.Sdt_Keep_Latest)
1002: {
1003: dr[field[n].ToString()] = DBNull.Value;
1004: }
1005: else if (rule == F.Up)
1006: {
1007: dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
1008: }
1009: }
1010: }
1011: }
1012:
1013: count_delete++;
1014: }
1015: }
1016: }
1017:
1018: foreach (DataRow in_dr in in_dt.Rows)
1019: {
1020: // below: collect relevent values:
1021:
1022: dr = dt.Rows.Find(long.Parse(in_dr[primary_key].ToString()));
1023:
1024: //progress = c / count;
1025:
1026: if (dr != null)
1027: {
1028: // below: check if rows are identical
1029:
1030: identical = true;
1031:
1032: for (int n = 0; n < in_field.Length; n++)
1033: {
1034: rule = field_rule[n];
1035:
1036: if (rule == F.Bit)
1037: {
1038: try
1039: {
1040: if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
1041: {
1042: identical = false;
1043: break;
1044: }
1045: }
1046: catch (InvalidCastException)
1047: {
1048: identical = false;
1049: break;
1050: }
1051:
1052: }
1053: else if (rule == F.In)
1054: {
1055: try
1056: {
1057: if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
1058: {
1059: identical = false;
1060: break;
1061: }
1062: }
1063: catch (FormatException)
1064: {
1065: identical = false;
1066: break;
1067: }
1068: }
1069: else if (rule == F.St)
1070: {
1071: if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { }
1072: else if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] != DBNull.Value) { identical = false; break; }
1073: else if (dr[field[n].ToString()] != DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { identical = false; break; }
1074: else if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
1075: {
1076: identical = false;
1077: break;
1078: }
1079: }
1080: else if (rule == F.Sdt)
1081: {
1082: sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
1083: in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
1084:
1085: // below: if in_sdt lays within 1 minute of sdt they are identical
1086:
1087: if (in_sdt > sdt.AddMinutes(1) || in_sdt < sdt.AddMinutes(-1))
1088: {
1089: identical = false;
1090: break;
1091: }
1092: }
1093: else { }
1094: }
1095:
1096: //if (dr["dn"].ToString() == "25645818") { }
1097:
1098: if (keep_latest)
1099: {
1100: // identical = true;
1101:
1102: for (int n = 0; n < in_field.Length; n++)
1103: {
1104: rule = field_rule[n];
1105:
1106: if (rule == F.Sdt_Keep_Latest)
1107: {
1108: // below: this will keep the record as same with no change if the new date is older than the old date
1109:
1110: sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
1111: in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
1112:
1113: // below: if in_sdt is less than sdt they are identical
1114:
1115: if (in_sdt >= sdt) identical = false;
1116: else identical = true;
1117:
1118: break;
1119: }
1120: }
1121: }
1122:
1123: if (identical)
1124: {
1125: // below: rows are the exact same. Do nothing
1126: }
1127: else
1128: {
1129: // below: row was updated
1130: for (int n = 0; n < in_field.Length; n++)
1131: {
1132: rule = field_rule[n];
1133:
1134: if (rule == F.Bit)
1135: {
1136: try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
1137: catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
1138: }
1139: else if (rule == F.In)
1140: {
1141: try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
1142: catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
1143: }
1144: else if (rule == F.St)
1145: {
1146: if (in_dr[in_field[n].ToString()] == DBNull.Value) dr[field[n].ToString()] = DBNull.Value;
1147: else dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
1148: }
1149: else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
1150: {
1151: in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
1152: dr[field[n].ToString()] = SmallDateTime(in_sdt);
1153: }
1154: else if (rule == F.Up)
1155: {
1156: dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
1157: }
1158: }
1159:
1160: c++;
1161: }
1162: }
1163: else
1164: {
1165: // below: row does not exists, we will add it to database
1166:
1167: dr = dt.NewRow();
1168:
1169: for (int n = 0; n < in_field.Length; n++)
1170: {
1171: rule = field_rule[n];
1172:
1173: if (rule == F.Bit)
1174: {
1175: try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
1176: catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
1177: }
1178: else if (rule == F.In)
1179: {
1180: try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
1181: catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
1182: }
1183: else if (rule == F.St)
1184: {
1185: dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
1186: }
1187: else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
1188: {
1189: in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
1190: dr[field[n].ToString()] = SmallDateTime(in_sdt);
1191: }
1192: else if (rule == F.Cr || rule == F.Up)
1193: {
1194: dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
1195: }
1196: }
1197:
1198: dt.Rows.Add(dr);
1199: c++;
1200: }
1201: }
1202:
1203: scb.GetUpdateCommand();
1204: sda.Update(ds, tableName);
1205: sc.Close();
1206:
1207: result = "(" + c + "-" + count_delete + "/" + count_in + "/" + count + ")";
1208:
1209: if (c > 0 || count_delete > 0) op = 1;
1210: }
1211: else
1212: {
1213: result += "(0-0/0/*)";
1214: op = 0;
1215: }
1216: }
1217: else
1218: {
1219: result += "(0-0/*/null)";
1220: op = -1;
1221: }
1222: }
1223: else
1224: {
1225: result += "(0-0/null/*)";
1226: op = -1;
1227: }
1228: }
1229: catch (Exception ex)
1230: {
1231: #if DEBUG
1232: result = "Ia.Ngn.Cs.Application.Update(): " + ex.ToString(); // +" TEMP=[" + temp + "]"; // TEMP
1233: #else
1234: result = "Ia.Ngn.Cs.Application.Update(): " + ex.Message;
1235: #endif
1236:
1237: op = -1;
1238: }
1239:
1240: return op;
1241: }
1242:
1243: ////////////////////////////////////////////////////////////////////////////
1244: ////////////////////////////////////////////////////////////////////////////
1245: }
1246: }
1247:
1248: /*
1249:
1250: <%@ Page Language="C#" %>
1251: <%@ Import Namespace="System.Data" %>
1252: <%@ Import Namespace="System.Data.SqlClient" %>
1253: <%@ Import Namespace="System.Xml" %>
1254: <%@ Import Namespace="System.Collections" %>
1255: <%@ Import Namespace="System.Globalization" %>
1256: <%@ Import Namespace="System.ComponentModel" %>
1257:
1258: <script runat="server">
1259:
1260: public void Page_Load(Object sender, EventArgs e) {
1261:
1262: //QUICK OVERVIEW OF SYNTAX:
1263: / *
1264: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
1265: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
1266: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
1267: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
1268: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
1269: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
1270: DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
1271: DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
1272: DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
1273: 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");
1274: DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
1275: DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
1276: * /
1277:
1278: //get an integer
1279: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
1280: litShow1.Text = "There are " + numberOfEmployees.ToString() + " employees";
1281:
1282: //get an integer with parameters
1283: string selectedCountry = "UK";
1284: int numberOfEmployees2 = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
1285: litShow2.Text = "There are " + numberOfEmployees2.ToString() + " employees from: " + selectedCountry;
1286:
1287: //get a string
1288: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
1289: litShow3.Text = "The last name of the person selected is <b>" + currentLastName + "</b>. ";
1290:
1291: //get a string with parameters
1292: int selectedId = 4;
1293: string currentLastName2 = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
1294: litShow4.Text = "The last name of the person with id " + selectedId.ToString() + " is <b>" + currentLastName2 + "</b>. ";
1295:
1296: //get a date
1297: CultureInfo ci = new CultureInfo("en-US");
1298: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
1299: litShow5.Text = "Your order shipped on <b>" + shippedDate.ToString("dddd, MMMM d, yyyy",ci) + "</b>. ";
1300:
1301: //get a date with parameters
1302: int currentOrderId = 10264;
1303: CultureInfo ci2 = new CultureInfo("en-US");
1304: DateTime shippedDate2 = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
1305: litShow6.Text = "Your order shipped on <b>" + shippedDate2.ToString("dddd, MMMM d, yyyy",ci2) + "</b>. ";
1306:
1307: //get one record
1308: DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
1309: litShow7.Text = "The selected employee is <b>" + drEmployee["FirstName"].ToString() + " " + drEmployee["LastName"].ToString() + "</b>";
1310:
1311: //get one record with parameters
1312: int currentEmployeeId = 8;
1313: DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
1314: litShow8.Text = "The employee with id#" + currentEmployeeId.ToString() + " is <b>" + drEmployee2["FirstName"].ToString() + " " + drEmployee2["LastName"].ToString() + "</b>";
1315:
1316: //get several records
1317: DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
1318: foreach(DataRow row in dtEmployees.Rows) {
1319: litShow9.Text += row["LastName"].ToString() + ", " + row["FirstName"].ToString() + "<br>";
1320: }
1321:
1322: //get several records with parameters
1323: DateTime cutOffDate = DateTime.Parse("1/1/1963");
1324: 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");
1325: foreach(DataRow row in dtEmployees2.Rows) {
1326: litShow10.Text += row["EmployeeId"].ToString() + ". " + row["LastName"].ToString() + ", " + row["FirstName"].ToString() + " (" + row["Country"].ToString() + ")<br>";
1327: }
1328:
1329: //perform an SQL command
1330: DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
1331:
1332: //perform an SQL command
1333: int theId = 1;
1334: DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
1335:
1336: }
1337:
1338: //EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
1339: public int GetInteger(string sql) {
1340: int r = 0;
1341: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1342: con.Open();
1343: SqlCeCommand cmd = new SqlCeCommand(sql,con);
1344: r = (int)cmd.ExecuteScalar();
1345: con.Close();
1346: return r;
1347: }
1348:
1349: //EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255,UK");
1350: public int GetInteger(string sql, string parameterList) {
1351: int r = 0;
1352: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1353: con.Open();
1354: SqlCeCommand cmd = new SqlCeCommand(sql,con);
1355: GetParameters(ref cmd, parameterList);
1356: r = (int)cmd.ExecuteScalar();
1357: con.Close();
1358: return r;
1359: }
1360:
1361: //EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
1362: public string GetString(string sql) {
1363: string rs = "";
1364: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1365: con.Open();
1366: SqlCeCommand cmd = new SqlCeCommand(sql,con);
1367: rs = (string)cmd.ExecuteScalar();
1368: con.Close();
1369: return rs;
1370: }
1371:
1372: //EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeedId,int," + selectedId.ToString());
1373: public string GetString(string sql, string parameterList) {
1374: string rs = "";
1375: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1376: con.Open();
1377: SqlCeCommand cmd = new SqlCeCommand(sql,con);
1378: GetParameters(ref cmd, parameterList);
1379: rs = (string)cmd.ExecuteScalar();
1380: con.Close();
1381: return rs;
1382: }
1383:
1384: //EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
1385: public DateTime GetDate(string sql) {
1386: DateTime rd;
1387: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1388: con.Open();
1389: SqlCeCommand cmd = new SqlCeCommand(sql,con);
1390: rd = (DateTime)cmd.ExecuteScalar();
1391: con.Close();
1392: return rd;
1393: }
1394:
1395:
1396: //EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
1397: public DateTime GetDate(string sql, string parameterList) {
1398: DateTime rd;
1399: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1400: con.Open();
1401: SqlCeCommand cmd = new SqlCeCommand(sql,con);
1402: GetParameters(ref cmd, parameterList);
1403: rd = (DateTime)cmd.ExecuteScalar();
1404: con.Close();
1405: return rd;
1406: }
1407:
1408: //EXAMPLE: DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
1409: public DataRow GetDataRow(string sql) {
1410: DataSet ds = new DataSet();
1411: DataTable dt = new DataTable();
1412: SqlCeDataAdapter da = new SqlCeDataAdapter();
1413: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1414: SqlCeCommand cmd = new SqlCeCommand(sql, con);
1415: da.SelectCommand = cmd;
1416: da.Fill(ds);
1417: try {
1418: dt = ds.Tables[0];
1419: return dt.Rows[0];
1420: }
1421: catch {
1422: return null;
1423: }
1424: }
1425:
1426: //EXAMPLE: DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
1427: public DataRow GetDataRow(string sql,string parameterList) {
1428: DataSet ds = new DataSet();
1429: DataTable dt = new DataTable();
1430: SqlCeDataAdapter da = new SqlCeDataAdapter();
1431: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1432: SqlCeCommand cmd = new SqlCeCommand(sql, con);
1433: GetParameters(ref cmd, parameterList);
1434: da.SelectCommand = cmd;
1435: da.Fill(ds);
1436: try {
1437: dt = ds.Tables[0];
1438: return dt.Rows[0];
1439: }
1440: catch {
1441: return null;
1442: }
1443: }
1444:
1445: //EXAMPLE: DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
1446: public DataTable GetDataTable(string sql) {
1447: DataSet ds = new DataSet();
1448: DataTable dt = new DataTable();
1449: SqlCeDataAdapter da = new SqlCeDataAdapter();
1450: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1451: SqlCeCommand cmd = new SqlCeCommand(sql, con);
1452: da.SelectCommand = cmd;
1453: da.Fill(ds);
1454: try {
1455: dt = ds.Tables[0];
1456: return dt;
1457: }
1458: catch {
1459: return null;
1460: }
1461: }
1462:
1463: //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");
1464: public DataTable GetDataTable(string sql, string parameterList) {
1465: //parameterList will be in this form: "@currentCountry,varchar/255,USA;@cutOffDate,date,1/1/1963"
1466:
1467: DataSet ds = new DataSet();
1468: DataTable dt = new DataTable();
1469: SqlCeDataAdapter da = new SqlCeDataAdapter();
1470: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1471: SqlCeCommand cmd = new SqlCeCommand(sql, con);
1472: GetParameters(ref cmd, parameterList);
1473: da.SelectCommand = cmd;
1474: da.Fill(ds);
1475: try {
1476: dt = ds.Tables[0];
1477: return dt;
1478: }
1479: catch {
1480: return null;
1481: }
1482: }
1483:
1484: //EXAMPLE: DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
1485: public void DoCommand(string sql) {
1486: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1487: SqlCeCommand cmd = new SqlCeCommand();
1488: cmd.CommandText = sql;
1489: cmd.Connection = con;
1490: con.Open();
1491: cmd.ExecuteNonQuery();
1492: con.Close();
1493: }
1494:
1495: //EXAMPLE: DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
1496: public void DoCommand(string sql, string parameterList) {
1497: SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
1498: SqlCeCommand cmd = new SqlCeCommand();
1499: cmd.CommandText = sql;
1500: GetParameters(ref cmd, parameterList);
1501: cmd.Connection = con;
1502: con.Open();
1503: cmd.ExecuteNonQuery();
1504: con.Close();
1505: }
1506:
1507: //used by the other methods
1508: public void GetParameters(ref SqlCeCommand cmd, string parameterList) {
1509: //build parameters from the parameter list
1510: string[] parameterLines = parameterList.Split(';');
1511: foreach(string parameterLine in parameterLines) {
1512: //break up individual line
1513: string[] parts = parameterLine.Split(',');
1514: switch(parts[1].ToString().Substring(0,3).ToUpper()) {
1515: case "VAR":
1516: //get the size from e.g. "varchar/255"
1517: string[] half = parts[1].ToString().Split('/');
1518: int size = Int32.Parse(half[1]);
1519: cmd.Parameters.Add(parts[0],SqlDbType.VarChar,size).Value = parts[2];
1520: break;
1521: case "DAT":
1522: cmd.Parameters.Add(parts[0],SqlDbType.DateTime).Value = DateTime.Parse(parts[2]);
1523: break;
1524: case "INT":
1525: cmd.Parameters.Add(parts[0],SqlDbType.Int).Value = Int32.Parse(parts[2]);
1526: break;
1527: }
1528: }
1529: }
1530: */