شركة التطبيقات المتكاملة لتصميم النظم البرمجية الخاصة ش.ش.و.

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » SqlServerCe

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];
 
                                                    if (rule == F.Bit || rule == F.In || rule == F.St || rule == F.Sdt || rule == F.Sdt_Keep_Latest)
                                                    {
                                                        dr[field[n].ToString()] = DBNull.Value;
                                                    }
                                                    else if (rule == F.Up)
                                                    {
                                                        dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
                                                    }
                                                }
                                            }
                                        }
 
                                        count_delete++;
                                    }
                                }
                            }
 
                            foreach (DataRow in_dr in in_dt.Rows)
                            {
                                // below: collect relevent values:
 
                                dr = dt.Rows.Find(long.Parse(in_dr[primary_key].ToString()));
 
                                //progress = c / count;
 
                                if (dr != null)
                                {
                                    // below: check if rows are identical
 
                                    identical = true;
 
                                    for (int n = 0; n < in_field.Length; n++)
                                    {
                                        rule = field_rule[n];
 
                                        if (rule == F.Bit)
                                        {
                                            try
                                            {
                                                if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
                                                {
                                                    identical = false;
                                                    break;
                                                }
                                            }
                                            catch (InvalidCastException)
                                            {
                                                identical = false;
                                                break;
                                            }
 
                                        }
                                        else if (rule == F.In)
                                        {
                                            try
                                            {
                                                if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
                                                {
                                                    identical = false;
                                                    break;
                                                }
                                            }
                                            catch (FormatException)
                                            {
                                                identical = false;
                                                break;
                                            }
                                        }
                                        else if (rule == F.St)
                                        {
                                            if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { }
                                            else if (dr[field[n].ToString()] == DBNull.Value && in_dr[in_field[n].ToString()] != DBNull.Value) { identical = false; break; }
                                            else if (dr[field[n].ToString()] != DBNull.Value && in_dr[in_field[n].ToString()] == DBNull.Value) { identical = false; break; }
                                            else if (dr[field[n].ToString()].ToString() != in_dr[in_field[n].ToString()].ToString())
                                            {
                                                identical = false;
                                                break;
                                            }
                                        }
                                        else if (rule == F.Sdt)
                                        {
                                            sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
                                            in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
 
                                            // below: if in_sdt lays within 1 minute of sdt they are identical
 
                                            if (in_sdt > sdt.AddMinutes(1) || in_sdt < sdt.AddMinutes(-1))
                                            {
                                                identical = false;
                                                break;
                                            }
                                        }
                                        else { }
                                    }
 
                                    //if (dr["dn"].ToString() == "25645818") { }
 
                                    if (keep_latest)
                                    {
                                        // identical = true;
 
                                        for (int n = 0; n < in_field.Length; n++)
                                        {
                                            rule = field_rule[n];
 
                                            if (rule == F.Sdt_Keep_Latest)
                                            {
                                                // below: this will keep the record as same with no change if the new date is older than the old date
 
                                                sdt = DateTime.Parse(dr[field[n].ToString()].ToString());
                                                in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
 
                                                // below: if in_sdt is less than sdt they are identical
 
                                                if (in_sdt >= sdt) identical = false;
                                                else identical = true;
 
                                                break;
                                            }
                                        }
                                    }
 
                                    if (identical)
                                    {
                                        // below: rows are the exact same. Do nothing
                                    }
                                    else
                                    {
                                        // below: row was updated
                                        for (int n = 0; n < in_field.Length; n++)
                                        {
                                            rule = field_rule[n];
 
                                            if (rule == F.Bit)
                                            {
                                                try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
                                                catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
                                            }
                                            else if (rule == F.In)
                                            {
                                                try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
                                                catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
                                            }
                                            else if (rule == F.St)
                                            {
                                                if (in_dr[in_field[n].ToString()] == DBNull.Value) dr[field[n].ToString()] = DBNull.Value;
                                                else dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
                                            }
                                            else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
                                            {
                                                in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
                                                dr[field[n].ToString()] = SmallDateTime(in_sdt);
                                            }
                                            else if (rule == F.Up)
                                            {
                                                dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
                                            }
                                        }
 
                                        c++;
                                    }
                                }
                                else
                                {
                                    // below: row does not exists, we will add it to database
 
                                    dr = dt.NewRow();
 
                                    for (int n = 0; n < in_field.Length; n++)
                                    {
                                        rule = field_rule[n];
 
                                        if (rule == F.Bit)
                                        {
                                            try { dr[field[n].ToString()] = (bool)in_dr[in_field[n].ToString()]; }
                                            catch (InvalidCastException) { dr[field[n].ToString()] = DBNull.Value; }
                                        }
                                        else if (rule == F.In)
                                        {
                                            try { dr[field[n].ToString()] = long.Parse(in_dr[in_field[n].ToString()].ToString()); }
                                            catch (FormatException) { dr[field[n].ToString()] = DBNull.Value; }
                                        }
                                        else if (rule == F.St)
                                        {
                                            dr[field[n].ToString()] = in_dr[in_field[n].ToString()];
                                        }
                                        else if (rule == F.Sdt || rule == F.Sdt_Keep_Latest)
                                        {
                                            in_sdt = DateTime.Parse(in_dr[in_field[n].ToString()].ToString());
                                            dr[field[n].ToString()] = SmallDateTime(in_sdt);
                                        }
                                        else if (rule == F.Cr || rule == F.Up)
                                        {
                                            dr[field[n].ToString()] = SmallDateTime(DateTime.UtcNow.AddHours(3));
                                        }
                                    }
 
                                    dt.Rows.Add(dr);
                                    c++;
                                }
                            }
 
                            scb.GetUpdateCommand();
                            sda.Update(ds, tableName);
                            sc.Close();
 
                            result = "(" + c + "-" + count_delete + "/" + count_in + "/" + count + ")";
 
                            if (c > 0 || count_delete > 0) op = 1;
                        }
                        else
                        {
                            result += "(0-0/0/*)";
                            op = 0;
                        }
                    }
                    else
                    {
                        result += "(0-0/*/null)";
                        op = -1;
                    }
                }
                else
                {
                    result += "(0-0/null/*)";
                    op = -1;
                }
            }
            catch (Exception ex)
            {
#if DEBUG
                result = "Ia.Ngn.Cs.Application.Update(): " + ex.ToString(); // +"  TEMP=[" + temp + "]"; // TEMP
#else
                result = "Ia.Ngn.Cs.Application.Update(): " + ex.Message;
#endif
 
                op = -1;
            }
 
            return op;
        }
 
        ////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////
    }
}
 
/*
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Globalization" %>
<%@ Import Namespace="System.ComponentModel" %>
<script runat="server">
    public void Page_Load(Object sender, EventArgs e) {  
        //QUICK OVERVIEW OF SYNTAX:
        / *
        int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
        int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
        string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
        string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());      
        DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");      
        DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
        DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
        DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
        DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
        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");
        DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
        DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
        * /
        //get an integer
        int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
        litShow1.Text = "There are " + numberOfEmployees.ToString() + " employees";
        
        //get an integer with parameters
        string selectedCountry = "UK";
        int numberOfEmployees2 = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255," + selectedCountry);
        litShow2.Text = "There are " + numberOfEmployees2.ToString() + " employees from: " + selectedCountry;
        
        //get a string
        string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
        litShow3.Text = "The last name of the person selected is <b>" + currentLastName + "</b>. ";
        
        //get a string with parameters
        int selectedId = 4;
        string currentLastName2 = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + selectedId.ToString());
        litShow4.Text = "The last name of the person with id " + selectedId.ToString() + " is <b>" + currentLastName2 + "</b>. ";
        
        //get a date
        CultureInfo ci = new CultureInfo("en-US");        
        DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
        litShow5.Text = "Your order shipped on <b>" + shippedDate.ToString("dddd, MMMM d, yyyy",ci) + "</b>. ";
        //get a date with parameters
        int currentOrderId = 10264;
        CultureInfo ci2 = new CultureInfo("en-US");        
        DateTime shippedDate2 = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
        litShow6.Text = "Your order shipped on <b>" + shippedDate2.ToString("dddd, MMMM d, yyyy",ci2) + "</b>. ";
        //get one record
        DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
        litShow7.Text = "The selected employee is <b>" + drEmployee["FirstName"].ToString() + " " + drEmployee["LastName"].ToString() + "</b>";
        
        //get one record with parameters
        int currentEmployeeId = 8;
        DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
        litShow8.Text = "The employee with id#" + currentEmployeeId.ToString() + " is <b>" + drEmployee2["FirstName"].ToString() + " " + drEmployee2["LastName"].ToString() + "</b>";
        
        //get several records
        DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
        foreach(DataRow row in dtEmployees.Rows) {
            litShow9.Text += row["LastName"].ToString() + ", " + row["FirstName"].ToString() + "<br>";
        }
        
        //get several records with parameters
        DateTime cutOffDate = DateTime.Parse("1/1/1963");
        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");
        foreach(DataRow row in dtEmployees2.Rows) {
            litShow10.Text += row["EmployeeId"].ToString() + ". " + row["LastName"].ToString() + ", " + row["FirstName"].ToString() + " (" + row["Country"].ToString() + ")<br>";
        }
        
        //perform an SQL command
        DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
        
        //perform an SQL command
        int theId = 1;
        DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
        
    }
    
    //EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees");
    public int GetInteger(string sql) {    
        int r = 0;
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand(sql,con);
        r = (int)cmd.ExecuteScalar();
        con.Close();    
        return r;
    }
    //EXAMPLE: int numberOfEmployees = GetInteger("SELECT COUNT(*) FROM Employees WHERE Country=@country","@country,varchar/255,UK");
    public int GetInteger(string sql, string parameterList) {    
        int r = 0;
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand(sql,con);
        GetParameters(ref cmd, parameterList);
        r = (int)cmd.ExecuteScalar();
        con.Close();    
        return r;
    }
    
    //EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=8");
    public string GetString(string sql) {    
        string rs = "";
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand(sql,con);
        rs = (string)cmd.ExecuteScalar();
        con.Close();    
        return rs;
    }
    
    //EXAMPLE: string currentLastName = GetString("SELECT LastName FROM Employees WHERE EmployeeId=@employeeId","@employeedId,int," + selectedId.ToString());
    public string GetString(string sql, string parameterList) {    
        string rs = "";
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand(sql,con);
        GetParameters(ref cmd, parameterList);
        rs = (string)cmd.ExecuteScalar();
        con.Close();    
        return rs;
    }
    
    //EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=10259");
    public DateTime GetDate(string sql) {    
        DateTime rd;
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand(sql,con);
        rd = (DateTime)cmd.ExecuteScalar();
        con.Close();    
        return rd;
    }
    
    
    //EXAMPLE: DateTime shippedDate = GetDate("SELECT ShippedDate FROM Orders WHERE OrderId=@orderId","@orderId,int," + currentOrderId.ToString());
    public DateTime GetDate(string sql, string parameterList) {    
        DateTime rd;
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand(sql,con);
        GetParameters(ref cmd, parameterList);
        rd = (DateTime)cmd.ExecuteScalar();
        con.Close();    
        return rd;
    }
    //EXAMPLE: DataRow drEmployee = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=3");
    public DataRow GetDataRow(string sql) {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        SqlCeDataAdapter da = new SqlCeDataAdapter();
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        SqlCeCommand cmd = new SqlCeCommand(sql, con);
        da.SelectCommand = cmd;
        da.Fill(ds);
        try {
            dt = ds.Tables[0];
            return dt.Rows[0];
        }
        catch {
            return null;
        }
    }
    //EXAMPLE: DataRow drEmployee2 = GetDataRow("SELECT * FROM Employees WHERE EmployeeId=@employeeId","@employeeId,int," + currentEmployeeId.ToString());
    public DataRow GetDataRow(string sql,string parameterList) {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        SqlCeDataAdapter da = new SqlCeDataAdapter();
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        SqlCeCommand cmd = new SqlCeCommand(sql, con);
        GetParameters(ref cmd, parameterList);
        da.SelectCommand = cmd;
        da.Fill(ds);
        try {
            dt = ds.Tables[0];
            return dt.Rows[0];
        }
        catch {
            return null;
        }
    }
    //EXAMPLE: DataTable dtEmployees = GetDataTable("SELECT * FROM Employees ORDER BY LastName");
    public DataTable GetDataTable(string sql) {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        SqlCeDataAdapter da = new SqlCeDataAdapter();
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        SqlCeCommand cmd = new SqlCeCommand(sql, con);
        da.SelectCommand = cmd;
        da.Fill(ds);
        try {
            dt = ds.Tables[0];
            return dt;
        }
        catch {
            return null;
        }
    }
    
    //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");
    public DataTable GetDataTable(string sql, string parameterList) {
        //parameterList will be in this form: "@currentCountry,varchar/255,USA;@cutOffDate,date,1/1/1963"
        
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        SqlCeDataAdapter da = new SqlCeDataAdapter();
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        SqlCeCommand cmd = new SqlCeCommand(sql, con);
        GetParameters(ref cmd, parameterList);
        da.SelectCommand = cmd;
        da.Fill(ds);
        try {
            dt = ds.Tables[0];
            return dt;
        }
        catch {
            return null;
        }
    }
    //EXAMPLE: DoCommand("UPDATE Employees SET LastName='Smithinew' WHERE EmployeeId=9");
    public void DoCommand(string sql) {
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        SqlCeCommand cmd = new SqlCeCommand();
        cmd.CommandText = sql;
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    //EXAMPLE: DoCommand("UPDATE Employees SET LastName='Thompsonnew' WHERE EmployeeId=@id","@id,int," + theId.ToString());
    public void DoCommand(string sql, string parameterList) {
        SqlCeConnection con = new SqlCeConnection(ConfigurationManager.AppSettings["con"]);
        SqlCeCommand cmd = new SqlCeCommand();
        cmd.CommandText = sql;
        GetParameters(ref cmd, parameterList);
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    //used by the other methods
    public void GetParameters(ref SqlCeCommand cmd, string parameterList) {
        //build parameters from the parameter list
        string[] parameterLines = parameterList.Split(';');
        foreach(string parameterLine in parameterLines) { 
            //break up individual line
            string[] parts = parameterLine.Split(',');
            switch(parts[1].ToString().Substring(0,3).ToUpper()) {
                case "VAR":
                    //get the size from e.g. "varchar/255"
                    string[] half = parts[1].ToString().Split('/');
                    int size = Int32.Parse(half[1]);
                    cmd.Parameters.Add(parts[0],SqlDbType.VarChar,size).Value = parts[2];
                    break;
                case "DAT":
                    cmd.Parameters.Add(parts[0],SqlDbType.DateTime).Value = DateTime.Parse(parts[2]);
                    break;
                case "INT":
                    cmd.Parameters.Add(parts[0],SqlDbType.Int).Value = Int32.Parse(parts[2]);
                    break;
            }
        }       
    }
*/