Click here to Skip to main content
11,805,266 members (63,258 online)
Click here to Skip to main content

Using ADO.NET programmatically with C#

, 15 Dec 2005 CPOL 125.8K 3.3K 40
Rate this:
Please Sign up or sign in to vote.
An article on using ADO.NET programmatically.

Sample image


In this article, I will explain the basic concepts of MS ADO.NET and how to involve use it programmatically.


It is well known that Visual Studio .NET supports developers with many wizards. One of them is the Data Form Wizard. If you want to create forms involving single and master detail databases, this tool will give you many detailed and interesting opportunities while creating your form. But when you are developing real-world applications, generally, you have to face altering these standard methods to match your target application requirements. Therefore, you need to write some code to make your application flexible. Here is an example of using ADO.NET fully programmatically with C#.

Using the Code

The program uses a simple SQL database named 'Library' and a table named 'Books' in it. We have a few ways to create this database. After receiving a request, I decided to write some code to create the necessary database programmatically. I added a menu item Settings->Create Database to create the database.

public void CreateDatabase()
    string sCreateDatabase="CREATE DATABASE Library";            
        "Title CHAR(50) NOT NULL , Author CHAR(50), " + 
        "PageCount INTEGER,Topic CHAR(30),Code CHAR(15))" ; 
        +"VALUES('Test Book','Test Author', 100, 'Test Topic', 'Test Code');";
    SqlConnection mycon=new SqlConnection();
    mycon.ConnectionString="workstation id=;initial catalog=; integrated security=SSPI";

    SqlCommand mycomm=new SqlCommand();

        //    Open the connection
        //    Execute CreateDatabase query
        //    Catch any errors and show the error message
        MessageBox.Show(" The database already exists. ");
      "workstation id=;initial catalog=Library; integrated security=SSPI";
        //    Open the connection
        //    Execute CreateTable query
        //    Execute InsertFirstRow query
        //    Catch any errors and show the error message
        MessageBox.Show(" There is already a table named 'Books' in the database. ");

We can also do this via MS Visual Studio .NET or MS SQL Server Manager. Let us create a Books table in the Library database. Here are the table columns:

Column Name Data Type Length Allow nulls
BookID int 4
Title char 50
Author char 50 V
PageCount int 4 V
Topic char 30 V
Code char 15 V

First of all, to move data between a data store and our application, we must have a connection to the data store. We will use a Connection object from ADO.NET to create and manage the connection. Because our data source is SQL Server, we use SqlConnection.

SqlConnection mycon=new SqlConnection();

To determine the settings, we must use the ConnectionString property.

mycon.ConnectionString="workstation id=;initial catalog=LIBRARY; integrated security=SSPI";

DataSet is a memory-resident representation of data, and can include table(s), constraint(s), and relationship(s).

DataSet myds=new DataSet();

A DataAdapter is used to retrieve data from a data source and populate within a DataSet and to resolve changes made to the DataSet back to the data source. We can also say simply, interaction with data source(s) and dataset(s) is controlled through the DataAdapter.

SqlDataAdapter myadap=new SqlDataAdapter();

A DataTable is used for representing a single table of memory-resident data. In our project, as we have only one table in our database, we must create a table as a presentation of the table in our database and add it to our DataSet object.

DataTable mytable=new DataTable("books");

A Command object is used to access data directly in the database in a connected environment. CommandType is declared as Text. CommandText is a SQL statement.

mycomm SqlCommand =new SqlCommand();

To be able to declare and use these ADO.NET objects, we must include the System.Data and System.Data.SqlClient namespaces in our application.

using System.Data;
using System.Data.SqlClient;

Indeed we have two options. We can manipulate data through the DataAdapter object using the Fill and Update methods. We use the Fill method for loading and searching data in our application because of its simplicity. As a second option, we can use SqlConnection and SqlCommand to access our data directly. It is faster than the first. This method is used to insert, delete, and modify functions with the ExecuteNonQuery method of the Command object. We can create a class and put all the necessary code and make some necessary items public to reach them from everywhere in our namespace.

public class dataManipulationClass
    public SqlConnection mycon;
    public DataSet myds;
    public DataTable mytable;
    public SqlDataAdapter myadap;
    public SqlCommand mycomm;        

    public bool ManupulateData()
        mycon=new SqlConnection();
        mycon.ConnectionString="workstation id=;initial catalog=LIBRARY;
            integrated security=SSPI";
        myds=new DataSet();
        mytable=new DataTable("books");
        myadap=new SqlDataAdapter();

        mycomm=new SqlCommand();

        return true;

To use this class in the FormMain class, we must declare it.

dataManipulationClass cDataMan=new dataManipulationClass();

To load data, we have a simple LoadData function:

