Professional Applications Programmers/Consultants برمجة واستشارات تطبيقات الإنترنت
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), Internet 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:  */