DB_Interface_DLL_Class

    1| /*
    2| NTC C# II Programming 152-124
    3| Instructor: John Heckendorf
    4| Student: Keith Gallistel
    5| Lab ID: <35176>
    6| 
    7| Project Name: Database Interface DLL
    8| 
    9| 

  10| Maintenance Log:
  11| By:Date:Change:
  12| Keith Gallistel10/13/08-started project
  13|  -added code to connect to database
  14|  -added code to open database
  15|  -added code to close database
  16|  -added code to add record to database
  17|  -added code to create temporary fields for data
  18|  to add to database
  19|  
  20| Keith Gallistel10/20/08-confirmed that it worked
  21|  -added ReQuery method
  22| */

  23| 
  24| 
  25| using System;
  26| using System.Collections.Generic;
  27| using System.Linq;
  28| using System.Text;
  29| 
  30| //Add references
  31| using System.Data.OleDb;
  32| using System.Data;
  33| using System.Windows.Forms;
  34| 
  35| namespace DB_Interface_DLL
  36| {
  37|     public class DB_Interface_DLL_Class
  38|     {
  39|         public DB_Interface_DLL_Class()
  40|         {
  41| 
  42|         }
  43| 
  44|         public static bool bDBOpenSW = false;
  45|         public static int iCurrRow = 0;
  46| 
  47|         //Create the database component's objects
  48|         public static System.Data.OleDb.OleDbConnection MyDBConnection;
  49|         public static System.Data.OleDb.OleDbDataAdapter MyDBDataAdapter;
  50|         public static System.Data.OleDb.OleDbCommandBuilder MyDBCommandBuilder;
  51|         public static System.Data.DataSet MyDBDataSet;
  52| 
  53|         public static string sMyDBsqlStr = "";
  54|         public static string sConnect = "";
  55|         public static string sTableName = "tblUser_Information";
  56| 
  57|         //Open datbase
  58|         public void OpenDB(string sDBNameIn)
  59|         {
  60|             sConnect = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
  61|               "Data Source =" + sDBNameIn);
  62| 
  63|             try
  64|             {
  65|                 MyDBConnection = new OleDbConnection(sConnect);
  66|                 MyDBConnection.Open();
  67| 
  68|                 MyDBDataSet = new DataSet();
  69| 
  70|                 sMyDBsqlStr = "SELECT * FROM tblUser_Information ORDER BY fldKey";
  71| 
  72|                 MyDBDataAdapter = new OleDbDataAdapter(sMyDBsqlStr, MyDBConnection);
  73|                 MyDBDataAdapter.SelectCommand.CommandText = sMyDBsqlStr;
  74| 
  75|                 MyDBCommandBuilder = new OleDbCommandBuilder(MyDBDataAdapter);
  76| 
  77|                 //allow find command
  78|                 MyDBDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  79| 
  80|                 MyDBDataAdapter.Fill(MyDBDataSet, sTableName);
  81| 
  82|                 bDBOpenSW = true;
  83|             }
  84|             catch(System.Data.OleDb.OleDbException)
  85|             {
  86|                 MessageBox.Show("Unable to Open Database.\n Application will Close.", "Critical Error",
  87|                   MessageBoxButtons.OK,
  88|                   MessageBoxIcon.Error);
  89|             }
  90|         }
  91| 
  92|         //Close Database
  93|         public void CloseDB()
  94|         {
  95|             if (bDBOpenSW)
  96|             {
  97|                 MyDBConnection.Close();
  98|             }
  99|             bDBOpenSW = false;
100|         }
101| 
102|         //Add a record
103|         public bool AddRecord()
104|         {
105|             DataRow dNewRow;
106| 
107|             try
108|             {
109|                 dNewRow = MyDBDataSet.Tables[sTableName].NewRow();
110|                 dNewRow["fldUserID"] = Table_Fields_Class.sUserID;
111|                 dNewRow["fldUserDate"] = Table_Fields_Class.sUserDate;
112|                 dNewRow["fldUserTime"] = Table_Fields_Class.sUserTime;
113|                 dNewRow["fldFormattedDate"] = Table_Fields_Class.sFormattedDate;
114|                 dNewRow["fldMilitaryTime"] = Table_Fields_Class.sMilitaryTime;
115|             }
116|             catch(Exception e)
117|             {
118|                 MessageBox.Show(e.Message +
119|                   "\nField error - this class record could not be added.",
120|                   "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
121|                 return false;
122|             }
123| 
124|             //These lines add new record to the database
125|             try
126|             {
127|                 MyDBDataSet.Tables[sTableName].Rows.Add(dNewRow);
128|                 MyDBDataAdapter.Update(MyDBDataSet, sTableName);
129|             }
130|             catch (Exception e)
131|             {
132|                 MessageBox.Show(e.Message +
133|                   "\nDatabase error - this class record could not be added.",
134|                   "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
135|                 return false;
136|             }
137| 
138|             MessageBox.Show("Record has been added.");
139|             iCurrRow = 0;
140| 
141|             return true;
142| 
143|         }
144| 
145|         public void ReQuery(string sQuery)
146|         {
147|             MyDBDataAdapter.SelectCommand.CommandText = sQuery;
148|             MyDBDataSet.Clear();
149| 
150|             try
151|             {
152|                 MyDBDataAdapter.Fill(MyDBDataSet, sTableName);
153|             }
154|             catch (Exception e)
155|             {
156|                 MessageBox.Show("Query Error - " + e);
157|             }
158|         }
159| 
160|     }//end of Class
161| 
162| }