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

• Download demo project - 13 Kb
• Download source - 3 Kb

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.