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

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

SQL Server CE support class.

    1: using System;
    2: using System.Web;
    3: using System.Xml;
    4: using System.Xml.Linq;
    5: using System.Configuration;
    6: using System.Data;
    7: using System.Data.SqlServerCe;
    8: using System.Collections;
    9: using System.Text;
   10: using System.Text.RegularExpressions;
   11:  
   12: namespace Ia.Cs.Db
   13: {
   14:     ////////////////////////////////////////////////////////////////////////////
   15:  
   16:     /// <summary publish="true">
   17:     /// SQL Server CE support class.
   18:     /// </summary>
   19:     /// <remarks>
   20:     /// Copyright © 2008-2013 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
   21:     ///
   22:     /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
   23:     /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
   24:     ///
   25:     /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
   26:     /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
   27:     /// 
   28:     /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
   29:     /// 
   30:     /// Copyright notice: This notice may not be removed or altered from any source distribution.
   31:     /// </remarks>
   32:     public class SqlServerCe
   33:     {
   34:         private string connectionString;
   35:         private ArrayList al, from_al, insert_al, delete_al;
   36:         private SqlCeConnection sc;
   37:         private SqlCeCommand sco;
   38:  
   39:         ////////////////////////////////////////////////////////////////////////////
   40:  
   41:         /// <summary>
   42:         ///
   43:         /// </summary>
   44:         public enum F
   45:         {
   46:             /// <summary/>
   47:             Bit,
   48:             /// <summary/>
   49:             In,
   50:             /// <summary/>
   51:             St,
   52:             /// <summary/>
   53:             Sdt,
   54:             /// <summary/>
   55:             Sdt_Keep_Latest,
   56:             /// <summary/>
   57:             Cr,
   58:             /// <summary/>
   59:             Up,
   60:             /// <summary/>
   61:             Ni
   62:         };
   63:  
   64:         ////////////////////////////////////////////////////////////////////////////
   65:  
   66:         /// <summary>
   67:         /// Initialize database with connection string from app.config.
   68:         /// </summary>
   69:         public SqlServerCe()
   70:         {
   71:             connectionString = ConfigurationManager.ConnectionStrings["SqlServerCeConnectionString"].ConnectionString;
   72:         }
   73:  
   74:         ////////////////////////////////////////////////////////////////////////////
   75:  
   76:         /// <summary>
   77:         /// Initialize database with connection string from app.config but with the passed database file name.
   78:         /// </summary>
   79:         public SqlServerCe(string _databaseName)
   80:         {
   81:             connectionString = Connection_String_Specific(_databaseName);
   82:         }
   83:  
   84:         ////////////////////////////////////////////////////////////////////////////
   85:  
   86:         /// <summary>
   87:         ///
   88:         /// </summary>
   89:         public bool Database_Exist()
   90:         {
   91:             // below: check if database exists
   92:             bool b;
   93:  
   94:             b = true;
   95:  
   96:             try
   97:             {
   98:                 Sql("SELECT GETDATE() AS date");
   99:             }
  100:             catch (Exception ex)
  101:             {
  102:                 if (ex.Message.Contains("The database file cannot be found. ")) b = false;
  103:             }
  104:  
  105:             return b;
  106:         }
  107:  
  108:         ////////////////////////////////////////////////////////////////////////////
  109:  
  110:         /// <summary>
  111:         ///
  112:         /// </summary>
  113:         public bool Create_Database()
  114:         {
  115:             // below:
  116:             bool b;
  117:             SqlCeEngine sce;
  118:  
  119:             b = true;
  120:  
  121:             try
  122:             {
  123:                 sce = new SqlCeEngine(connectionString);
  124:                 sce.CreateDatabase();
  125:                 sce.Dispose();
  126:             }
  127:             catch (Exception)
  128:             {
  129:                 b = false;
  130:             }
  131:  
  132:             return b;
  133:         }
  134:  
  135:         ////////////////////////////////////////////////////////////////////////////
  136:  
  137:         /// <summary>
  138:         /// Return the connection string given the database file name passed.
  139:         /// </summary>
  140:         private string Connection_String_Specific(string _database_file_name)
  141:         {
  142:             string s;
  143:  
  144:             // below: this will copy and replace the original database file name with the provided one.
  145:             s = Regex.Replace(connectionString, @"(\w+?\.sdf)", _database_file_name);
  146:  
  147:             return s;
  148:         }
  149:  
  150:         ////////////////////////////////////////////////////////////////////////////
  151:  
  152:         /// <summary>
  153:         ///
  154:         /// </summary>
  155:         public bool Sql(string sql)
  156:         {
  157:             // below: execute an SQL command
  158:             bool b = true;
  159:  
  160:             sc = new SqlCeConnection(connectionString);
  161:             sco = new SqlCeCommand();
  162:  
  163:             sco.CommandType = Server.HtmlEncode(CommandType.Text); // default
  164:             sco.CommandText = sql;
  165:             sco.Connection = sc;
  166:             sc.Open();
  167:             sco.ExecuteNonQuery();
  168:             sc.Close();
  169:  
  170:             return b;
  171:         }
  172:  
  173:         ////////////////////////////////////////////////////////////////////////////
  174:  
  175:         /// <summary>
  176:         /// Execute an SQL statement over a database file. The files is assumed to be in the data directory.
  177:         /// </summary>
  178:         public bool Sql(string sql, string database_file)
  179:         {
  180:             // below: execute an SQL command
  181:             bool b = true;
  182:  
  183:             sc = new SqlCeConnection(Connection_String_Specific(database_file));
  184:             sco = new SqlCeCommand();
  185:  
  186:             sco.CommandType = Server.HtmlEncode(CommandType.Text); // default
  187:             sco.CommandText = sql;
  188:             sco.Connection = sc;
  189:             sc.Open();
  190:             sco.ExecuteNonQuery();
  191:             sc.Close();
  192:  
  193:             return b;
  194:         }
  195:  
  196:         ////////////////////////////////////////////////////////////////////////////
  197:  
  198:         /// <summary>
  199:         ///
  200:         /// </summary>
  201:         public DataTable Sp(string sp_name, params object[] list)
  202:         {
  203:             // below: return data from a stored procedure
  204:  
  205:             // ERRORS
  206:  
  207:             object o;
  208:             int i;
  209:  
  210:             DataSet ds = new DataSet();
  211:             DataTable dt = new DataTable();
  212:             DataRow dr = null;
  213:             SqlCeDataReader sdr = null;
  214:  
  215:             try
  216:             {
  217:                 sc = new SqlCeConnection(connectionString);
  218:                 sco = new SqlCeCommand(sp_name, sc);
  219:  
  220:                 sco.CommandType = CommandType.StoredProcedure;
  221:  
  222:                 for (i = 0; i < list.Length; i += 2)
  223:                 {
  224:                     o = list[i];
  225:                     if (o.GetType() == typeof(string))
  226:                     {
  227:                         o = list[i + 1];
  228:                         if (o.GetType() == typeof(string))
  229:                         {
  230:                             sco.Parameters.Add(new SqlCeParameter(list[i].ToString(), list[i + 1].ToString()));
  231:                             dt.Columns.Add(new DataColumn(list[i].ToString().Replace("@", ""), System.Type.GetType("System.String")));
  232:                         }
  233:                         else if (o.GetType() == typeof(int))
  234:                         {
  235:                             sco.Parameters.Add(new SqlCeParameter(list[i].ToString(), (int)list[i + 1]));
  236:                             dt.Columns.Add(new DataColumn(list[i].ToString().Replace("@", ""), System.Type.GetType("System.Int32")));
  237:                         }
  238:                     }
  239:                 }
  240:  
  241:                 sc.Open();
  242:  
  243:                 sdr = sco.ExecuteReader();
  244:  
  245:                 while (sdr.Read())
  246:                 {
  247:                     dr = dt.NewRow();
  248:  
  249:                     for (i = 0; i < dt.Columns.Count; i++)
  250:                     {
  251:                         dr[i] = sdr[dt.Columns[i].ColumnName];
  252:                     }
  253:                 }
  254:  
  255:                 sc.Close();
  256:             }
  257:             finally
  258:             {
  259:  
  260:                 if (sc != null) sc.Close();
  261:  
  262:                 if (sdr != null) sdr.Close();
  263:             }
  264:  
  265:             return dt;
  266:         }
  267:  
  268:         ////////////////////////////////////////////////////////////////////////////
  269:  
  270:         /// <summary>
  271:         ///
  272:         /// </summary>
  273:         public DataTable Select(string sql)
  274:         {
  275:             // below: return a DataTable of result rows
  276:  
  277:             DataSet ds = new DataSet();
  278:             DataTable dt = new DataTable();
  279:             SqlCeDataAdapter da = new SqlCeDataAdapter();
  280:  
  281:             try
  282:             {
  283:                 sc = new SqlCeConnection(connectionString);
  284:                 sco = new SqlCeCommand(sql, sc);
  285:  
  286:                 sc.Open();
  287:  
  288:                 da.SelectCommand = sco;
  289:  
  290:                 da.Fill(ds);
  291:  
  292:                 sc.Close();
  293:  
  294:                 dt = ds.Tables[0];
  295:             }
  296:             catch { dt = null; }
  297:  
  298:             return dt;
  299:         }
  300:  
  301:         ////////////////////////////////////////////////////////////////////////////
  302:  
  303:         /// <summary>
  304:         /// Return a DataTable of result rows and use a database_file. This will assume the file to be in the data directory.
  305:         /// </summary>
  306:         public DataTable Select(string sql, string database_file)
  307:         {
  308:             // below:
  309:             DataSet ds = new DataSet();
  310:             DataTable dt = new DataTable();
  311:             SqlCeDataAdapter da = new SqlCeDataAdapter();
  312:  
  313:             try
  314:             {
  315:                 sc = new SqlCeConnection(Connection_String_Specific(database_file));
  316:                 sco = new SqlCeCommand(sql, sc);
  317:  
  318:                 sc.Open();
  319:  
  320:                 da.SelectCommand = sco;
  321:  
  322:                 da.Fill(ds);
  323:  
  324:                 sc.Close();
  325:  
  326:                 dt = ds.Tables[0];
  327:             }
  328:             catch (Exception)
  329:             {
  330:                 dt = null;
  331:             }
  332:  
  333:             return dt;
  334:         }
  335:  
  336:         ////////////////////////////////////////////////////////////////////////////
  337:  
  338:         /// <summary>
  339:         ///
  340:         /// </summary>
  341:         public string Misc_Select(string name)
  342:         {
  343:             string s;
  344:  
  345:             s = Scalar(@"SELECT value FROM ia_misc WHERE name = '" + name + "'");
  346:  
  347:             return s;
  348:         }
  349:  
  350:         ////////////////////////////////////////////////////////////////////////////
  351:  
  352:         /// <summary>
  353:         ///
  354:         /// </summary>
  355:         public int Misc_Select(string name, out ArrayList al)
  356:         {
  357:             int op;
  358:             string s;
  359:             string[] st;
  360:  
  361:             op = 0;
  362:  
  363:             al = new ArrayList(1);
  364:  
  365:             s = Scalar(@"SELECT value FROM ia_misc WHERE name = '" + name + "'");
  366:  
  367:             if (s != null)
  368:             {
  369:                 st = s.Split('|');
  370:                 al = new ArrayList(st.Length);
  371:                 al.Clear();
  372:  
  373:                 foreach (string t in st) al.Add(t);
  374:  
  375:                 if (al.Count > 0) op = 1;
  376:                 else op = 0;
  377:             }
  378:             else op = -1;
  379:  
  380:             return op;
  381:         }
  382:  
  383:         ////////////////////////////////////////////////////////////////////////////
  384:  
  385:         /// <summary>
  386:         ///
  387:         /// </summary>
  388:         public int Misc_Select(string name, out DataTable dt)
  389:         {
  390:             int op;
  391:             string s;
  392:             DataRow dr;
  393:             XmlNode xn;
  394:             XmlDocument xd;
  395:  
  396:             op = 0;
  397:  
  398:             dt = new DataTable(name);
  399:  
  400:             s = Scalar(@"SELECT value_xml FROM ia_misc WHERE name = '" + name + "'");
  401:  
  402:             if (s != null && s != "")
  403:             {
  404:                 xd = new XmlDocument();
  405:                 xd.LoadXml(s);
  406:  
  407:                 dt = new DataTable(xd.DocumentElement.Name);
  408:  
  409:                 // below: collect table columns
  410:                 xn = xd.DocumentElement.FirstChild;
  411:                 foreach (XmlNode n in xn.ChildNodes)
  412:                 {
  413:                     if (n.Name == "user_id") dt.Columns.Add("user_id", typeof(System.Guid));
  414:                     else dt.Columns.Add(n.Name);
  415:                 }
  416:  
  417:                 // below: collect row values
  418:                 foreach (XmlNode n in xd.DocumentElement.ChildNodes)
  419:                 {
  420:                     dr = dt.NewRow();
  421:  
  422:                     foreach (XmlNode o in n.ChildNodes)
  423:                     {
  424:                         if (o.Name == "user_id")
  425:                         {
  426:                             if (o.InnerText == "") dr[o.Name] = DBNull.Value;
  427:                             else dr[o.Name] = o.InnerText;
  428:                         }
  429:                         else dr[o.Name] = o.InnerText;
  430:                     }
  431:  
  432:                     dt.Rows.Add(dr);
  433:                 }
  434:  
  435:                 dt.AcceptChanges();
  436:  
  437:                 if (dt.Rows.Count > 0) op = 1;
  438:                 else op = 0;
  439:             }
  440:             else op = -1;
  441:  
  442:             return op;
  443:         }
  444:  
  445:         ////////////////////////////////////////////////////////////////////////////
  446:  
  447:         /// <summary>
  448:         ///
  449:         /// </summary>
  450:         public void Misc_Update(string name, string value)
  451:         {
  452:             Sql(@"UPDATE ia_misc SET value = '" + value + "' WHERE name = '" + name + "'");
  453:         }
  454:  
  455:         ////////////////////////////////////////////////////////////////////////////
  456:  
  457:         /// <summary>
  458:         ///
  459:         /// </summary>
  460:         public void Misc_Update(string name, ArrayList al)
  461:         {
  462:             StringBuilder sb;
  463:  
  464:             if (al.Count > 0)
  465:             {
  466:                 sb = new StringBuilder(al.Count + 1);
  467:                 sb.Length = 0;
  468:  
  469:                 foreach (string t in al) sb.Append(t + "|");
  470:                 sb = sb.Remove(sb.Length - 1, 1);
  471:             }
  472:             else
  473:             {
  474:                 sb = new StringBuilder(1);
  475:                 sb.Length = 0;
  476:             }
  477:  
  478:             Sql(@"UPDATE ia_misc SET value = '" + sb.ToString() + "' WHERE name = '" + name + "'");
  479:         }
  480:  
  481:         ////////////////////////////////////////////////////////////////////////////
  482:  
  483:         /// <summary>
  484:         ///
  485:         /// </summary>
  486:         public void Misc_Update(string name, SortedList sl)
  487:         {
  488:             StringBuilder sb;
  489:  
  490:             if (sl.Count > 0)
  491:             {
  492:                 sb = new StringBuilder(sl.Count + 1);
  493:                 sb.Length = 0;
  494:  
  495:                 foreach (string t in sl.Keys) sb.Append(t + "|");
  496:                 sb = sb.Remove(sb.Length - 1, 1);
  497:             }
  498:             else
  499:             {
  500:                 sb = new StringBuilder(1);
  501:                 sb.Length = 0;
  502:             }
  503:  
  504:             Sql(@"UPDATE ia_misc SET value = '" + sb.ToString() + "' WHERE name = '" + name + "'");
  505:         }
  506:  
  507:         ////////////////////////////////////////////////////////////////////////////
  508:  
  509:         /// <summary>
  510:         ///
  511:         /// </summary>
  512:         public void Misc_Update(string name, DataTable dt)
  513:         {
  514:             StringBuilder sb;
  515:  
  516:             if (dt.Rows.Count > 0)
  517:             {
  518:                 sb = new StringBuilder(dt.Rows.Count + 1);
  519:                 sb.Length = 0;
  520:  
  521:                 sb = sb.Append("<" + name + ">");
  522:  
  523:                 foreach (DataRow r in dt.Rows)
  524:                 {
  525:                     sb = sb.Append("<row>");
  526:  
  527:                     foreach (DataColumn c in dt.Columns)
  528:                     {
  529:                         sb = sb.Append("<" + c.ColumnName + ">");
  530:  
  531:                         sb.Append(r[c.ColumnName].ToString());
  532:  
  533:                         sb = sb.Append("</" + c.ColumnName + ">");
  534:                     }
  535:  
  536:                     sb = sb.Append("</row>");
  537:                 }
  538:  
  539:                 sb = sb.Append("</" + name + ">");
  540:             }
  541:             else
  542:             {
  543:                 sb = new StringBuilder(1);
  544:                 sb.Length = 0;
  545:             }
  546:  
  547:             Sql(@"UPDATE ia_misc SET value_xml = '" + sb.ToString() + "' WHERE name = '" + name + "'");
  548:         }
  549:  
  550:         ////////////////////////////////////////////////////////////////////////////
  551:  
  552:         /// <summary>
  553:         ///
  554:         /// </summary>
  555:         public void Misc_Delete(string name)
  556:         {
  557:             Sql(@"DELETE FROM ia_misc WHERE name = '" + name + "'");
  558:         }
  559:  
  560:         ////////////////////////////////////////////////////////////////////////////
  561:  
  562:         /// <summary>
  563:         ///
  564:         /// </summary>
  565:         public void Misc_Insert(string name)
  566:         {
  567:             Sql(@"INSERT INTO ia_misc (name) VALUES ('" + name + "')");
  568:         }
  569:  
  570:         ////////////////////////////////////////////////////////////////////////////
  571:  
  572:         /// <summary>
  573:         ///
  574:         /// </summary>
  575:         public string Scalar(string sql)
  576:         {
  577:             // below: return a scaler
  578:             string s;
  579:  
  580:             sc = new SqlCeConnection(connectionString);
  581:             sco = new SqlCeCommand(sql, sc);
  582:  
  583:             sc.Open();
  584:  
  585:             try { s = sco.ExecuteScalar().ToString(); }
  586:             catch { s = null; }
  587:  
  588:             sc.Close();
  589:  
  590:             return s;
  591:         }
  592:  
  593:         ////////////////////////////////////////////////////////////////////////////
  594:  
  595:         /// <summary>
  596:         ///
  597:         /// </summary>
  598:         public int Scalar_Integer(string sql)
  599:         {
  600:             // below: return a DataTable of result rows
  601:             int n;
  602:  
  603:             sc = new SqlCeConnection(connectionString);
  604:             sco = new SqlCeCommand(sql, sc);
  605:  
  606:             sc.Open();
  607:  
  608:             try
  609:             {
  610:                 n = (System.Int32)sco.ExecuteScalar();
  611:             }
  612:             catch
  613:             {
  614:                 n = 0;
  615:             }
  616:  
  617:             sc.Close();
  618:  
  619:             return n;
  620:         }
  621:  
  622:         ////////////////////////////////////////////////////////////////////////////
  623:  
  624:         /// <summary>
  625:         ///
  626:         /// </summary>
  627:         public int Scalar_SmallInteger(string sql)
  628:         {
  629:             // below: return a DataTable of result rows
  630:             int n;
  631:  
  632:             sc = new SqlCeConnection(connectionString);
  633:             sco = new SqlCeCommand(sql, sc);
  634:  
  635:             sc.Open();
  636:  
  637:             try
  638:             {
  639:                 n = (System.Int16)sco.ExecuteScalar();
  640:             }
  641:             catch
  642:             {
  643:                 n = 0;
  644:             }
  645:  
  646:             sc.Close();
  647:  
  648:             return n;
  649:         }
  650:  
  651:         ////////////////////////////////////////////////////////////////////////////
  652:  
  653:         /// <summary>
  654:         ///
  655:         /// </summary>
  656:         public string SmallDateTime(DateTime dt)
  657:         {
  658:             // below: return an SQL Server friendly string of a smalldatetime value
  659:             string s;
  660:  
  661:             //s = "'" + dt.ToString("yyyy-MM-ddTHH:mm:ss") + "'";
  662:             s = dt.ToString("yyyy-MM-ddTHH:mm:ss");
  663:  
  664:             return s;
  665:         }
  666:  
  667:         ////////////////////////////////////////////////////////////////////////////
  668:  
  669:         /// <summary>
  670:         ///
  671:         /// </summary>
  672:         public string Range_Sql(ArrayList al, string term)
  673:         {
  674:             // below: this will take a special ArrayList of ranges and will construct an SQL format that correspond to the array.
  675:             // input will look like al = [1,2,4,6,7,15,20-22,34-36,38], output will look like "(sr.dn>16 AND sr.dn<19) OR sr.dn=22"
  676:  
  677:             string sql, u, v;
  678:             string[] sp;
  679:             StringBuilder sb;
  680:  
  681:             sql = "";
  682:  
  683:             if (al.Count > 0)
  684:             {
  685:                 sb = new StringBuilder((term.Length + 15) * al.Count);
  686:                 sb.Length = 0;
  687:  
  688:                 foreach (string s in al)
  689:                 {
  690:                     sp = s.Split('-');
  691:  
  692:                     if (sp.Length == 1)
  693:                     {
  694:                         // single value
  695:                         sb.Append(term + "=" + s + " OR ");
  696:                     }
  697:                     else if (sp.Length == 2)
  698:                     {
  699:                         // range
  700:                         u = sp[0]; v = sp[1];
  701:                         sb.Append("(" + term + ">=" + u + " AND " + term + "<=" + v + ") OR ");
  702:                     }
  703:                 }
  704:  
  705:                 sql = sb.ToString();
  706:                 sql = sql.Remove(sql.Length - 4, 4);
  707:             }
  708:  
  709:  
  710:             return sql;
  711:         }
  712:  
  713:         ////////////////////////////////////////////////////////////////////////////
  714:  
  715:         /// <summary>
  716:         ///
  717:         /// </summary>
  718:         public bool Xml_Import(string tableName, XDocument xd, out string r)
  719:         {
  720:             bool b;
  721:             string parameters, values;
  722:             string c;//, path;
  723:             //DataSet ds;
  724:             SqlCeDataAdapter sda;
  725:  
  726:             r = "";
  727:  
  728:             // below: first we delete all records in table
  729:             Sql(@"DELETE FROM " + tableName);
  730:  
  731:             // below: iterate through Xml records and insert into database
  732:  
  733:             //ds.ReadXml(file);
  734:  
  735:             sc = new SqlCeConnection(connectionString);
  736:             sc.Open();
  737:  
  738:             c = @"SELECT * FROM [" + tableName + @"]";
  739:             sda = new SqlCeDataAdapter(c, sc);
  740:             //scb = new SqlCeCommandBuilder(sda);
  741:  
  742:             try
  743:             {
  744:                 foreach (XElement xe in xd.Descendants("row"))
  745:                 {
  746:                     parameters = values = null;
  747:  
  748:                     foreach (XElement xe2 in xe.Descendants())
  749:                     {
  750:                         if (xe2.Name != "id")
  751:                         {
  752:                             parameters += xe2.Name + ",";
  753:                             values += "'" + xe2.Value + "',";
  754:                         }
  755:                     }
  756:  
  757:                     parameters = parameters.Remove(parameters.Length - 1, 1);
  758:                     values = values.Remove(values.Length - 1, 1);
  759:  
  760:                     Sql(@"INSERT INTO " + tableName + "(" + parameters + ") VALUES (" + values + ")");
  761:                 }
  762:  
  763:                 b = true;
  764:             }
  765:             catch (Exception ex)
  766:             {
  767:                 r = "Exception: in Xml_Import(): " + ex.Message;
  768:                 b = false;
  769:             }
  770:             finally
  771:             {
  772:             }
  773:  
  774:             return b;
  775:         }
  776:  
  777:         ////////////////////////////////////////////////////////////////////////////
  778:  
  779:         /// <summary>
  780:         ///
  781:         /// </summary>
  782:         public bool Xml_Export(string tableName, string file)
  783:         {
  784:             // below: perform dump or backup of database table data into an XML document
  785:             bool b;
  786:             string c, path;
  787:             DataSet ds;
  788:             SqlCeDataAdapter sda;
  789:  
  790:             c = @"SELECT * FROM [" + tableName + @"]";
  791:             sc = new SqlCeConnection(connectionString);
  792:             sc.Open();
  793:  
  794:             ds = new DataSet("ia_ngn");
  795:             sda = new SqlCeDataAdapter(c, sc);
  796:  
  797:             try
  798:             {
  799:                 sda.Fill(ds, tableName);
  800:  
  801:                 path = Ia.Cs.Default.Absolute_Path();
  802:  
  803:                 file = path + file;
  804:                 ds.WriteXml(file, XmlWriteMode.WriteSchema);
  805:  
  806:                 b = true;
  807:             }
  808:             catch (Exception) { b = false; }
  809:             finally
  810:             {
  811:                 sc.Close();
  812:             }
  813:  
  814:             return b;
  815:         }
  816:  
  817:         ////////////////////////////////////////////////////////////////////////////
  818:  
  819:         /// <summary>
  820:         /// Log a standard logging entry into a special database table
  821:         /// </summary>
  822:         public void Log(int type_id, int direction_type_id, int system_id, int process_id, int function_id, string detail, DateTime created)
  823:         {
  824:             string sql;
  825:  
  826:             // See table ia_log and log.xml
  827:  
  828:             /*
  829:             CREATE TABLE [dbo].[ia_log]
  830:             (
  831:                [id]                    int    IDENTITY(1,1) CONSTRAINT [ia_log_id_pk] PRIMARY KEY,
  832:                [type_id]                tinyint NULL,
  833:                [direction_type_id]    tinyint NULL,
  834:                [system_id]            smallint NULL,
  835:                [process_id]            smallint NULL,
  836:                [function_id]            smallint NULL,
  837:                [detail]                ntext NULL,
  838:                [created]                smalldatetime NULL
  839:             )
  840:             */
  841:  
  842: #if WINDOWS_FORM
  843:             sql = "INSERT INTO [ia_log] ([type_id],[direction_type_id],[system_id],[process_id],[function_id],[detail],[created]) VALUES (" + type_id + "," + direction_type_id + "," + system_id + "," + process_id + "," + function_id + ",'" + detail + "','" + SmallDateTime(created) + "')";
  844: #else
  845:             sql = "INSERT INTO [ia_log] ([type_id],[direction_type_id],[system_id],[process_id],[function_id],[detail],[created]) VALUES (" + type_id + "," + direction_type_id + "," + system_id + "," + process_id + "," + function_id + ",'" + HttpUtility.HtmlEncode(detail) + "','" + SmallDateTime(created) + "')";
  846: #endif
  847:  
  848:             Sql(sql);
  849:         }
  850:  
  851:         ////////////////////////////////////////////////////////////////////////////
  852:         ////////////////////////////////////////////////////////////////////////////
  853:  
  854:         /// <summary>
  855:         ///
  856:         /// </summary>
  857:         public int Update(DataTable in_dt, string tableName, string select_command, string primary_key, string[] in_field, string[] field, F[] field_rule, bool synch, string delete_rule, out string result)
  858:         {
  859:             bool identical, keep_latest;
  860:             int op, c, count, count_in, count_delete;
  861:             F rule;
  862:             string command;
  863:  
  864:             // TEMP
  865:             //string temp = "", temp_dt_str, temp_in_dt_str; // TEMP
  866:  
  867:             // TEMP
  868:             //string temp = "";
  869:  
  870:             int i, j;
  871:             Hashtable ht;
  872:  
  873:             keep_latest = false;
  874:  
  875:             al = new ArrayList(1000);
  876:             from_al = new ArrayList(1000);
  877:             insert_al = new ArrayList(1000);
  878:             delete_al = new ArrayList(1000);
  879:  
  880:             ht = new Hashtable(1000);
  881:  
  882:             DateTime sdt, in_sdt;
  883:  
  884:             DataRow dr;
  885:             DataTable dt;
  886:             DataSet ds;
  887:             SqlCeDataAdapter sda;
  888:             SqlCeCommandBuilder scb;
  889:  
  890:             op = 0;
  891:             c = count = count_in = count_delete = 0;
  892:  
  893:             //progress = 0;
  894:  
  895:             ds = new DataSet("ia");
  896:             sc = new SqlCeConnection(connectionString);
  897:  
  898:             sc.Open();
  899:             command = select_command;
  900:             sda = new SqlCeDataAdapter();
  901:             scb = new SqlCeCommandBuilder(sda);
  902:             sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  903:             sda.SelectCommand = new SqlCeCommand(command, sc);
  904:  
  905:             result = "";
  906:  
  907:             dt = null;
  908:  
  909:             // below: I will check if the records have a keep_latest field. This field means I will ignore the new record if the keep_latest date is older then
  910:             // the existing record.
  911:             for (int n = 0; n < in_field.Length; n++)
  912:             {
  913:                 rule = field_rule[n];
  914:  
  915:                 if (rule == F.Sdt_Keep_Latest)
  916:                 {
  917:                     keep_latest = true; break;
  918:                 }
  919:             }
  920:  
  921:             try
  922:             {
  923:                 sda.Fill(ds, tableName);
  924:  
  925:                 dt = ds.Tables[0];
  926:  
  927:                 if (in_dt != null)
  928:                 {
  929:                     count_in = in_dt.Rows.Count;
  930:  
  931:                     if (dt != null)
  932:                     {
  933:                         count = dt.Rows.Count;
  934:  
  935:                         if (in_dt.Rows.Count > 0)
  936:                         {
  937:                             if (synch)
  938:                             {
  939:                                 // below: compair two lists to find records in in_dt that are not in dt
  940:                                 foreach (DataRow r in dt.Rows) al.Add(r[primary_key].ToString());
  941:                                 foreach (DataRow r in in_dt.Rows) from_al.Add(r[primary_key].ToString());
  942:  
  943:                                 al.Sort();
  944:                                 from_al.Sort();
  945:  
  946:                                 i = j = 0;
  947:  
  948:                                 ArrayList list1, list2;
  949:  
  950:                                 list1 = from_al;
  951:                                 list2 = al;
  952:  
  953:                                 while (i < list1.Count)
  954:                                 {
  955:                                     if (j == list2.Count) break;
  956:                                     IComparable obj1 = list1[i] as IComparable;
  957:                                     IComparable obj2 = list2[j] as IComparable;
  958:  
  959:                                     int cmp = obj1.CompareTo(obj2);
  960:  
  961:                                     switch (Math.Sign(cmp))
  962:                                     {
  963:                                         case 0: ++i; ++j; break;
  964:                                         case 1: delete_al.Add(list2[j].ToString()); ++j; break;
  965:                                         case -1: insert_al.Add(list1[i].ToString()); ++i; break;
  966:                                     }
  967:                                 }
  968:  
  969:                                 while (i < list1.Count) // we reached the end of list 2 first.
  970:                                 {
  971:                                     insert_al.Add(list1[i].ToString()); ++i;
  972:                                 }
  973:  
  974:                                 while (j < list2.Count) // we reached the end of list 1 first.
  975:                                 {
  976:                                     delete_al.Add(list2[j].ToString()); ++j;
  977:                                 }
  978:  
  979:                                 if (delete_al.Count > 0)
  980:                                 {
  981:                                     for (i = 0; i < delete_al.Count && i < 100; i++)
  982:                                     {
  983:                                         // We will delete it, or its contents according to the deletion rules of the tableName
  984:  
  985:                                         dr = dt.Rows.Find(delete_al[i].ToString());
  986:  
  987:                                         if (delete_rule == "all")
  988:                                         {
  989:                                             dr.Delete();
  990:                                         }
  991:                                         else if (delete_rule == "keep primary key")
  992:                                         {
  993:                                             // below: this will delete everything but keep only the primary key
  994:  
  995:                                             for (int n = 0; n < in_field.Length; n++)
  996:                                             {
  997:                                                 if (field[n].ToString() != primary_key)
  998:                                                 {
  999:                                                     rule = field_rule[n];
 1000:  
 1001:                                                     if (rule == F.Bit || rule == F.In || rule == F.St || rule == F.Sdt || rule == F.Sdt_Keep_Latest)
 1002:                                                     {
 1003:                                                         dr[field[n].ToString()] = DBNull.Value;
 1004:                                                     }
 1005:                                                     else if (rule == F.Up)
 1006:                                                     {
 1007:                                                         dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
 1008:                                                     }
 1009:                                                 }
 1010:                                             }
 1011:                                         }
 1012:  
 1013:                                         count_delete++;
 1014:                                     }
 1015:                                 }
 1016:                             }
 1017:  
 1018:                             foreach (DataRow in_dr in in_dt.Rows)
 1019:                             {
 1020:                                 // below: collect relevent values:
 1021:  
 1022:                                 dr = dt.Rows.Find(long.Parse(in_dr[primary_key].ToString()));
 1023:  
 1024:                                 //progress = c / count;
 1025:  
 1026:                                 if (dr != null)
 1027:                                 {
 1028:                                     // below: check if rows are identical
 1029:  
 1030:                                     identical = true;
 1031:  
 1032:                                     for (int n = 0; n < in_field.Length; n++)
 1033:                                     {
 1034:                                         rule = field_rule[n];
 1035:  
 1036:                                         if (rule == F.Bit)
 1037:                                         {
 1038:                                             try
 1039:                                             {
 1040:                                                 if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
 1041:                                                 {
 1042:                                                     identical = false;
 1043:                                                     break;
 1044:                                                 }
 1045:                                             }
 1046:                                             catch (InvalidCastException)
 1047:                                             {
 1048:                                                 identical = false;
 1049:                                                 break;
 1050:                                             }
 1051:  
 1052:                                         }
 1053:                                         else if (rule == F.In)
 1054:                                         {
 1055:                                             try
 1056:                                             {
 1057:                                                 if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
 1058:                                                 {
 1059:                                                     identical = false;
 1060:                                                     break;
 1061:                                                 }
 1062:                                             }
 1063:                                             catch (FormatException)
 1064:                                             {
 1065:                                                 identical = false;
 1066:                                                 break;
 1067:                                             }
 1068:                                         }
 1069:                                         else if (rule == F.St)
 1070:                                         {
 1071:                                             if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { }
 1072:                                             else if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] != DBNull.Value) { identical = false; break; }
 1073:                                             else if (dr[field[n].ToString()] != DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { identical = false; break; }
 1074:                                             else if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
 1075:                                             {
 1076:                                                 identical = false;
 1077:                                                 break;
 1078:                                             }
 1079:                                         }
 1080:                                         else if (rule == F.Sdt)
 1081:                                         {
 1082:                                             sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
 1083:                                             in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
 1084:  
 1085:                                             // below: if in_sdt lays within 1 minute of sdt they are identical
 1086:  
 1087:                                             if (in_sdt > sdt.AddMinutes(1) || in_sdt < sdt.AddMinutes(-1))
 1088:                                             {
 1089:                                                 identical = false;
 1090:                                                 break;
 1091:                                             }
 1092:                                         }
 1093:                                         else { }
 1094:                                     }
 1095:  
 1096:                                     //if (dr["dn"].ToString() == "25645818") { }
 1097:  
 1098:                                     if (keep_latest)
 1099:                                     {
 1100:                                         // identical = true;
 1101:  
 1102:                                         for (int n = 0; n < in_field.Length; n++)
 1103:                                         {
 1104:                                             rule = field_rule[n];
 1105:  
 1106:                                             if (rule == F.Sdt_Keep_Latest)
 1107:                                             {
 1108:                                                 // below: this will keep the record as same with no change if the new date is older than the old date
 1109:  
 1110:                                                 sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
 1111:                                                 in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
 1112:  
 1113:                                                 // below: if in_sdt is less than sdt they are identical
 1114:  
 1115:                                                 if (in_sdt >= sdt) identical = false;
 1116:                                                 else identical = true;
 1117:  
 1118:                                                 break;
 1119:                                             }
 1120:                                         }
 1121:                                     }
 1122:  
 1123:                                     if (identical)
 1124:                                     {
 1125:                                         // below: rows are the exact same. Do nothing
 1126:                                     }
 1127:                                     else
 1128:                                     {
 1129:                                         // below: row was updated
 1130:                                         for (int n = 0; n < in_field.Length; n++)
 1131:                                         {
 1132:                                             rule = field_rule[n];
 1133:  
 1134:                                             if (rule == F.Bit)
 1135:                                             {
 1136:                                                 try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
 1137:                                                 catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
 1138:                                             }
 1139:                                             else if (rule == F.In)
 1140:                                             {
 1141:                                                 try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
 1142:                                                 catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
 1143:                                             }
 1144:                                             else if (rule == F.St)
 1145:                                             {
 1146:                                                 if (in_dr[in_field[n].ToString()] == DBNull.Value) dr[field[n].ToString()] = DBNull.Value;
 1147:                                                 else dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
 1148:                                             }
 1149:                                             else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
 1150:                                             {
 1151:                                                 in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
 1152:                                                 dr[field[n].ToString()] = SmallDateTime(in_sdt);
 1153:                                             }
 1154:                                             else if (rule == F.Up)
 1155:                                             {
 1156:                                                 dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
 1157:                                             }
 1158:                                         }
 1159:  
 1160:                                         c++;
 1161:                                     }
 1162:                                 }
 1163:                                 else
 1164:                                 {
 1165:                                     // below: row does not exists, we will add it to database
 1166:  
 1167:                                     dr = dt.NewRow();
 1168:  
 1169:                                     for (int n = 0; n < in_field.Length; n++)
 1170:                                     {
 1171:                                         rule = field_rule[n];
 1172:  
 1173:                                         if (rule == F.Bit)
 1174:                                         {
 1175:                                             try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
 1176:                                             catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
 1177:                                         }
 1178:                                         else if (rule == F.In)
 1179:                                         {
 1180:                                             try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
 1181:                                             catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
 1182:                                         }
 1183:                                         else if (rule == F.St)
 1184:                                         {
 1185:                                             dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
 1186:                                         }
 1187:                                         else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
 1188:                                         {
 1189:                                             in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
 1190:                                             dr[field[n].ToString()] = SmallDateTime(in_sdt);
 1191:                                         }
 1192:                                         else if (rule == F.Cr || rule == F.Up)
 1193:                                         {
 1194:                                             dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
 1195:                                         }
 1196:                                     }
 1197:  
 1198:                                     dt.Rows.Add(dr);
 1199:                                     c++;
 1200:                                 }
 1201:                             }
 1202:  
 1203:                             scb.GetUpdateCommand();
 1204:                             sda.Update(ds, tableName);
 1205:                             sc.Close();
 1206:  
 1207:                             result = "(" + c + "-" + count_delete + "/" + count_in + "/" + count + ")";
 1208:  
 1209:                             if (c > 0 || count_delete > 0) op = 1;
 1210:                         }
 1211:                         else
 1212:                         {
 1213:                             result += "(0-0/0/*)";
 1214:                             op = 0;
 1215:                         }
 1216:                     }
 1217:                     else
 1218:                     {
 1219:                         result += "(0-0/*/null)";
 1220:                         op = -1;
 1221:                     }
 1222:                 }
 1223:                 else
 1224:                 {
 1225:                     result += "(0-0/null/*)";
 1226:                     op = -1;
 1227:                 }
 1228:             }
 1229:             catch (Exception ex)
 1230:             {
 1231: #if DEBUG
 1232:                 result = "Ia.Ngn.Cs.Application.Update(): " + ex.ToString(); // +"  TEMP=[" + temp + "]"; // TEMP
 1233: #else
 1234:                 result = "Ia.Ngn.Cs.Application.Update(): " + ex.Message;
 1235: #endif
 1236:  
 1237:                 op = -1;
 1238:             }
 1239:  
 1240:             return op;
 1241:         }
 1242:  
 1243:         ////////////////////////////////////////////////////////////////////////////
 1244:         ////////////////////////////////////////////////////////////////////////////
 1245:     }
 1246: }
 1247:  
 1248: /*
 1249: 
 1250: <%@ Page Language="C#" %>
 1251: <%@ Import Namespace="System.Data" %>
 1252: <%@ Import Namespace="System.Data.SqlClient" %>
 1253: <%@ Import Namespace="System.Xml" %>
 1254: <%@ Import Namespace="System.Collections" %>
 1255: <%@ Import Namespace="System.Globalization" %>
 1256: <%@ Import Namespace="System.ComponentModel" %>
 1257: 
 1258: <script runat="server">
 1259: 
 1260:     public void Page_Load(Object sender, EventArgs e) {  
 1261: 
 1262:         //QUICK OVERVIEW OF SYNTAX:
 1263:         / *
 1264:         int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
 1265:         int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
 1266:         string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
 1267:         string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());      
 1268:         DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");      
 1269:         DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
 1270:         DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
 1271:         DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
 1272:         DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
 1273:         DataTable dtEmployees = GetDataTable("SELECT * FROM Employees WHERE Country=@currentCountry AND BirthDate<@cutOffDate AND EmployeeId>@employeeId","@currentCountry,varchar/255,USA;@cutOffDate,date," + cutOffDate.ToString() + ";@employeeId,int,2");
 1274:         DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
 1275:         DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
 1276:         * /
 1277: 
 1278:         //get an integer
 1279:         int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
 1280:         litShow1.Text = "There are " + numberOfEmployees.ToString() + " employees";
 1281:         
 1282:         //get an integer with parameters
 1283:         string selectedCountry = "UK";
 1284:         int numberOfEmployees2 = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
 1285:         litShow2.Text = "There are " + numberOfEmployees2.ToString() + " employees from: " + selectedCountry;
 1286:         
 1287:         //get a string
 1288:         string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
 1289:         litShow3.Text = "The last name of the person selected is <b>" + currentLastName + "</b>. ";
 1290:         
 1291:         //get a string with parameters
 1292:         int selectedId = 4;
 1293:         string currentLastName2 = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
 1294:         litShow4.Text = "The last name of the person with id " + selectedId.ToString() + " is <b>" + currentLastName2 + "</b>. ";
 1295:         
 1296:         //get a date
 1297:         CultureInfo ci = new CultureInfo("en-US");        
 1298:         DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
 1299:         litShow5.Text = "Your order shipped on <b>" + shippedDate.ToString("dddd, MMMM d, yyyy",ci) + "</b>. ";
 1300: 
 1301:         //get a date with parameters
 1302:         int currentOrderId = 10264;
 1303:         CultureInfo ci2 = new CultureInfo("en-US");        
 1304:         DateTime shippedDate2 = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
 1305:         litShow6.Text = "Your order shipped on <b>" + shippedDate2.ToString("dddd, MMMM d, yyyy",ci2) + "</b>. ";
 1306: 
 1307:         //get one record
 1308:         DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
 1309:         litShow7.Text = "The selected employee is <b>" + drEmployee["FirstName"].ToString() + " " + drEmployee["LastName"].ToString() + "</b>";
 1310:         
 1311:         //get one record with parameters
 1312:         int currentEmployeeId = 8;
 1313:         DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
 1314:         litShow8.Text = "The employee with id#" + currentEmployeeId.ToString() + " is <b>" + drEmployee2["FirstName"].ToString() + " " + drEmployee2["LastName"].ToString() + "</b>";
 1315:         
 1316:         //get several records
 1317:         DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
 1318:         foreach(DataRow row in dtEmployees.Rows) {
 1319:             litShow9.Text += row["LastName"].ToString() + ", " + row["FirstName"].ToString() + "<br>";
 1320:         }
 1321:         
 1322:         //get several records with parameters
 1323:         DateTime cutOffDate = DateTime.Parse("1/1/1963");
 1324:         DataTable dtEmployees2 = GetDataTable("SELECT * FROM Employees WHERE Country=@currentCountry AND BirthDate<@cutOffDate AND EmployeeId>@employeeId","@currentCountry,varchar/255,USA;@cutOffDate,date," + cutOffDate.ToString() + ";@employeeId,int,2");
 1325:         foreach(DataRow row in dtEmployees2.Rows) {
 1326:             litShow10.Text += row["EmployeeId"].ToString() + ". " + row["LastName"].ToString() + ", " + row["FirstName"].ToString() + " (" + row["Country"].ToString() + ")<br>";
 1327:         }
 1328:         
 1329:         //perform an SQL command
 1330:         DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
 1331:         
 1332:         //perform an SQL command
 1333:         int theId = 1;
 1334:         DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
 1335:         
 1336:     }
 1337:     
 1338:     //EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
 1339:     public int GetInteger(string sql) {    
 1340:         int r = 0;
 1341:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1342:         con.Open();
 1343:         SqlCeCommand cmd = new SqlCeCommand(sql,con);
 1344:         r = (int)cmd.ExecuteScalar();
 1345:         con.Close();    
 1346:         return r;
 1347:     }
 1348: 
 1349:     //EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255,UK");
 1350:     public int GetInteger(string sql, string parameterList) {    
 1351:         int r = 0;
 1352:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1353:         con.Open();
 1354:         SqlCeCommand cmd = new SqlCeCommand(sql,con);
 1355:         GetParameters(ref cmd, parameterList);
 1356:         r = (int)cmd.ExecuteScalar();
 1357:         con.Close();    
 1358:         return r;
 1359:     }
 1360:     
 1361:     //EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
 1362:     public string GetString(string sql) {    
 1363:         string rs = "";
 1364:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1365:         con.Open();
 1366:         SqlCeCommand cmd = new SqlCeCommand(sql,con);
 1367:         rs = (string)cmd.ExecuteScalar();
 1368:         con.Close();    
 1369:         return rs;
 1370:     }
 1371:     
 1372:     //EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeedId,int," + selectedId.ToString());
 1373:     public string GetString(string sql, string parameterList) {    
 1374:         string rs = "";
 1375:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1376:         con.Open();
 1377:         SqlCeCommand cmd = new SqlCeCommand(sql,con);
 1378:         GetParameters(ref cmd, parameterList);
 1379:         rs = (string)cmd.ExecuteScalar();
 1380:         con.Close();    
 1381:         return rs;
 1382:     }
 1383:     
 1384:     //EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
 1385:     public DateTime GetDate(string sql) {    
 1386:         DateTime rd;
 1387:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1388:         con.Open();
 1389:         SqlCeCommand cmd = new SqlCeCommand(sql,con);
 1390:         rd = (DateTime)cmd.ExecuteScalar();
 1391:         con.Close();    
 1392:         return rd;
 1393:     }
 1394:     
 1395:     
 1396:     //EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
 1397:     public DateTime GetDate(string sql, string parameterList) {    
 1398:         DateTime rd;
 1399:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1400:         con.Open();
 1401:         SqlCeCommand cmd = new SqlCeCommand(sql,con);
 1402:         GetParameters(ref cmd, parameterList);
 1403:         rd = (DateTime)cmd.ExecuteScalar();
 1404:         con.Close();    
 1405:         return rd;
 1406:     }
 1407: 
 1408:     //EXAMPLE: DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
 1409:     public DataRow GetDataRow(string sql) {
 1410:         DataSet ds = new DataSet();
 1411:         DataTable dt = new DataTable();
 1412:         SqlCeDataAdapter da = new SqlCeDataAdapter();
 1413:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1414:         SqlCeCommand cmd = new SqlCeCommand(sql, con);
 1415:         da.SelectCommand = cmd;
 1416:         da.Fill(ds);
 1417:         try {
 1418:             dt = ds.Tables[0];
 1419:             return dt.Rows[0];
 1420:         }
 1421:         catch {
 1422:             return null;
 1423:         }
 1424:     }
 1425: 
 1426:     //EXAMPLE: DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
 1427:     public DataRow GetDataRow(string sql,string parameterList) {
 1428:         DataSet ds = new DataSet();
 1429:         DataTable dt = new DataTable();
 1430:         SqlCeDataAdapter da = new SqlCeDataAdapter();
 1431:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1432:         SqlCeCommand cmd = new SqlCeCommand(sql, con);
 1433:         GetParameters(ref cmd, parameterList);
 1434:         da.SelectCommand = cmd;
 1435:         da.Fill(ds);
 1436:         try {
 1437:             dt = ds.Tables[0];
 1438:             return dt.Rows[0];
 1439:         }
 1440:         catch {
 1441:             return null;
 1442:         }
 1443:     }
 1444: 
 1445:     //EXAMPLE: DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
 1446:     public DataTable GetDataTable(string sql) {
 1447:         DataSet ds = new DataSet();
 1448:         DataTable dt = new DataTable();
 1449:         SqlCeDataAdapter da = new SqlCeDataAdapter();
 1450:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1451:         SqlCeCommand cmd = new SqlCeCommand(sql, con);
 1452:         da.SelectCommand = cmd;
 1453:         da.Fill(ds);
 1454:         try {
 1455:             dt = ds.Tables[0];
 1456:             return dt;
 1457:         }
 1458:         catch {
 1459:             return null;
 1460:         }
 1461:     }
 1462:     
 1463:     //EXAMPLE: DataTable dtEmployees2 = GetDataTable("SELECT * FROM Employees WHERE Country=@currentCountry AND BirthDate<@cutOffDate AND EmployeeId>@employeeId","@currentCountry,varchar/255,USA;@cutOffDate,date," + cutOffDate.ToString() + ";@employeeId,int,2");
 1464:     public DataTable GetDataTable(string sql, string parameterList) {
 1465:         //parameterList will be in this form: "@currentCountry,varchar/255,USA;@cutOffDate,date,1/1/1963"
 1466:         
 1467:         DataSet ds = new DataSet();
 1468:         DataTable dt = new DataTable();
 1469:         SqlCeDataAdapter da = new SqlCeDataAdapter();
 1470:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1471:         SqlCeCommand cmd = new SqlCeCommand(sql, con);
 1472:         GetParameters(ref cmd, parameterList);
 1473:         da.SelectCommand = cmd;
 1474:         da.Fill(ds);
 1475:         try {
 1476:             dt = ds.Tables[0];
 1477:             return dt;
 1478:         }
 1479:         catch {
 1480:             return null;
 1481:         }
 1482:     }
 1483: 
 1484:     //EXAMPLE: DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
 1485:     public void DoCommand(string sql) {
 1486:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1487:         SqlCeCommand cmd = new SqlCeCommand();
 1488:         cmd.CommandText = sql;
 1489:         cmd.Connection = con;
 1490:         con.Open();
 1491:         cmd.ExecuteNonQuery();
 1492:         con.Close();
 1493:     }
 1494: 
 1495:     //EXAMPLE: DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
 1496:     public void DoCommand(string sql, string parameterList) {
 1497:         SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
 1498:         SqlCeCommand cmd = new SqlCeCommand();
 1499:         cmd.CommandText = sql;
 1500:         GetParameters(ref cmd, parameterList);
 1501:         cmd.Connection = con;
 1502:         con.Open();
 1503:         cmd.ExecuteNonQuery();
 1504:         con.Close();
 1505:     }
 1506: 
 1507:     //used by the other methods
 1508:     public void GetParameters(ref SqlCeCommand cmd, string parameterList) {
 1509:         //build parameters from the parameter list
 1510:         string[] parameterLines = parameterList.Split(';');
 1511:         foreach(string parameterLine in parameterLines) { 
 1512:             //break up individual line
 1513:             string[] parts = parameterLine.Split(',');
 1514:             switch(parts[1].ToString().Substring(0,3).ToUpper()) {
 1515:                 case "VAR":
 1516:                     //get the size from e.g. "varchar/255"
 1517:                     string[] half = parts[1].ToString().Split('/');
 1518:                     int size = Int32.Parse(half[1]);
 1519:                     cmd.Parameters.Add(parts[0],SqlDbType.VarChar,size).Value = parts[2];
 1520:                     break;
 1521:                 case "DAT":
 1522:                     cmd.Parameters.Add(parts[0],SqlDbType.DateTime).Value = DateTime.Parse(parts[2]);
 1523:                     break;
 1524:                 case "INT":
 1525:                     cmd.Parameters.Add(parts[0],SqlDbType.Int).Value = Int32.Parse(parts[2]);
 1526:                     break;
 1527:             }
 1528:         }       
 1529:     }
 1530: */