Click here to Skip to main content
Click here to Skip to main content

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

, 30 May 2001
Rate this:
Please Sign up or sign in to vote.
An easy introduction to accessing databases using the ADO classes. It includes how to read and write integers, strings and dates.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

John McTainsh

United States United States
I starting writing code when Apple IIe was all the rage (1982?). Graduated Mechanical Engineering in New Zealand, so I could build Robots. Took up Commerical diving in Asia for a few years to make some quick money (Spent it all). I have been writing C/C++/Assembly and building underwater robots since MS QuickC/Quick Assembler in 1989. I currently live in Australia and work mosly with MS VisualC.

Comments and Discussions

 
QuestionMessage Automatically Removed Pinmemberheemanshubhalla14-Feb-14 1:43 
GeneralGood Job PinmemberRockingDownTheHighway31-Dec-10 3:58 
GeneralReading data from txt file and save it to SQL DB table PinmemberNopo17-Mar-09 4:48 
Questionadvice? Pinmemberguvil15-Jan-09 9:22 
Questionassistance ? Pinmemberguvil15-Jan-09 9:19 
Questionado.net memory release problem PinmemberShashikant_20061-Mar-07 3:24 
AnswerRe: ado.net memory release problem Pinmemberxietiande8-May-07 23:09 
GeneralRe: ado.net memory release problem Pinmemberxwpxly31-Jul-08 5:22 
GeneralWriting to a new Access database using C# PinmemberMember #387144327-Feb-07 13:50 
QuestionADO Namespace ??? Pinmemberdamiller1976@yahoo.com26-Nov-06 11:36 
AnswerRe: ADO Namespace ??? Pinmembereyasso8-Jan-07 21:03 
AnswerRe: ADO Namespace ??? PinmemberJasonShort11-Jun-09 5:27 
Questionquotes Pinmemberasnila1-Aug-06 16:44 
AnswerRe: quotes Pinmemberbrian.hawley22-Mar-07 23:34 
GeneralThat's quite good! Pinmemberduguyitian27-Mar-06 0:21 
QuestionNI LabVIEW PinmemberPooja kamboj20-Mar-06 21:28 
AnswerRe: NI LabVIEW Pinmembernish8514-Aug-07 0:22 
GeneralAn example in Oracle Pinmemberaravindramachandran26-Jan-06 5:38 
GeneralRemote Access Database for MS Access Pinmembertqcuong4-Aug-04 22:08 
GeneralGood one Pinmembernoby_datasoft15-Apr-04 15:45 
GeneralWhy namespace 'ADO' does nto exist Pinmembertzuching2-Apr-04 12:01 
GeneralRe: Why namespace 'ADO' does nto exist PinmemberChad Z. Hower aka Kudzu8-Jun-04 10:21 
Generalglobal connection Pinsussmeitei29-Aug-03 23:49 
GeneralRe: global connection PinmemberSinan Sefai2-Sep-03 1:25 
GeneralRe: global connection PinsussAnonymous19-Aug-05 6:50 
GeneralGreat! PinmemberPhilip Patrick28-Jul-01 0:59 
GeneralChanges for Beta 2 PinmemberJohnz McTainsh24-Jun-01 16:29 
GeneralRe: Changes for Beta 2 Pinmemberhelmutforren16-Aug-03 15:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 31 May 2001
Article Copyright 2001 by John McTainsh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid