Create, Read, Update, Delete - ADO.NET sample

This chapter demonstrates the basic database operations using the ADO.NET classes. The sample code in this chapter uses the OleDb Provider.

Here is some sample code to execute a simple query.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );
myConnection.Open();

string query = "insert into EMPLOYEE_TABLE (EmployeeID, Name, Address) VALUES (101, 'John', '3960 CliffValley Way')";

OleDbCommand myCommand = new OleDbCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();

myConnection.Close();

Let us analyze the code. First we have declared a connection string. The connection string points to an MS Access database. Before you execute this code, make sure you have the database in the path specified. Or, change the path accordingly.

In the next step, we are creating a OleDbConnectionobject and passing the connection string to this object. The line 'myConnection.Open();' will open a connection to the MS Access database specified in the connection string. If the database doesn't exists or if it is not able to open a connection for some other reason, the '.Open' call will fail.

Next step is, creating a OleDbCommand object. This command object is used to execute sql statements and uses the connection opened by the OleDbConnection object.

Note that before executing a command, we have to establish a valid connection to the database.

And finally, after we have executed with the command, we will close the connection.

The above sample code executes a sql statement and returns no data from database. We are calling the method 'ExecuteNonQuery()' on the command object. If we have a 'select ...' statement which returns data from database, we cannot use the 'ExecuteNonQuery()' method.

The following sample demonstrates using OleDbDataAdapterObject and DataSet to retrieve data from databbase. 

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill ( employeeData );

Here we are creating a OleDbConnection object and we are just passing the object to the OleDbDataAdapterobject. Also, we pass the 'select ...' query to the OleDbDataAdapter. Next, we call the '.Fill()' method of the OleDbDataAdapter. This step will populate the dataset ( called 'employeeData' ) with the data retrieved for the sql statement 'select * from EMPLOYEE'.

As you already know, a DataSet can contain a collection of tables. But in our case, our sql statement will retrieve data from only one table. So, our DataSet will have only one table.

We can iterate through the table in the dataset and retrieve all the records. See the following code demonstrating this:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

// Repeat for each table in the DataSet collection.
foreach ( DataTable table in employeeData.Tables )
{
// Repeat for each row in the table.
foreach ( DataRow row in table.Rows )
{
MessageBox.Show( "Employee Number : " + row["EmployeeNumber"].ToString() );
MessageBox.Show( "Name : " + row["Name"].ToString() );
MessageBox.Show( "Address : " + row["Address"].ToString() );
}
}

The above code retrieves all the records from the employee table and displays all the fields. You can download a sample project demonstrating the basic database operations.