Introduction
Accessing database is pretty simple job using C# and ADO. In this article, the sample gives you the guidelines for a C# programmer which demonstrates how to use ADO.NET objects including ADOCOnnection
, ADOCommand
, ADODataReader
. Microsoft Data Access Technology got changed with the ADO.NET I access database Employee.mdb, which has a table called "Employee". I will add the employee information to the database .
Using the Code
To write a code in ADO you need to use the following at the beginning of your code.
using System;
using System.Drawing;
using System.ComponentModel;
using System.WinForms;
using System.Data.ADO ;
using System.Data ;
using System.Threading ;
Proceeding with the next step, make sure that all of your GUI is in place for adding the data ( all the text boxes and labels , that is pretty simple so I am not going to cover this), Next will will go ahead and get the connection to the database( note the connection to the database should be running on a different thread, so that it will not mess up the GUI. to do this we have to start a thread as shown below.
ThreadStart DBThread = new ThreadStart(ConnectToDatabase) ;
Thread DB = new Thread(DBThread) ;
DB.Start() ;
As you can see above we have successfully started the thread, This thread calls the method ConnectToDataBase
, let see what this ConnectToDatabase
method is all about.
public void ConnectToDatabase()
{
Infobar.Text="Please Wait, Connecting to database...." ;
string conn=
"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=Employee.mdb" ;
ADOConnection dbConn = new ADOConnection(conn) ;
string empStr = "Select id from Employee" ;
ADOCommand sqlCommand =new ADOCommand(empStr,dbConn);
dbConn.Open();
ADODataReader reader;
sqlCommand.Execute(out reader) ;
int numRecords=0 ;
while(reader.Read())
{
numRecords++ ;
}
numRecords++ ;
employeeID.Text = numRecords.ToString() ;
Infobar.Text="Connected - Now you can Add records";
}
Hope the above code is not much confusing, It is lot simpler than writing the code in VC++ ADO. now it's time to write a method that actually takes all the parameters from the GUI Form and writes it to the database with the click of a button. for this we will create a button called
Add In your
InitializeComponents
Method the code would look like this
Add.Text = "AddToDatabase";
Add.Click += new System.EventHandler(WriteToDataBase);
This gets as simple as it looks, Actually writing to database is much simpler as shown below in the code.
protected void WriteToDatabase(object sender, System.EventArgs e)
{
if(employeeID.Text!=""&&employeeLastName.Text!=""
&&employeeFirstName.Text!=""&&employeeAge.Text!=""
&&employeeSSN.Text!="")
{
string conn="Provider=Microsoft.Jet.OLEDB.4.0 ;"+
"Data Source=Employee.mdb" ;
ADOConnection dbConn = new ADOConnection(conn) ;
dbConn.Open();
string strInsert = "INSERT INTO Employee (employeeID, " +
"employeeLastName, employeeFirstName, "+
"employeeAge, employeeSSN) VALUES ( "
+employeeID.Text+", '"
+employeeLastName.Text+"' , '"
+employeeFirstName.Text+"' , "
+employeeAge.Text+", "
+employeeSSN.Text+")";
ADOCommand adoInsert = new ADOCommand(strInsert,DBConn) ;
adoInsert.ExecuteNonQuery() ;
Infobar.Text="Data is being Added to Database " ;
int iClear=int.Parse(employeeID.Text);
iClear++;
employeeID.Text=i.ToString() ;
employeeLastName.Text="" ;
employeeFirstName.Text="" ;
employeeAge.Text="" ;
employeeSSN.Text="" ;
Infobar.Text="Connected - Now you can Add more records...";
DBConn.Close() ;
}
else
{
MessageBox.Show("All fields must be filled.", "Error",
MessageBox.IconExclamation);
}
}
Hope this was helpful. Will write more on how to view the database values and how to delete those values in my coming articles.