)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Home » Code Library » MySql (Ia.Model.Db)

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