public void LoadData()
    //    Call method to initialize the parameters of dataManipulationClass object
    //    Bound dataGrid to dataset 
    //    Set DataMember to books table
        //    Get data from table and fill the table in the dataset via dataadapter
    catch(Exception xcp)
        //    Catch any errors and show the error message
        //    Close the connection

The DeleteData and ModifyData functions are very similar to AddData except for their connection string. The connection string is for the DeleteData function:

cDataMan.mycomm.CommandText="DELETE FROM BOOKS WHERE BOOKID=@BookID";

A SqlParameter is added to the string for BookID.

cDataMan.mycomm.Parameters.Add(new SqlParameter("@BookID",sBookID));

The connection string is for the ModifyData function:

cDataMan.mycomm.CommandText="SELECT TITLE,AUTHOR,PAGECOUNT,"

A SqlParameter is then added to the string.

cDataMan.mycomm.Parameters.Add(new SqlParameter("@Title",sTitle));


I think this code can be used for generating code and an application without design-time data object replacements and wizard-free application development which generates sometimes unclear code. The code is simple, but of course, should be improved to overcome your real-time problems. I know that there are a lot of things to do with my code. I am planning to improve my code in the future and expect your help.


  • 14 Dec. 2005: Created paramaterized SQL queries. Added database creation feature to create the necessary database for this article. Fixed the Delete and Modify functions to invoke exact selection on the DataGrid.
  • 03 Dec. 2005: Initial revision.



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
Turkey Turkey
Started writing code in 1988. There were GW Basic, Turbo Pascal, Turbo C under DOS.
While studentship there were Assembly, C++, digital electronics, the scene as new things. Then next versions, next generations like Visual Basic, Delphi, C++ Builder, next platforms like Windows, Win32, Linux. Last years many projects took place using various tech and under various platforms. New technologies and IDEs like Delphi 2005, Visual Studio 2003, 2005, C#, ASP.NET, ADO.NET, Metodologies and Tools like UML, Starteam..

You may also be interested in...

Comments and Discussions

Questionmain function Pin
codingisok10120-Aug-12 20:08
membercodingisok10120-Aug-12 20:08 
QuestionHOW TO: SQL & C# Pin
Shahriar Nour Khondokar2-Jun-10 2:48
memberShahriar Nour Khondokar2-Jun-10 2:48 
JokeThanks so much! Pin
An Huy Hoang3-Sep-09 6:39
memberAn Huy Hoang3-Sep-09 6:39 
Generaldispose Pin
Billy Jean again20-Dec-05 7:10
memberBilly Jean again20-Dec-05 7:10 
GeneralRe: dispose Pin
L-NRG26-Dec-05 22:09
memberL-NRG26-Dec-05 22:09 
Generalinteresting code Pin
Jong G Andr16-Dec-05 23:14
memberJong G Andr16-Dec-05 23:14 
GeneralRe: interesting code Pin
L-NRG20-Dec-05 3:23
memberL-NRG20-Dec-05 3:23 
GeneralNo source code Pin
Peter Kohout16-Dec-05 0:18
memberPeter Kohout16-Dec-05 0:18 
Many thanks for a wonderful article. As a newcomer to C# I need all the help that I can get.

I would really be most grateful for a copy of the source code to 'play with'.

Thanking in advance

Peter K.
Generalsource code Pin
L-NRG16-Dec-05 8:22
memberL-NRG16-Dec-05 8:22 
GeneralSource code and image Pin
AxelM15-Dec-05 20:53
memberAxelM15-Dec-05 20:53 
GeneralRe: Source code and image Pin
L-NRG16-Dec-05 8:38
memberL-NRG16-Dec-05 8:38 
GeneralRe: Source code and image Pin
AxelM16-Dec-05 9:08
memberAxelM16-Dec-05 9:08 
GeneralRe: Source code and image Pin
L-NRG16-Dec-05 22:25
memberL-NRG16-Dec-05 22:25 
Generalsome useful info Pin
befast14-Dec-05 19:57
memberbefast14-Dec-05 19:57 
Generalteaches bad practices Pin
Rob Graham3-Dec-05 9:40
memberRob Graham3-Dec-05 9:40 
GeneralRe: teaches bad practices Pin
computerguru923823-Dec-05 13:19
membercomputerguru923823-Dec-05 13:19 
GeneralRe: teaches bad practices Pin
L-NRG6-Dec-05 19:55
memberL-NRG6-Dec-05 19:55 
GeneralRe: teaches bad practices Pin
computerguru923827-Dec-05 5:10
membercomputerguru923827-Dec-05 5:10 
GeneralRe: teaches bad practices Pin
Mike Elliott19-Dec-05 8:43
memberMike Elliott19-Dec-05 8:43 

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 | Terms of Use | Mobile
Web03 | 2.8.151002.1 | Last Updated 15 Dec 2005
Article Copyright 2005 by Umut ŞİMŞEK
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid