Professional Applications Programmers/Consultants برمجة واستشارات تطبيقات الإنترنت
Skip Navigation LinksHome » Code Library » MySql

Public general use code classes and xml files that we've compiled and used over the years:

MySQL supporting class.

   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), Internet 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:  */