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



Comments

  • Alle første kvalitet ved Dr. Dre-hodetelefoner

    Posted by mantouhmmm on 06/04/2013 05:26pm

    [url=http://www.beatshodetelefoner.webstarts.com/]beats by dre studio[/url] Bestem deg for hva din mor ville elske den største og å pynte gaven kurv pakk den opp pent med noen gave papirer og sette på noen blomster og bånd på den. Sett også et bilde av deg og din mor innrammet i den. Hun er bare overskriften å virkelig liker det. For å toppe det hele, også inneholde beats by Dr Dre solo hd personlig kort, og det er på vei til å være utmerket gave til en ideell billige beats av Dre på Mors day.AKG-produsent av high-end hodetelefoner, mikrofoner og høyttalere i over 60 år, kunngjorde i dag tilgjengeligheten av sin K551 håndsett. De lukkede tilbake bokser har en massiv drivere og realistisk lyd Passiv støyisolering for pue nedsenking. [url=http://www.drebeatsnorge.qsite.dk/]beats by dre norge[/url] Den moderne kreasjoner forbundet med Bests ved hjelp av Dre Expedition montører inkludere ting som varer beregnet for alle fem. en flerkanals lydopptak sammen med high-tech hodetelefoner, jorden one of a kind wi-fi konferanser prosessen Revoluto, high-end hodetelefoner som har en digital forstyrrelse kansellering, på sine egne konstruerte hodetelefoner beregnet for mp3-elskere i tillegg til avanserte hodetelefoner som har fersk Tesla teknologisk know-how. Analytisk støy forbundet med Bests ved hjelp av TourBeats ved hjelp av Dre Expedition Praktisk Ferdig Tilbake Veiledning hodetelefoner, beregnet for DJs støy montører i tillegg til artister, sletter denne Bests ved hjelp av dre på plass helt ny størrelse med en fantastisk støy utmerket i tillegg til bemerkelsesverdig produkter. [url=http://www.beatsstudionorge.weebly.com/]beats studio[/url] Once du har lastet ned flere beats, og du er fornøyd med beats som du har lastet ned. Da er det tid kompilere og polere opp på beats når beats er godt arrangert, og lyden er god, og du har spilt den om og om igjen, og du er fornøyd med det endelige produktet så er du god til å go.Once dine beats er klar så er det på tide å dele dine sluttprodukter med venner og familie. Det er også anledning til å lansere deg som en rap artist gjennom å tilby gratis rap beats og selge rap musikk på nettet. Dette gjelder også andre sjangere av musikk som du oppretter gjennom gratis online beats nedlasting.

    Reply
  • Te das cuenta theMonster auriculares que tiene sin duda una protección considerable

    Posted by cheneason on 06/04/2013 01:52pm

    [url=http://www.beats-by-dr-dre-2013.webstarts.com/]auriculares beats[/url] Hvis man ville præcisere, hvad præcist udrette audio transskription tjenesteudbydere samt transskribere tjenester udføre, svaret er, som de fuldføre opgaven med at konvertere musik information i elektronisk fil info. Hvis det overhovedet er muligt, kan en uddannet transcriptionist lytte til lyd tape løsninger, der har brug for at konvertere, og derefter dokumentere dem i en elektronisk digital fil, der kan helt sikkert være en sætning fil eller tilknyttede fil formatering. [url=http://www.comprarbeatsbaratos.weebly.com/]Comprar beats baratos[/url] Butland afsløret i en besked til en anden GB fodboldspiller, Karen Carney, at en Beats repræsentant besøgte holdet hotel på mandag, twitte: “. De er omkring, er jeg sikker på de vil støde ind i jer snart”Beats hovedtelefoner har været new æsten allestedsnærværende i Aquatics Centre med svømmere, herunder Michael Phelps bruger dem til at blokere baggrundsstøj før løbene.Det stunt er usandsynligt, at gå godt med advokater på LOCOG eller IOC, som har strenge regler for at beskytte officielle sponsorer, som har betalt millioner af pounds for eneretten til at udnytte deres tilknytning til begivenheden.Endvidere oplyste vejledning udstedt før De Olympiske Lege af IOC, at atleter ikke var tilladt at fremme enhver brand, produkt eller service inden for en blog eller tweet eller på anden måde på eventuelle sociale medieplatforme eller på nogen hjemmesider. [url=http://www.beatsbydre777.weebly.com/]auriculares beats[/url] Denne nye forretninger med salget af den seneste teknologiske udvikling og kvalitetsudvikling i beats af forskellige mærker er en ganske næring til en. Kompatibilitet er også en grund, der gør folk kører efter de nye versioner på markedet. Beats by Dre er en af de mest populære mærker, når det kommer til hovedtelefoner af højeste kvalitet. News viser, at de har et partnerskab med Apple om at levere et eksklusivt sæt hovedtelefoner der er helt mindre i antal, men ganske højt i prisklasse. Monster beats er også en anden vigtig mærke i serien.

    Reply
  • c# Database

    Posted by arith_s on 01/17/2010 03:45am

    Try EffiProz Database http://www.EffiProz.com, EffiProz is a database written entirely in C#. comprehensive SQL support including Stored Procedures, Triggers and Functions. Ideal for embed in .Net applications. Support Silverlight 3 and .Net compact framework as well Include Visual Studio ad-in, ADO.Net provider, Entity Framework provider, etc.

    Reply
  • System.Data.ADO not found!....?

    Posted by gilly914 on 02/02/2008 10:01am

    How come my Visual Studio cannot find the namespace System.Data.ADO??? I added the System.Data reference, but it didn't solve the problem...

    • System.Data.ADO does not exist

      Posted by dcrooks on 10/18/2009 11:28am

      The System.Data.ADO was removed from the beta. Need to look at these: System.Data System.Data.SqlClient System.Data.OleDb System.Data.Odbc

      Reply
    • stupid, but still doesn't work...

      Posted by gilly914 on 02/02/2008 04:17pm

      I checked the comments, and found out my silly mistake, but now the code still won't run! I seem to have problems with the ConnectionString parameter... I used what is given in the article but with my file : DB_CONN_STRING = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\\temp\\db1.mdb"; ..and i can't find what seems to cause the problem. Can someone help me???

      Reply
    Reply
  • Insert statement

    Posted by sarathshiva_19 on 02/02/2006 08:02am

    Hi, I get an exception box saying "syntax error in insert into statement" when I try to execute the following code, the exception is thrown when I call the executenonquery method in the try block. Any input on this is highly appreciated. Thanks and regards, --sarath. sarathshiva_19@rediffmail.com OleDbConnection conn = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=..\\..\\ePrescription.mdb"); OleDbCommand myOleDbCommand = new OleDbCommand("INSERT INTO UserTable (UserName, PassWord, Address, CreationDate, Name, Salutation, PhoneNumber, Email) VALUES ( ' s ' , ' s ' , ' Cunningham Road ' , ' 18:08:43-02/00/06 ' , ' name ' , ' Mr ' , 'sdfgsfdg ' , ' s@s.com ' )", conn); conn.Open(); try { myOleDbCommand.ExecuteNonQuery(); } catch (System.Exception err) { MessageBox.Show ("" + err.ToString()); } finally { // Close the connection if it is open if (conn.State==ConnectionState.Open) { conn.Close(); } }

    Reply
  • How can I use variables in the INSERT command?

    Posted by Legacy on 02/25/2004 12:00am

    Originally posted by: Kevin Cahn

    I've created similar code in JAVA2. I can insert hardcoded data in my database without any problems. However, in a real world application you will want to be able to insert data from variables into the database. After all, if you were only going to insert hardcoded data, then you could just go into the database and type the data in. How can I insert variable data into a database using SQL commands?

    Thanks,
    Kevin

    • Variables in INSERT command

      Posted by marcnz on 01/08/2005 03:10am

      Here is an example: private void fnInsertDate(string pCat) { ... OleDbCommand dCmd = new OleDbCommand("INSERT INTO category(catname)VALUES ('" + pCat + "')", dCon); ... } Hope this help.

      Reply
    • How can I use variables in the INSERT command?

      Posted by wdicks on 11/10/2004 06:18am

      This has been my question too! Does anyone know the answer to this?

      Reply
    Reply
  • Synchronised updates

    Posted by Legacy on 10/21/2003 12:00am

    Originally posted by: zee

    What happens when two people access the application at once and one updates another reads. There is a possibility of reading incorrect data. In java I would synchronise, what do you do in C#?

    Reply
  • database

    Posted by Legacy on 06/20/2003 12:00am

    Originally posted by: A.RAMA KRISHNA

    i want how insert and update and delete
    
    a record in vb.net using oracle database

    Reply
  • Gr8

    Posted by Legacy on 06/06/2003 12:00am

    Originally posted by: jehanzeb khan

    hi,
    well i read this and it really helped my problem. But to be very frank C# sucks. Java is the best.

    Reply
  • nothing

    Posted by Legacy on 01/18/2002 12:00am

    Originally posted by: cy

    What a cool code.
    
    Add more.

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Companies must routinely transfer files and share data to run their business, work with partners, and speed operations. However, many find the traditional approach to file transfer lacks necessary security, is too complex and difficult to manage, does not support the levels of automation needed, and breaks down when addressing the file transfer requirements of new areas like Big Data analytics and mobile applications. This QuinStreet SmartSelect discusses how the changing business environment is making the use …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds