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
for (int n = 0; n < in_field.Length; n++)
{
rule = field_rule[n];
if (rule == F.Bit)
{
// below: I can not use bool. The true and false values are 1 and 0.
try
{
u = in_dr[in_field[n].ToString()].ToString();
if (u == "1") dr[field[n].ToString()] = true;
else if (u == "0") dr[field[n].ToString()] = false;
else dr[field[n].ToString()] = DBNull.Value;
}
catch (Exception) { 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.Dt || rule == F.Dt_Accept_Newer)
{
in_sdt = System.DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
dr[field[n].ToString()] = DateTime(in_sdt);
}
else if (rule == F.Up || rule == F.Re)
{
dr[field[n].ToString()] = DateTime(System.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.Dt || rule == F.Dt_Accept_Newer)
{
in_sdt = System.DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
dr[field[n].ToString()] = DateTime(in_sdt);
}
else if (rule == F.Cr || rule == F.Up || rule == F.Re)
{
dr[field[n].ToString()] = DateTime(System.DateTime.UtcNow.AddHours(3));
}
}
// TEMP
//temp = "";
//foreach (DataColumn dc in dr.Table.Columns) temp += "|" + dr[dc.ColumnName];
dt.Rows.Add(dr);
c++;
}
}
//msda.GetUpdateCommand();
msda.Update(ds, table_name);
msc.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/0/*)";
op = 0;
}
}
else
{
result += "(0-0/*/null)";
op = -1;
}
}
else
{
result += "(0-0/null/*)";
op = -1;
}
}
catch (Exception ex)
{
#if DEBUG
result = "Ia.Model.Db.MySql.Update(): " + ex.ToString();
#else
result = "Ia.Model.Db.MySql.Update(): " + ex.Message;
#endif
// for debugging
/*
string d;
d = "Select: [" + select_command + "] " + now.ToString() + "\n";
d += "Row: [" + temp + "]\n";
d += "in_dt:---------------------\n";
d += temp_in_dt_str + "\n";
d += "dt:---------------------\n";
d += temp_dt_str + "\n";
Ia.Model.Log.Append("error.txt", d + "\n");
foreach (DataRow r in dt.Rows)
{
d = "\n";
foreach (DataColumn c2 in dt.Columns) d += ":" + r[c2].ToString();
Ia.Model.Log.Append("error.txt", d + "\n");
}
*/
op = -1;
}
return op;
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
}
}
/*
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Collections; // for ArrayList
using System.Text.RegularExpressions;
using System.Globalization;
namespace IA
{
public class MySQL : System.Web.UI.Page
{
protected DataSet ds;
protected OleDbDataAdapter da;
protected DataTable dt;
protected Label error_l;
////////////////////////////////////////////////////////////////////////////
protected void Page_Load(object sender, EventArgs e)
{
/ *
int word_group,i=0;
string word,language,type;
Execute_Non_Query("DROP TABLE IF EXISTS Word");
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 )");
word_group=0;
language="en";
type="PLOT";
for(i=0;i<1000;i++)
{
word=i.ToString();
try
{
// Execute_Non_Query(@"INSERT INTO Word (word,word_group,language,type) VALUES ('"+word+@"',"+(word_group++)+@",'"+language+@"','"+type+@"')");
}
catch (Exception ex)
{
error_l.Text += "<br><br> Error occured in Execute_Non_Query: " + ex.ToString();
}
}
error_l.Text += " FINISH ";
* /
// below: execute scalar tests
error_l.Text += "["+Execute_Scalar("SELECT id FROM User WHERE login = 'ahmad'")+"]";
error_l.Text += "<br><br>";
error_l.Text += "["+Execute_Scalar("SELECT COUNT(*) FROM Word WHERE language = 'jp'")+"]";
}
////////////////////////////////////////////////////////////////////////////
protected string Execute_Scalar(string command_string)
{
string id;
object obj = null; // Return Value
OleDbConnection connection = new OleDbConnection(connection_string);
OleDbCommand command = new OleDbCommand(command_string,connection);
connection.Open();
obj = command.ExecuteScalar();
connection.Close();
if(obj == null) { id="null"; }
else id = Convert.ToString(obj);
return id;
/ *
public object GetObject(string connStr, string sqlCmd)
{
object obj = null; // Return Value
SqlConnection m_SqlCn = new SqlConnection(connStr);
SqlCommand m_SqlCommand = new SqlCommand(sqlCmd,m_SqlCn);
try
{ m_SqlCommand.Connection.Open();
obj = m_SqlCommand.ExecuteScalar();
} // end try
catch (Exception e)
{ string Er = "Error in GetObject()-> " + e.ToString();
throw new Exception(Er);
}
finally
{ m_SqlCommand.Dispose();
m_SqlConnection.Close();
}
return obj;
}
--------------
string id;
object obj = null; // Return Value
OleDbConnection connection = new OleDbConnection(connection_string);
OleDbCommand command = new OleDbCommand();
command.Connection=connection;
command.CommandText=command_string;
connection.Open();
obj = command.ExecuteScalar();
connection.Close();
if(obj == null) { id="null"; }
else id = Convert.ToString(obj);
return id;
* /
}
////////////////////////////////////////////////////////////////////////////
}
}
*/