Click here to Skip to main content
15,438,393 members
Articles / Programming Languages / C#
Posted 30 May 2001


125 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.16/5 (38 votes)
30 May 20014 min read
An easy introduction to accessing databases using the ADO classes. It includes how to read and write integers, strings and dates.


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 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.

public const string DB_CONN_STRING =
    "Driver={Microsoft Access Driver (*.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);
  • 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.

    .... 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 );
    // Close the connection
    if( conn.State == DBObjectState.Open )

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!" );
    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(
// 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.


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').


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'";


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.


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 for more updates on simple coding.


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

Written By
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

GeneralGood Job Pin
Nueman31-Dec-10 3:58
MemberNueman31-Dec-10 3:58 
GeneralReading data from txt file and save it to SQL DB table Pin
Nopo17-Mar-09 4:48
MemberNopo17-Mar-09 4:48 
Questionadvice? Pin
guvil15-Jan-09 9:22
Memberguvil15-Jan-09 9:22 
Questionassistance ? Pin
guvil15-Jan-09 9:19
Memberguvil15-Jan-09 9:19 memory release problem Pin
Shashikant_20061-Mar-07 3:24
MemberShashikant_20061-Mar-07 3:24 
AnswerRe: memory release problem Pin
mervyn s8-May-07 23:09
Membermervyn s8-May-07 23:09 
GeneralRe: memory release problem Pin
xwpxly31-Jul-08 5:22
Memberxwpxly31-Jul-08 5:22 
GeneralWriting to a new Access database using C# Pin
Doctor S27-Feb-07 13:50
MemberDoctor S27-Feb-07 13:50 
QuestionADO Namespace ??? Pin
damiller1976@yahoo.com26-Nov-06 11:36
Memberdamiller1976@yahoo.com26-Nov-06 11:36 
AnswerRe: ADO Namespace ??? Pin
eyasso8-Jan-07 21:03
Membereyasso8-Jan-07 21:03 
AnswerRe: ADO Namespace ??? Pin
JasonShort11-Jun-09 5:27
MemberJasonShort11-Jun-09 5:27 
Questionquotes Pin
asnila1-Aug-06 16:44
Memberasnila1-Aug-06 16:44 
AnswerRe: quotes Pin
brian.hawley22-Mar-07 23:34
Memberbrian.hawley22-Mar-07 23:34 
GeneralThat's quite good! Pin
duguyitian27-Mar-06 0:21
Memberduguyitian27-Mar-06 0:21 
QuestionNI LabVIEW Pin
Pooja k20-Mar-06 21:28
MemberPooja k20-Mar-06 21:28 
AnswerRe: NI LabVIEW Pin
nish8514-Aug-07 0:22
Membernish8514-Aug-07 0:22 
GeneralAn example in Oracle Pin
aravindramachandran26-Jan-06 5:38
Memberaravindramachandran26-Jan-06 5:38 
GeneralRemote Access Database for MS Access Pin
tqcuong4-Aug-04 22:08
Membertqcuong4-Aug-04 22:08 
GeneralGood one Pin
noby_datasoft15-Apr-04 15:45
Membernoby_datasoft15-Apr-04 15:45 
GeneralWhy namespace 'ADO' does nto exist Pin
tzuching2-Apr-04 12:01
Membertzuching2-Apr-04 12:01 
GeneralRe: Why namespace 'ADO' does nto exist Pin
Chad Z. Hower aka Kudzu8-Jun-04 10:21
MemberChad Z. Hower aka Kudzu8-Jun-04 10:21 
Generalglobal connection Pin
meitei29-Aug-03 23:49
Membermeitei29-Aug-03 23:49 
GeneralRe: global connection Pin
Sinan Sefai2-Sep-03 1:25
MemberSinan Sefai2-Sep-03 1:25 
GeneralRe: global connection Pin
Anonymous19-Aug-05 6:50
MemberAnonymous19-Aug-05 6:50 
GeneralGreat! Pin
Philip Patrick28-Jul-01 0:59
professionalPhilip Patrick28-Jul-01 0:59 

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

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