1: using System;
2: using System.Web;
3: using System.Xml;
4: using System.Configuration;
5: using System.Data;
6: using System.Collections;
7: using System.Text;
8: using System.Text.RegularExpressions;
9: using MySql.Data.MySqlClient;
10:
11: namespace Ia.Model.Db
12: {
13: ////////////////////////////////////////////////////////////////////////////
14:
15: /// <summary publish="true">
16: /// MySQL supporting class.
17: /// </summary>
18: /// <remarks>
19: /// Copyright © 2001-2015 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
20: ///
21: /// 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
22: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
23: ///
24: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
25: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
26: ///
27: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
28: ///
29: /// Copyright notice: This notice may not be removed or altered from any source distribution.
30: /// </remarks>
31: public class MySql
32: {
33: private string connectionString, databaseName;
34: private ArrayList al, from_al, insert_al, delete_al;
35: private MySqlConnection msc;
36: private MySqlCommand msco;
37:
38: ////////////////////////////////////////////////////////////////////////////
39:
40: /// <summary>
41: ///
42: /// </summary>
43: public enum F
44: {
45: /// <summary/>
46: Bit,
47: /// <summary/>
48: In,
49: /// <summary/>
50: St,
51: /// <summary/>
52: Dt,
53: /// <summary/>
54: Dt_Accept_Newer,
55: /// <summary/>
56: Cr,
57: /// <summary/>
58: Up,
59: /// <summary/>
60: Re,
61: /// <summary/>
62: Ni
63: };
64:
65: ////////////////////////////////////////////////////////////////////////////
66:
67: /*
68: * The MISC function does not work I did not create the misc database nor did I test it.
69: *
70: * Later create a webserivce to update and read misc values
71: */
72:
73: ////////////////////////////////////////////////////////////////////////////
74:
75: /// <summary>
76: /// Initialize database with connection string from web.config.
77: /// </summary>
78: public MySql()
79: {
80: if (ConfigurationManager.ConnectionStrings["MySqlConnectionString"] != null)
81: {
82: connectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
83: }
84: }
85:
86: ////////////////////////////////////////////////////////////////////////////
87:
88: /// <summary>
89: /// Initialize database with connection string from web.config but with the passed database file name.
90: /// </summary>
91: public MySql(string _databaseName)
92: {
93: connectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
94:
95: connectionString = Database_String(_databaseName);
96: }
97:
98: ////////////////////////////////////////////////////////////////////////////
99:
100: /// <summary>
101: ///
102: /// </summary>
103: public bool TableExist(string tableName)
104: {
105: // below: check if database exists
106: bool b;
107: DataTable dt;
108:
109: b = true;
110:
111: try
112: {
113: dt = Select("show tables like '" + tableName + "';");
114:
115: if (dt.Rows.Count > 0) b = true;
116: else b = false;
117: }
118: catch (Exception)
119: {
120: b = false;
121: }
122:
123: return b;
124: }
125:
126: ////////////////////////////////////////////////////////////////////////////
127:
128: /// <summary>
129: ///
130: /// </summary>
131: private string Database_String(string _databaseName)
132: {
133: // below: check if there is a database specified in command
134: if (_databaseName != null && _databaseName.Length > 0)
135: {
136: // below: check if connection_string has a database specified
137: if (connectionString.Contains("database"))
138: {
139: databaseName = _databaseName;
140:
141: connectionString = Regex.Replace(connectionString, @"database\s*=\s*[a-z]+?;", "database=" + databaseName + ";");
142: }
143: else
144: {
145: connectionString += "database=" + _databaseName + ";";
146: }
147: }
148: else
149: {
150: if (_databaseName != null && _databaseName.Length > 0)
151: {
152: if (connectionString.Contains("database"))
153: {
154: connectionString = Regex.Replace(connectionString, @"database\s*=\s*[a-z]+?;", "database=" + _databaseName + ";");
155: }
156: else
157: {
158: connectionString += "database=" + _databaseName + ";";
159: }
160: }
161: }
162:
163: return connectionString;
164: }
165:
166: ////////////////////////////////////////////////////////////////////////////
167:
168: /// <summary>
169: ///
170: /// </summary>
171: public string DateTime(DateTime dt)
172: {
173: // below: return a friendly string of a datetime value
174: string s;
175:
176: //s = "'" + dt.ToString("dd/MM/yyyy HH:mm:ss") + "'";
177: //s = dt.ToString("dd/MM/yyyy HH:mm:ss");
178: s = dt.ToString("yyyy-MM-dd HH:mm:ss");
179:
180: return s;
181: }
182:
183: ////////////////////////////////////////////////////////////////////////////
184:
185: /// <summary>
186: ///
187: /// </summary>
188: public bool Sql(string sql)
189: {
190: return Sql(sql, null, false);
191: }
192:
193: ////////////////////////////////////////////////////////////////////////////
194:
195: /// <summary>
196: ///
197: /// </summary>
198: public bool Sql(string sql, bool b)
199: {
200: return Sql(sql, null, b);
201: }
202:
203: ////////////////////////////////////////////////////////////////////////////
204:
205: /// <summary>
206: ///
207: /// </summary>
208: public bool Sql(string sql, string database)
209: {
210: return Sql(sql, database, false);
211: }
212:
213: ////////////////////////////////////////////////////////////////////////////
214:
215: /// <summary>
216: /// Execute and SQL command
217: /// </summary>
218: /// <param name="sql">SQL string</param>
219: /// <param name="changeEmptyStringQuoteToNull">Indicator weather single quotes '' should be replaced with NULL string</param>
220: /// <returns>Boolean</returns>
221: public bool Sql(string sql, string database, bool changeEmptyStringQuoteToNull)
222: {
223: // below: execute an SQL command
224: bool b;
225: string s;
226:
227: b = true;
228:
229: if (changeEmptyStringQuoteToNull) sql = sql.Replace("''", "NULL");
230:
231: s = Database_String(database);
232:
233: msc = new MySqlConnection(s);
234:
235: msco = new MySqlCommand();
236:
237: msco.CommandType = /*Server.HtmlEncode(*/CommandType.Text;//); // default
238: msco.CommandText = sql;
239: msco.Connection = msc;
240: msc.Open();
241:
242: try
243: {
244: msco.ExecuteNonQuery();
245: b = true;
246: }
247: catch (Exception)
248: {
249: b = false;
250: }
251: finally
252: {
253: msc.Close();
254: }
255:
256: return b;
257: }
258:
259: ////////////////////////////////////////////////////////////////////////////
260:
261: /// <summary>
262: ///
263: /// </summary>
264: public DataTable Select(string sql)
265: {
266: return Select(sql, null);
267: }
268:
269: ////////////////////////////////////////////////////////////////////////////
270:
271: /// <summary>
272: ///
273: /// </summary>
274: public DataTable Select(string sql, string database)
275: {
276: // below: return a DataTable of result rows
277: string s;
278:
279: DataSet ds = new DataSet();
280: DataTable dt = new DataTable();
281: MySqlDataAdapter da = new MySqlDataAdapter();
282:
283: s = Database_String(database);
284:
285: try
286: {
287: msc = new MySqlConnection(s);
288:
289: msco = new MySqlCommand(sql, msc);
290:
291: msc.Open();
292:
293: da.SelectCommand = msco;
294:
295: da.Fill(ds);
296:
297: msc.Close();
298:
299: dt = ds.Tables[0];
300: }
301: catch { dt = null; }
302:
303: return dt;
304: }
305:
306: /*
307: ////////////////////////////////////////////////////////////////////////////
308:
309: /// <summary>
310: ///
311: /// </summary>
312: public DataTable Select(string sql)
313: {
314: // below: return a DataTable of result rows
315:
316: OdbcConnection sc = null;
317: OdbcCommand sco;
318:
319: DataSet ds = new DataSet();
320: DataTable dt = new DataTable();
321: OdbcDataAdapter da = new OdbcDataAdapter();
322:
323: sc = new OdbcConnection(ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString);
324: sco = new OdbcCommand(sql, sc);
325:
326: if (sc.State == ConnectionState.Open) sc.Close();
327: if (sco.Connection.State == ConnectionState.Open) sco.Connection.Close();
328:
329: try
330: {
331: sc.Open();
332:
333: da.SelectCommand = sco;
334:
335: da.Fill(ds);
336:
337: sc.Close();
338:
339: dt = ds.Tables[0];
340: }
341: catch (Exception ex)
342: {
343: dt = null;
344: }
345:
346: return dt;
347: }
348:
349: ////////////////////////////////////////////////////////////////////////////
350: ////////////////////////////////////////////////////////////////////////////
351:
352: public DataTable Select(string sql)
353: {
354: string s;
355: string connection_string = "server=iis;uid=*;pwd=*;persist security info=True;database=*";
356: DataTable dt = new DataTable();
357:
358: dt = null;
359:
360: OleDbConnection connection = new OleDbConnection(connection_string);
361: OleDbCommand command = new OleDbCommand();
362:
363: command.Connection = connection;
364: command.CommandText = sql;
365:
366: connection.Open();
367:
368: try
369: {
370: command.ExecuteNonQuery();
371: }
372: catch (Exception ex)
373: {
374: s = " <br><br>Error occured in Execute_Non_Query: <br>" + ex.ToString();
375: }
376: finally
377: {
378: connection.Close();
379: }
380:
381: return dt;
382: }
383: */
384:
385:
386: ////////////////////////////////////////////////////////////////////////////
387:
388: /// <summary>
389: ///
390: /// </summary>
391: public string Misc_Select(string name)
392: {
393: string s;
394:
395: s = Scalar(@"SELECT value FROM ia_misc WHERE name = '" + name + "'");
396:
397: return s;
398: }
399:
400: /*
401: ////////////////////////////////////////////////////////////////////////////
402:
403: /// <summary>
404: ///
405: /// </summary>
406: public int Misc_Select(string name, out ArrayList al)
407: {
408: int op;
409: string s;
410: string[] st;
411:
412: op = 0;
413:
414: al = new ArrayList(1);
415:
416: s = Scalar(@"SELECT value FROM ia_misc WHERE name = '" + name + "'");
417:
418: if (s != null)
419: {
420: st = s.Split('|');
421: al = new ArrayList(st.Length);
422: al.Clear();
423:
424: foreach (string t in st) al.Add(t);
425:
426: if (al.Count > 0) op = 1;
427: else op = 0;
428: }
429: else op = -1;
430:
431: return op;
432: }
433:
434: ////////////////////////////////////////////////////////////////////////////
435:
436: /// <summary>
437: ///
438: /// </summary>
439: public int Misc_Select(string name, out DataTable dt)
440: {
441: int op;
442: string s;
443: DataRow dr;
444: XmlNode xn;
445: XmlDocument xd;
446:
447: op = 0;
448:
449: dt = new DataTable(name);
450:
451: s = Scalar(@"SELECT value_xml FROM ia_misc WHERE name = '" + name + "'");
452:
453: if (s != null && s != "")
454: {
455: xd = new XmlDocument();
456: xd.LoadXml(s);
457:
458: dt = new DataTable(xd.DocumentElement.Name);
459:
460: // below: collect table columns
461: xn = xd.DocumentElement.FirstChild;
462: foreach (XmlNode n in xn.ChildNodes)
463: {
464: if (n.Name == "user_id") dt.Columns.Add("user_id", typeof(System.Guid));
465: else dt.Columns.Add(n.Name);
466: }
467:
468: // below: collect row values
469: foreach (XmlNode n in xd.DocumentElement.ChildNodes)
470: {
471: dr = dt.NewRow();
472:
473: foreach (XmlNode o in n.ChildNodes)
474: {
475: if (o.Name == "user_id")
476: {
477: if (o.InnerText == "") dr[o.Name] = DBNull.Value;
478: else dr[o.Name] = o.InnerText;
479: }
480: else dr[o.Name] = o.InnerText;
481: }
482:
483: dt.Rows.Add(dr);
484: }
485:
486: dt.AcceptChanges();
487:
488: if (dt.Rows.Count > 0) op = 1;
489: else op = 0;
490: }
491: else op = -1;
492:
493: return op;
494: }
495: */
496:
497: ////////////////////////////////////////////////////////////////////////////
498:
499: /// <summary>
500: ///
501: /// </summary>
502: public void Misc_Update(string name, string value)
503: {
504: Sql(@"UPDATE ia_misc SET value = '" + value + "' WHERE name = '" + name + "'");
505: }
506:
507: /*
508: ////////////////////////////////////////////////////////////////////////////
509:
510: /// <summary>
511: ///
512: /// </summary>
513: public void Misc_Update(string name, ArrayList al)
514: {
515: StringBuilder sb;
516:
517: if (al.Count > 0)
518: {
519: sb = new StringBuilder(al.Count + 1);
520: sb.Length = 0;
521:
522: foreach (string t in al) sb.Append(t + "|");
523: sb = sb.Remove(sb.Length - 1, 1);
524: }
525: else
526: {
527: sb = new StringBuilder(1);
528: sb.Length = 0;
529: }
530:
531: Sql(@"UPDATE ia_misc SET value = '" + sb.ToString() + "' WHERE name = '" + name + "'");
532: }
533:
534: ////////////////////////////////////////////////////////////////////////////
535:
536: /// <summary>
537: ///
538: /// </summary>
539: public void Misc_Update(string name, SortedList sl)
540: {
541: StringBuilder sb;
542:
543: if (sl.Count > 0)
544: {
545: sb = new StringBuilder(sl.Count + 1);
546: sb.Length = 0;
547:
548: foreach (string t in sl.Keys) sb.Append(t + "|");
549: sb = sb.Remove(sb.Length - 1, 1);
550: }
551: else
552: {
553: sb = new StringBuilder(1);
554: sb.Length = 0;
555: }
556:
557: Sql(@"UPDATE ia_misc SET value = '" + sb.ToString() + "' WHERE name = '" + name + "'");
558: }
559:
560: ////////////////////////////////////////////////////////////////////////////
561:
562: /// <summary>
563: ///
564: /// </summary>
565: public void Misc_Update(string name, DataTable dt)
566: {
567: StringBuilder sb;
568:
569: if (dt.Rows.Count > 0)
570: {
571: sb = new StringBuilder(dt.Rows.Count + 1);
572: sb.Length = 0;
573:
574: sb = sb.Append("<" + name + ">");
575:
576: foreach (DataRow r in dt.Rows)
577: {
578: sb = sb.Append("<row>");
579:
580: foreach (DataColumn c in dt.Columns)
581: {
582: sb = sb.Append("<" + c.ColumnName + ">");
583:
584: sb.Append(r[c.ColumnName].ToString());
585:
586: sb = sb.Append("</" + c.ColumnName + ">");
587: }
588:
589: sb = sb.Append("</row>");
590: }
591:
592: sb = sb.Append("</" + name + ">");
593: }
594: else
595: {
596: sb = new StringBuilder(1);
597: sb.Length = 0;
598: }
599:
600: Sql(@"UPDATE ia_misc SET value_xml = '" + sb.ToString() + "' WHERE name = '" + name + "'");
601: }
602: */
603:
604: ////////////////////////////////////////////////////////////////////////////
605:
606: /// <summary>
607: ///
608: /// </summary>
609: public void Misc_Delete(string name)
610: {
611: Sql(@"DELETE FROM ia_misc WHERE name = '" + name + "'");
612: }
613:
614: ////////////////////////////////////////////////////////////////////////////
615:
616: /// <summary>
617: ///
618: /// </summary>
619: public void Misc_Insert(string name)
620: {
621: Sql(@"INSERT INTO ia_misc (name) VALUES ('" + name + "')");
622: }
623:
624: ////////////////////////////////////////////////////////////////////////////
625:
626: /// <summary>
627: ///
628: /// </summary>
629: public void Misc_Insert(string name, string value)
630: {
631: Sql(@"INSERT INTO ia_misc (name,value) VALUES ('" + name + "','" + value + "')");
632: }
633:
634: ////////////////////////////////////////////////////////////////////////////
635:
636: /// <summary>
637: ///
638: /// </summary>
639: public string Scalar(string sql)
640: {
641: return Scalar(sql, null);
642: }
643:
644: ////////////////////////////////////////////////////////////////////////////
645:
646: /// <summary>
647: ///
648: /// </summary>
649: public string Scalar(string sql, string database)
650: {
651: // below: return a scaler
652: string s;
653:
654: s = Database_String(database);
655:
656: msc = new MySqlConnection(s);
657: msco = new MySqlCommand(sql, msc);
658:
659: msc.Open();
660:
661: try
662: {
663: s = msco.ExecuteScalar().ToString();
664: }
665: catch
666: {
667: s = null;
668: }
669: finally
670: {
671: msc.Close();
672: }
673:
674: return s;
675: }
676:
677: ////////////////////////////////////////////////////////////////////////////
678: ////////////////////////////////////////////////////////////////////////////
679:
680: /// <summary>
681: ///
682: /// </summary>
683: public int Update(DataTable in_dt, string table_name, string select_command, string primary_key, string[] in_field, string[] field, F[] field_rule, bool synch, string delete_rule, out string result)
684: {
685: return Update(in_dt, table_name, select_command, primary_key, in_field, field, field_rule, synch, delete_rule, null, out result);
686: }
687:
688: ////////////////////////////////////////////////////////////////////////////
689:
690: /// <summary>
691: ///
692: /// </summary>
693: public int Update(DataTable in_dt, string table_name, string select_command, string primary_key, string[] in_field, string[] field, F[] field_rule, bool synch, string delete_rule, string database, out string result)
694: {
695: bool identical, ignore, accept_newer;
696: int op, c, count, count_in, count_delete;
697: F rule;
698: string s, u, command;
699:
700: //string temp = "", temp_dt_str, temp_in_dt_str; // TEMP
701:
702: int i, j;
703: Hashtable ht;
704:
705: accept_newer = false;
706:
707: al = new ArrayList(1000);
708: from_al = new ArrayList(1000);
709: insert_al = new ArrayList(1000);
710: delete_al = new ArrayList(1000);
711:
712: ht = new Hashtable(1000);
713:
714: DateTime sdt, in_sdt;
715:
716: DataRow dr;
717: DataTable dt;
718: DataSet ds;
719: MySqlDataAdapter msda;
720: MySqlConnection msc;
721: MySqlCommandBuilder mscb;
722:
723: op = 0;
724: c = count = count_in = count_delete = 0;
725:
726: ds = new DataSet("ia");
727: s = Database_String(database);
728: msc = new MySqlConnection(s);
729:
730: //sc = new SqlConnection(path);
731:
732: msc.Open();
733: command = select_command;
734: msda = new MySqlDataAdapter();
735: msda.SelectCommand = new MySqlCommand(command, msc);
736: msda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
737: mscb = new MySqlCommandBuilder(msda);
738:
739: result = "";
740:
741: //temp_in_dt_str = temp_dt_str = "";
742:
743: dt = null;
744:
745: // below: I will check if the records have a accept_newer field. This field means I will ignore the new record if the accept_newer date is older then
746: // the existing record.
747: for (int n = 0; n < in_field.Length; n++)
748: {
749: rule = field_rule[n];
750:
751: if (rule == F.Dt_Accept_Newer)
752: {
753: accept_newer = true; break;
754: }
755: }
756:
757: try
758: {
759: msda.Fill(ds, table_name);
760:
761: dt = ds.Tables[0];
762:
763: if (in_dt != null)
764: {
765: count_in = in_dt.Rows.Count;
766:
767: // TEMP
768: //foreach (DataRow r in in_dt.Rows)
769: //{
770: // temp_in_dt_str += "\n";
771: // foreach (DataColumn c2 in in_dt.Columns) temp_in_dt_str += ":" + r[c2].ToString();
772: //}
773:
774: if (dt != null)
775: {
776: count = dt.Rows.Count;
777:
778: // TEMP
779: //foreach (DataRow r in dt.Rows)
780: //{
781: // temp_dt_str += "\n";
782: // foreach (DataColumn c2 in dt.Columns) temp_dt_str += ":" + r[c2].ToString();
783: //}
784:
785: if (in_dt.Rows.Count > 0)
786: {
787: //if (dt.Rows.Count > 0)
788: //{
789: if (synch)
790: {
791: // below: compair two lists to find records in in_dt that are not in dt
792: foreach (DataRow r in dt.Rows) al.Add(r[primary_key].ToString());
793: foreach (DataRow r in in_dt.Rows) from_al.Add(r[primary_key].ToString());
794:
795: al.Sort();
796: from_al.Sort();
797:
798: i = j = 0;
799:
800: // below: I will assume that from_al is longer than al
801: //if (from_al.Count > al.Count)
802: //{
803: ArrayList list1, list2;
804:
805: list1 = from_al;
806: list2 = al;
807:
808: while (i < list1.Count)
809: {
810: if (j == list2.Count) break;
811: IComparable obj1 = list1[i] as IComparable;
812: IComparable obj2 = list2[j] as IComparable;
813:
814: int cmp = obj1.CompareTo(obj2);
815:
816: switch (Math.Sign(cmp))
817: {
818: case 0: ++i; ++j; break;
819: case 1: delete_al.Add(list2[j].ToString()); ++j; break;
820: case -1: insert_al.Add(list1[i].ToString()); ++i; break;
821: }
822: }
823:
824: while (i < list1.Count) // we reached the end of list 2 first.
825: {
826: insert_al.Add(list1[i].ToString()); ++i;
827: }
828:
829: while (j < list2.Count) // we reached the end of list 1 first.
830: {
831: delete_al.Add(list2[j].ToString()); ++j;
832: }
833: //}
834:
835: if (delete_al.Count > 0)
836: {
837: for (i = 0; i < delete_al.Count && i < 100; i++)
838: {
839: // We will delete it, or its contents according to the deletion rules of the table_name
840:
841: dr = dt.Rows.Find(delete_al[i].ToString());
842:
843: if (delete_rule == "all")
844: {
845: dr.Delete();
846: }
847: else if (delete_rule == "keep primary key")
848: {
849: // below: this will delete everything but keep only the primary key
850:
851: for (int n = 0; n < in_field.Length; n++)
852: {
853: if (field[n].ToString() != primary_key)
854: {
855: rule = field_rule[n];
856:
857: if (rule == F.Bit || rule == F.In || rule == F.St || rule == F.Dt || rule == F.Dt_Accept_Newer)
858: {
859: dr[field[n].ToString()] = DBNull.Value;
860: }
861: else if (rule == F.Up || rule == F.Re)
862: {
863: dr[field[n].ToString()] = DateTime(System.DateTime.UtcNow.AddHours(3));
864: }
865: }
866: }
867: }
868:
869: count_delete++;
870: }
871: }
872: }
873:
874: foreach (DataRow in_dr in in_dt.Rows)
875: {
876: // below: collect relevent values:
877:
878: //if (in_dr[primary_key].ToString() == "95126013") op++;
879:
880: dr = dt.Rows.Find(in_dr[primary_key].ToString());
881:
882: if (dr != null)
883: {
884: // below: if the accept newer flag is on we will ignore records that are older than the current record
885: ignore = false;
886: if (accept_newer)
887: {
888: // identical = true;
889:
890: for (int n = 0; n < in_field.Length; n++)
891: {
892: rule = field_rule[n];
893:
894: if (rule == F.Dt_Accept_Newer)
895: {
896: // below: this will keep the record as same with no change if the new date is older than the old date
897:
898: sdt = System.DateTime.Parse(dr[field[n].ToString()].ToString());
899: in_sdt = System.DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
900:
901: // below: if in_sdt is less than sdt they are identical
902:
903: if (in_sdt > sdt) ignore = false;
904: else ignore = true;
905:
906: break;
907: }
908: }
909: }
910:
911: if (ignore)
912: {
913: }
914: else
915: {
916: // below: check if rows are identical
917:
918: identical = true;
919:
920: for (int n = 0; n < in_field.Length; n++)
921: {
922: rule = field_rule[n];
923:
924: if (rule == F.Bit)
925: {
926: try
927: {
928: if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
929: {
930: identical = false;
931: break;
932: }
933: }
934: catch (InvalidCastException)
935: {
936: identical = false;
937: break;
938: }
939: }
940: else if (rule == F.In)
941: {
942: try
943: {
944: if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
945: {
946: identical = false;
947: break;
948: }
949: }
950: catch (FormatException)
951: {
952: identical = false;
953: break;
954: }
955: }
956: else if (rule == F.St)
957: {
958: if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { }
959: else if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] != DBNull.Value) { identical = false; break; }
960: else if (dr[field[n].ToString()] != DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { identical = false; break; }
961: else if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
962: {
963: identical = false;
964: break;
965: }
966: }
967: else if (rule == F.Dt)
968: {
969: sdt = System.DateTime.Parse(dr[field[n].ToString()].ToString());
970: in_sdt = System.DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
971:
972: // below: if in_sdt lays within 1 minute of sdt they are identical
973:
974: if (in_sdt > sdt.AddMinutes(1) || in_sdt < sdt.AddMinutes(-1))
975: {
976: identical = false;
977: break;
978: }
979: }
980: else { }
981: }
982:
983: if (identical)
984: {
985: // below: rows are the exact same
986:
987: for (int n = 0; n < in_field.Length; n++)
988: {
989: rule = field_rule[n];
990:
991: if (rule == F.Re)
992: {
993: dr[field[n].ToString()] = DateTime(System.DateTime.UtcNow.AddHours(3));
994: }
995: }
996: }
997: else
998: {
999: // below: row was updated
1000: for (int n = 0; n < in_field.Length; n++)
1001: {
1002: rule = field_rule[n];
1003:
1004: if (rule == F.Bit)
1005: {
1006: // below: I can not use bool. The true and false values are 1 and 0.
1007: try
1008: {
1009: u = in_dr[in_field[n].ToString()].ToString();
1010:
1011: if (u == "1") dr[field[n].ToString()] = true;
1012: else if (u == "0") dr[field[n].ToString()] = false;
1013: else dr[field[n].ToString()] = DBNull.Value;
1014: }
1015: catch (Exception) { dr[field[n].ToString()] = DBNull.Value; }
1016: }
1017: else if (rule == F.In)
1018: {
1019: try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
1020: catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
1021: }
1022: else if (rule == F.St)
1023: {
1024: if (in_dr[in_field[n].ToString()] == DBNull.Value) dr[field[n].ToString()] = DBNull.Value;
1025: else dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
1026: }
1027: else if (rule == F.Dt || rule == F.Dt_Accept_Newer)
1028: {
1029: in_sdt = System.DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
1030: dr[field[n].ToString()] = DateTime(in_sdt);
1031: }
1032: else if (rule == F.Up || rule == F.Re)
1033: {
1034: dr[field[n].ToString()] = DateTime(System.DateTime.UtcNow.AddHours(3));
1035: }
1036: }
1037:
1038: c++;
1039: }
1040: }
1041: }
1042: else
1043: {
1044: // below: row does not exists, we will add it to database
1045:
1046: dr = dt.NewRow();
1047:
1048: for (int n = 0; n < in_field.Length; n++)
1049: {
1050: rule = field_rule[n];
1051:
1052: if (rule == F.Bit)
1053: {
1054: try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
1055: catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
1056: }
1057: else if (rule == F.In)
1058: {
1059: try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
1060: catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
1061: }
1062: else if (rule == F.St)
1063: {
1064: dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
1065: }
1066: else if (rule == F.Dt || rule == F.Dt_Accept_Newer)
1067: {
1068: in_sdt = System.DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
1069: dr[field[n].ToString()] = DateTime(in_sdt);
1070: }
1071: else if (rule == F.Cr || rule == F.Up || rule == F.Re)
1072: {
1073: dr[field[n].ToString()] = DateTime(System.DateTime.UtcNow.AddHours(3));
1074: }
1075: }
1076:
1077: // TEMP
1078: //temp = "";
1079: //foreach (DataColumn dc in dr.Table.Columns) temp += "|" + dr[dc.ColumnName];
1080:
1081: dt.Rows.Add(dr);
1082: c++;
1083: }
1084: }
1085:
1086: //msda.GetUpdateCommand();
1087: msda.Update(ds, table_name);
1088: msc.Close();
1089:
1090: result = "(" + c + "-" + count_delete + "/" + count_in + "/" + count + ")";
1091:
1092: if (c > 0 || count_delete > 0) op = 1;
1093: //}
1094: //else
1095: //{
1096: // result += "(0-0/*/0)";
1097: // op = 0;
1098: //}
1099: }
1100: else
1101: {
1102: result += "(0-0/0/*)";
1103: op = 0;
1104: }
1105: }
1106: else
1107: {
1108: result += "(0-0/*/null)";
1109: op = -1;
1110: }
1111: }
1112: else
1113: {
1114: result += "(0-0/null/*)";
1115: op = -1;
1116: }
1117: }
1118: catch (Exception ex)
1119: {
1120: #if DEBUG
1121: result = "Ia.Model.Db.MySql.Update(): " + ex.ToString();
1122: #else
1123: result = "Ia.Model.Db.MySql.Update(): " + ex.Message;
1124: #endif
1125:
1126: // for debugging
1127: /*
1128: string d;
1129:
1130: d = "Select: [" + select_command + "] " + now.ToString() + "\n";
1131: d += "Row: [" + temp + "]\n";
1132: d += "in_dt:---------------------\n";
1133: d += temp_in_dt_str + "\n";
1134: d += "dt:---------------------\n";
1135: d += temp_dt_str + "\n";
1136:
1137: Ia.Model.Log.Append("error.txt", d + "\n");
1138:
1139: foreach (DataRow r in dt.Rows)
1140: {
1141: d = "\n";
1142: foreach (DataColumn c2 in dt.Columns) d += ":" + r[c2].ToString();
1143: Ia.Model.Log.Append("error.txt", d + "\n");
1144: }
1145: */
1146:
1147: op = -1;
1148: }
1149:
1150: return op;
1151: }
1152:
1153: ////////////////////////////////////////////////////////////////////////////
1154: ////////////////////////////////////////////////////////////////////////////
1155: }
1156: }
1157:
1158: /*
1159: using System;
1160: using System.Web;
1161: using System.Web.UI;
1162: using System.Web.UI.WebControls;
1163: using System.Web.UI.HtmlControls;
1164: using System.IO;
1165: using System.Xml;
1166: using System.Data;
1167: using System.Data.OleDb;
1168: using System.Text;
1169:
1170: using System.Collections; // for ArrayList
1171:
1172: using System.Text.RegularExpressions;
1173: using System.Globalization;
1174:
1175: namespace IA
1176: {
1177: public class MySQL : System.Web.UI.Page
1178: {
1179: protected DataSet ds;
1180: protected OleDbDataAdapter da;
1181: protected DataTable dt;
1182:
1183: protected Label error_l;
1184:
1185: ////////////////////////////////////////////////////////////////////////////
1186:
1187: protected void Page_Load(object sender, EventArgs e)
1188: {
1189: / *
1190: int word_group,i=0;
1191: string word,language,type;
1192:
1193: Execute_Non_Query("DROP TABLE IF EXISTS Word");
1194: Execute_Non_Query(@"CREATE TABLE Word (id INT(11) AUTO_INCREMENT PRIMARY KEY,word VARCHAR(255) BINARY,word_group INT,language CHAR(2),type VARCHAR(20),frequency INT DEFAULT '0' NOT NULL )");
1195:
1196: word_group=0;
1197:
1198: language="en";
1199: type="PLOT";
1200:
1201: for(i=0;i<1000;i++)
1202: {
1203: word=i.ToString();
1204: try
1205: {
1206: // Execute_Non_Query(@"INSERT INTO Word (word,word_group,language,type) VALUES ('"+word+@"',"+(word_group++)+@",'"+language+@"','"+type+@"')");
1207: }
1208: catch (Exception ex)
1209: {
1210: error_l.Text += "<br><br> Error occured in Execute_Non_Query: " + ex.ToString();
1211: }
1212: }
1213:
1214: error_l.Text += " FINISH ";
1215: * /
1216:
1217: // below: execute scalar tests
1218: error_l.Text += "["+Execute_Scalar("SELECT id FROM User WHERE login = 'ahmad'")+"]";
1219: error_l.Text += "<br><br>";
1220: error_l.Text += "["+Execute_Scalar("SELECT COUNT(*) FROM Word WHERE language = 'jp'")+"]";
1221:
1222: }
1223:
1224: ////////////////////////////////////////////////////////////////////////////
1225:
1226: protected string Execute_Scalar(string command_string)
1227: {
1228: string id;
1229: object obj = null; // Return Value
1230:
1231: OleDbConnection connection = new OleDbConnection(connection_string);
1232: OleDbCommand command = new OleDbCommand(command_string,connection);
1233:
1234: connection.Open();
1235: obj = command.ExecuteScalar();
1236: connection.Close();
1237:
1238:
1239: if(obj == null) { id="null"; }
1240: else id = Convert.ToString(obj);
1241:
1242: return id;
1243:
1244: / *
1245:
1246: public object GetObject(string connStr, string sqlCmd)
1247: {
1248: object obj = null; // Return Value
1249: SqlConnection m_SqlCn = new SqlConnection(connStr);
1250: SqlCommand m_SqlCommand = new SqlCommand(sqlCmd,m_SqlCn);
1251: try
1252: { m_SqlCommand.Connection.Open();
1253: obj = m_SqlCommand.ExecuteScalar();
1254: } // end try
1255: catch (Exception e)
1256: { string Er = "Error in GetObject()-> " + e.ToString();
1257: throw new Exception(Er);
1258: }
1259: finally
1260: { m_SqlCommand.Dispose();
1261: m_SqlConnection.Close();
1262: }
1263: return obj;
1264: }
1265:
1266:
1267: --------------
1268:
1269: string id;
1270: object obj = null; // Return Value
1271:
1272: OleDbConnection connection = new OleDbConnection(connection_string);
1273: OleDbCommand command = new OleDbCommand();
1274:
1275: command.Connection=connection;
1276: command.CommandText=command_string;
1277:
1278: connection.Open();
1279: obj = command.ExecuteScalar();
1280: connection.Close();
1281:
1282: if(obj == null) { id="null"; }
1283: else id = Convert.ToString(obj);
1284:
1285: return id;
1286: * /
1287: }
1288:
1289: ////////////////////////////////////////////////////////////////////////////
1290:
1291: }
1292: }
1293:
1294: */