Using ADO.NET programmatically with C#

• Download source code - 119.3 Kb

Introduction

In my article, I will explain basic concepts of MS ADO.NET and how to involve them programmatically.

Background

It is well known that Visual Studio .NET supports developers with many wizards. One of them is Data Form Wizard. If you want to create forms involving single and master detail data bases, 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 making your application flexible. Here is an example on 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";			
	string sCreateTable="CREATE TABLE Books (BookID INTEGER PRIMARY KEY IDENTITY,"+
		"Title CHAR(50) NOT NULL , Author CHAR(50), PageCount INTEGER,Topic CHAR(30),Code CHAR(15))" ; 
	string sInsertFirstRow="INSERT INTO BOOKS (TITLE,AUTHOR,PAGECOUNT,TOPIC,CODE)" 
		+"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();
	mycomm.CommandType=CommandType.Text;
	mycomm.CommandText=sCreateDatabase;
	mycomm.Connection=mycon;

	try
	{
		//	Open the connection

		mycon.Open();
		//	Execute CreateDatabase query

		mycomm.ExecuteNonQuery();
	}
	catch
	{
		//	Catch any errors and show the error message

		MessageBox.Show(" The database already exists. ");
	}	
	finally
	{
		mycon.Close();
	}
		
	mycon.ConnectionString="workstation id=;initial catalog=Library; integrated security=SSPI";
	try
	{
		//	Open the connection

		mycon.Open();
		//	Execute CreateTable query

		mycomm.CommandText=sCreateTable;
		mycomm.ExecuteNonQuery();
		//	Execute InsertFirstRow query

		mycomm.CommandText=sInsertFirstRow;
		mycomm.ExecuteNonQuery();
	}
	catch
	{
		//	Catch any errors and show the error message

		MessageBox.Show(" There is already a table named 'Books' in the database. ");
	}
	finally
	{
		mycon.Close();
	}
}	

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

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 Connection object of 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");

myds.Tables.Add(mytable);

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();
mycomm.CommandType=CommandType.Text;
mycomm.CommandText="SELECT TITLE,AUTHOR,PAGECOUNT,TOPIC,CODE FROM BOOKS";

To be able to declare and use these ADO.NET objects we must include 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 DataAdapter object using Fill and Update methods. We used Fill method 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 necessary code and make some necessery items public in obtain to reach 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");
		myds.Tables.Add(mytable);
		myadap=new SqlDataAdapter();

		mycomm=new SqlCommand();
		mycomm.CommandType=CommandType.Text;
		mycomm.CommandText="SELECT TITLE,AUTHOR,PAGECOUNT,TOPIC,CODE FROM BOOKS";
		mycomm.Connection=mycon;
		myadap.SelectCommand=mycomm;

		return true;
	}
}

To use class in 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

	cDataMan.ManupulateData();
	//	Bound dataGrid to dataset 

	dataGrid.DataSource=cDataMan.myds;
	//	Set DataMember to books table

	dataGrid.DataMember="books";		
	try
	{
		//	Get data from table and fill the table in the dataset via dataadapter

		cDataMan.myadap.Fill(cDataMan.mytable);
	}
	catch(Exception xcp)
	{
		//	Catch any errors and show the error message

		MessageBox.Show(xcp.ToString());
	}
	finally
	{
		//	Close the connection

		cDataMan.mycon.Close();
	}
}

DeleteData and ModifyData functions are very similar to AddData except their connection string.
Connection string is for 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));

Connection string is for ModifyData function:

cDataMan.mycomm.CommandText="SELECT TITLE,AUTHOR,PAGECOUNT,"
	+"TOPIC,CODE FROM BOOKS WHERE TITLE=@Title";

A SqlParameter is added to the string.

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

Conclusion

I think, the code can be used for generating codes and an application without design-time data objects 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.

History

� 14 Dec 2005 Created paramaterized sql queries. Added database creation feature to create the necessary database for this article. Fixed delete and modify functions to invoke exact selection on the datagrid.

� 03 Dec 2005 Initial revision.

Sources

� http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconpopulatingdatasetfromdataadapter.asp

� 2555A Developing Microsoft .NET Applications for Windows