Simple ADO Database Read, Insert, Update and Delete using C#.

Environment: VS.NET (beta 1), NT 4.0, Win95/98/2000

Introduction

Accessing databases is a common part of most applications and with the introduction
of C# and ADO.NET, has become quite simple. This article will demonstrate the
four most basic database operations. 

  • Reading data. This includes various data types such as integers, strings and
    dates.
  • Writing data. As with reading we will write these common types. This will
    be done using a SQL statement.
  • Updating or modifying data. Again we will use a simple SQL statement.
  • Deleting data. Using SQL.

These operations will be performed against a Microsoft Access 2000 database, however
SQL or other ADO data sources could be used by simply changing the connection
string. 

Getting started

To use the ADO classes we need to include the ADO.NET namespace and a few
handy date classes. Add the following line of code to the file where you want to
perform the database operation. It should appear below the namespace line and
above the class definition.

  using System.Data;             // State variables
  using System.Data.ADO;         // Database
  using System.Globalization;    // Date

Depending on the type of project you are working with, you may need to add a
reference to the System.Data namespace. You will know this if the compiler
errors on the code you just added. To add the System.Data namespace;

  • Right click on the Solution explorer – References branch.
  • Select Add reference.
  • Select the .NET Framework tab.
  • Double click on the System.data.dll entry.
  • Select OK.
  • System.Data should now appear in the References list of the Solution
    explorer.

The connection string is used during most operations, so I would recommend you
make it a member of the class you will be working in. Note: In your application
the path to the database file would be something else.

//Attributes
 public const string DB_CONN_STRING =
   "Driver={Microsoft Access Driver (*.mdb)}; "+
   "DBQ=D:\CS\TestDbReadWrite\SimpleTest.mdb";

Reading data

Now things get interesting. Reading is done using the ADODataReader
class. (See Chris Maunder’s article The ADO.NET ADODataReader
class
for more info on this class. ) The steps to perform the read are;

  • We open the database with an ADOConnection.
        ADOConnection conn =
                   new ADOConnection(DB_CONN_STRING);
        conn.Open();  
  • We create a SQL statement to define the data to be retrieved. This command
    is executed to return an ADODataReader object. Note the out keyword in the
    Execute method. This is C# talk for pass by reference.

        ADODataReader dr;
        ADOCommand cmd =
           new ADOCommand( "SELECT * FROM Person", conn );
        cmd.Execute( out dr);  	
  • We loop through each record in the ADODataReader until we are done. Note:
    The data is returned directly as a string and the field name is used to
    indicate the field to read.

        while( dr.Read() )
        {
            System.Console.WriteLine( dr["FirstName"] );
        }  
  • We clean up.  

However, as good programmers we would have also wrapped the lot in a
try/catch/finally to ensure we handled anything bad.

 try
 {
     .... the database operations ...
 }
 catch( Exception ex )
 {
   System.Console.WriteLine( "READING:" );
   System.Console.WriteLine( "  ERROR:" + ex.Message );
   System.Console.WriteLine( "  SQL  :" + sSqlCmd );
   System.Console.WriteLine( "  Conn.:" + DB_CONN_STRING );
 }
 finally
 {
   // Close the connection
   if( conn.State == DBObjectState.Open )
     conn.Close();
 }

Reading different data types

The dr["stuff"] is usually able to return a string of
some sort. However to get an int or DateTime object it
is often necessary to cast the data. This is usually done with a simple case or
using one of ADODataReader’s many build in conversions. ie

 int nOrdinalAge = dr.GetOrdinal( "Age" );
 int nAge = dr.GetInt32( nOrdinalAge );

 DateTime tUpdated = (DateTime)dr["Updated"];

Note the use of GetOrdinal to locate the field to read by name. If the field is blank (not been populated yet), the above code will throw an exception.
To catch this condition we check if data exists with the IsNull method as
follows.

 int nOrdinalAge = dr.GetOrdinal( "Age" );
 if( dr.IsNull( nOrdinalAge ) )
 {
   System.Console.WriteLine( " Age  : Not given!" );
 }
 else
 {
   int nAge = dr.GetInt32( nOrdinalAge );
   System.Console.WriteLine( " Age  : " + nAge );
 }

Insert, Modify, Delete and other SQL commands

Inserting, Modifying and Deleting can very simply be done using SQL statements.
The following code performs a SQL command to insert a record.

 // SQL command
 String sSQLCommand =
 "INSERT INTO Person (Age, FirstName, Description, Updated)  " +
 "VALUES( 55, 'Bob', 'Is a Penguin', '2001/12/25 20:30:15' );";
 // Create the command object
 ADOCommand cmdAdder = new ADOCommand(
     sSQLCommand,
     DB_CONN_STRING);
 cmdAdder.ActiveConnection.Open();
 // Execute the SQL command
 int nNoAdded = cmdAdder.ExecuteNonQuery();
 System.Console.WriteLine(
 "nRow(s) Added = " + nNoAdded + "n" );

Note: The try/catch was not shown in the above example but should wrap the
above code.

Inserting

The above code inserted a record by building a SQL command which was later
executed. Some things to note in the formatting of the command are;

  • Numerical values are presented directly. No single quotes (‘).
  • Strings are presented wrapped in single quotes (‘blah’).
  • Be sure the strings do not include any embedded single or double quotes.
    This will upset things.
  • Date and times are presented wrapped in single quotes in international format (‘YYYYY/MM/DD
    HH:MM:SS’).

Modifying

The UPDATE command indicates the records to be modified and the
modification to be made. The return value of the ExecuteNonQuery() indicates the
number of records changes so this would return 5 if there were 5 Peter’s in the
table. 

 String sSQLCommand =
   "UPDATE Person SET Age = 27 WHERE FirstName = 'Peter'";

Deleting

The DELETE command indicates the records to be deleted. This could be several
several records. The return value of the ExecuteNonQuery() indicates the number
of records changes so this would return 2 if there were 2 Bobo in the
table. Both Bobo’s would be deleted.

 String sSQLCommand =
   "DELETE FROM Person WHERE FirstName = 'Bobo'";

About the sample code

The sample is a simple console application that perform each of the database
operations on a provided Microsoft Access database. To build it, open the
TestDbReadWrite.csproj file as a project in the Visual Studio.NET IDE. Change
the DB_CONN_STRING variable in MainConsole.cs to point to the SimpleTest.mdb.
Build it and away you go.

Conclusion

Now you should be able to perform the basic database operation in C#, get out
there and cut some code. Take the time to learn SQL. Also read articles on the why and how this works. If
you get really bored check out my site at www.mctainsh.com
for more updates on simple coding.

Downloads

More by Author

Must Read