Page 1 of 1

OleDb Data Objects in C#

#1 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Post icon  Posted 25 September 2007 - 05:09 AM

In todays tutorial we will be discussing the basics of interacting with MS Access (or any other database that requires the OleDb Objects) in C#. These code ideas will be from a Windows Application, but with some (if any) modifications they can be used in a C# Web Application. There are also in .Net 2.0.

If your project doesn't already have one add an app.config file, in it you will be storing your connection string information for later retrieval. Your connection should look like this

< connectionStrings>
	<add name="YourConnectionName"
   connectionString="Persist Security Info=False;
   Data Source=YourDatabase;
   Initial Catalog=YourTable;
   Integrated Security=SSPI;
   Trusted_Connection=TRUE;
   Application Name=SampleCSharpApplication"
   providerName="System.Data.SqlClient" >
  < connectionStrings>



We will be referencing this connectionString in our next code snippet. Add new Class and name it DataAccess, then at the top of you class add this using statement

using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;



This is needed for working with OleDb Objects in the .Net Framework. The first code you will add to this class is a method to retrieve the connectionString from the app.config file. The method is this

public static string GetConnectionString(string strConnection)
{
   //variable to hold our connection string for returning it
   string strReturn = new string("");
   //check to see if the user provided a connection string name
   //this is for if your application has more than one connection string
   if (!string.IsNullOrEmpty(strConnection)) //a connection string name was provided
   {
	  //get the connection string by the name provided
	  strReturn = ConfigurationManager.ConnectionStrings(strConnection).ConnectionString;
   }
   else //no connection string name was provided
	   {
		  //get the default connection string
		  strReturn = ConfigurationManager.ConnectionStrings("YourConnectionName").ConnectionString;
	   }
   //return the connection string to the calling method
   return strReturn;
 }



Now, this method does several things:
  • Checks to see if a connection name was provided

  • If one was provided get the connection information for that name

  • If one wasnt provided it returns the default connection information (as set by you the developer)

  • Return the connection information to the calling method

Most people are retrieving data for populating a control, whether it be a DataGrid, A DataRepeater, etc. The easiest way I've found to populate these controls is with a BindingSource. According to the MSDN, this is the definition of a Binding Source:

Quote

The BindingSource component serves many purposes. First, it simplifies binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data sources. This is accomplished by attaching the BindingSource component to your data source using the DataSource property.

More: BindingSource


So to retrieve a BindingSource for a Data control I use this method

/// <summary>
/// Returns a BindingSource, which is used with, for example, a DataGridView control
/// </summary>
/// <param name="cmdSql">"pre-Loaded" command, ready to be executed</param>
/// <returns>BindingSource</returns>
/// <remarks>Use this function to ease populating controls that use a BindingSource</remarks>
public static BindingSource GetBindingSource(OleDbCommand cmd)
   {
	  //declare our binding source
	  BindingSource oBindingSource = new BindingSource();
	  // Create a new data adapter based on the specified query.
	  OleDbDataAdapter daGet = new OleDbDataAdapter(cmdSql);
	  // Populate a new data table and bind it to the BindingSource.
	  DataTable dtGet = new DataTable();
	  //set the timeout of the OleDbCommandObject
	  cmd.CommandTimeout = 240;
	  dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture;
	  try
		 {
			//fill the DataTable with the OleDbDataAdapter
			daGet.Fill(dtGet);
		 }
		 //check for errors
		 catch (Exception ex)
		 {
			 MsgBox(ex.Message,"Error in GetBindingSource");
			 return null;
		 }
	  //set the DataSource for the BindingSource to the DataTable
	  oBindingSource.DataSource = dtGet;
	  //return the BindingSource to the calling method or control
	  return oBindingSource;
   }



Heres how the GetBindingSource method works:
  • Pass the method your OleDbCommand Object from your method (this is already created and "pre-loaded" when you pass it)

  • Create a OleDbDataAdapter Object based on this OleDbCommand Object

  • Create and fill a DataTable Object with the OleDbDataAdapter (this executes your OleDbCommand Object)

  • Set the DataSource property of your BindingSource Object to you now filled DataTable Object

  • Return the BindingSource Object to the calling method for binding

The last thing to add to your DataAccess class is a way to open and close the OleDbConnection based on its current state. I created a method called HandleConnection, and you pass a OleDbConnection, the check its state and act accordingly.

public static void HandleConnection(OleDbConnection oCn)
{
		//do a switch on the state of the connection
	switch (oCn.State) {
		case ConnectionState.Open: //the connection is open
						//close then re-open
			oCn.Close();
						oCn.Open();
			break;
		case ConnectionState.Closed: //connection is open
						//open the connection
			oCn.Open();
			break;
		default:
			oCn.Close();
			oCn.Open();
			break;
	}
}



This covers 3 important methods, and seperates your data work from your form, also saves a lot of coding down the road. For the next part you can either create a new Class (my choice) or put it right in your form .cs file.

In this function we pass the values we want inserted into our table (I only have 3 in this example, there can be as many as you need though). We then create the objects we need for this, OleDbCommand, OleDbConnection and a String variable query. We set the query variable to the query we want to execute (or the inline SQL if you go that route), then we set the properties of our OleDbCommand Object. Once this is complete we open the connection to the database, we use ExecuteNonQuery to insert the values. ExecuteNonQuery is what you use when you're not returning a value from the database. Once we complete the insert we check the status and act accordingly, then we close the connection. Here is more on the objects we create.

Now Lets take a look at how we would insert a record into our database using the OleDb Data Objects in C#

public static bool InsertNewRecord(string item1, string item2, string item3)
{
	//Create the objects we need to insert a new record
	OleDbConnection cnInsert = new OleDbConnection(GetConnectionString("YourConnName"));
	OleDbCommand cmdInsert = new OleDbCommand();
	string query = "INSERT INTO YourTable(column1,column2,column3) VALUES(@item1,@item2,@item3)";
	int iSqlStatus;

	//Clear any parameters
	cmdInsert.Parameters.Clear();
	try {
		//Set the OleDbCommand Object Properties

		//Tell it what to execute
		cmdInsert.CommandText = query;
		//Tell it its a text query
		cmdInsert.CommandType = CommandType.Text;
		//Now add the parameters to our query
		//NOTE: Replace @value1.... with your parameter names in your query
		//and add all your parameters in this fashion
		cmdInsert.Parameters.AddWithValue("@value1", item1);
		cmdInsert.Parameters.AddWithValue("@value2", item2);
		cmdInsert.Parameters.AddWithValue("@value3", item3);
		//Set the connection of the object
		cmdInsert.Connection = cnInsert;


		//Now take care of the connection
		HandleConnection(cnInsert);

		//Set the iSqlStatus to the ExecuteNonQuery 
		//status of the insert (0 = failed, 1 = success)
		iSqlStatus = cmdInsert.ExecuteNonQuery;

		//Now check the status
		if (!iSqlStatus == 0)
		{
			//DO your failed messaging here
			return false;
		}
		else
		{
			//Do your success work here
			return true;
		}
	}
	catch (Exception ex) {
		MsgBox(ex.Message, "Error");
	}
	finally {
		//Now close the connection
		HandleConnection(cnInsert);
	}
}




Notice here, even using inline SQL, we still use the Parameters.AddWithValue("@value1",item1), always do your inline SQL like this to prevent a SQL Injection Attack

Next I will show you how to return all the rows in a table, this is used for displaying all the data of course and is the easiest thing for a new programmer to do. Here we have no parameters to pass to our method, it it's a simple select, then populate the BindingSource and return it to the calling method.

public BindingSource GetAllRecords()
{
	//create a OleDbConnection Object, 
	//use the GetConnectionString from the DataAccess class 
	//to retrieve the connection string
	OleDbDataAdapterOleDbConnection cnGetRecords = new		 

OleDbConnection(DataAccess.GetConnectionString("YourConnectionName"));
	//create a OleDbCommand Object for executing our SQL query
	OleDbCommand cmdGetRecords = new OleDbCommand();
	//String variable that will hold the name of your query
	string query = "SELECT * FROM YourTableName";
	//OleDbDataAdapter that will be used to fill your DataTable
	OleDbDataAdapter daGetRecords;
	//DataTable that will be bound to your DataGrid
	DataTable dtGetRecords;
		   
	//Set your OleDbCommand Object Properties
	cmdGetRecords.CommandText=query; //This tells it what SQL to execute
	cmdGetRecords.CommandType = CommandType.Text; //this tells your OleDbCommand Object executing a text query

	try{
		  //open out database connection
		  //first check to see if its closed
		  if (cnGetRecords.State == ConnectionState.Closed){
			  //connection is closed so open it
			  cnGetRecords.Open;
		  }
		  else{
				 //connection is still open so close then re-open
				 cnGetRecords.Close;
				 cnGetRecords.Open;
			   }

		   //create BindingSource to return for our DataGrid Control
		   BindingSource oBindingSource = DataAccess.GetBindingSource(cmdGetRecords);
			   
		   //now check to make sure a BindingSource was returned
		   if (!oBindingSource == null){
			  //return the binding source to the calling method
			  return oBindingSource;
		   }
		   else { //no binding source was returned 
					//let the user know the error
					throw new exception("There was no BindingSource returned");  
				}
		   }
		   //check for any errors
		   catch (exception ex){
			   MsgBox(ex.Message, "Error Retrieving Data");
	   }
	   //now we close the connection
	   cnGetRecords.Close;	   
}




Lets dive into this method. First, like the GetBindingSource it returns a BindingSource Object, this can be bound to a DataGrid, a DataRepeater or most other data controls. First thing you do is create your connection using the GetConnectionString method in the DataAccess class. You then create a couple more objects:
  • A OleDbConnection Object -> This will be used to connect to our database

  • A OleDbCommand Object -> This will perform your query execution

  • A OleDbDataAdapter -> This will be used to fill your dataset

  • A DataTable -> Once filled this will be bound to your BindingSource for binding to your control

  • A String (query) -> This will hold the name of your query

This method will return a populated BindingSource so you can bind it to your data control and display your data. A query with no parameters is the simplest database query, but lets say you want to filter your data by only returning certain data that falls into a certain category, you would then need parameters in your query. Lets take a look at a method that does this.

public static BindingSource GetRecordsById(int id)
{
	//query to execute
	string query = "SELECT * FROM YourTable WHERE YourID = @id";
	//OleDbConnection Object to use
	OleDbConnection cnGetRecords = new	 OleDbConnection(amaDBHelper.GetConnectionString("YourConnectionName"));
	//OleDbCommand Object to use
	OleDbCommand cmdGetRecords = new OleDbCommand();
	//OleDbDataAdapter Object to use
	OleDbDataAdapter daAgents = new OleDbDataAdapter();
	DataSet dsGetRecords = new DataSet();
	//Clear any parameters
	cmdGetRecords.Parameters.Clear();
	try
	  {
		//set the OleDbCommand Object Parameters
		cmdGetRecords.CommandText = query; //tell it what to execute
		cmdGetRecords.CommandType = CommandType.Text; //tell it its executing a query
		//heres the difference from the last method
		//here we are adding a parameter to send to our query
		//you use the AddWithValue, then the name of the parameter in your query
		//then the variable that holds that value
		cmdGetRecords.Parameters.AddWithValue("@year", id);
		//set the state of the OleDbConnection Object
		DataAccess.HandleConnection(cnGetRecords);
		//create BindingSource to return for our DataGrid Control
		BindingSource oBindingSource = DataAccess.GetBindingSource(cmdGetRecords);
		//now check to make sure a BindingSource was returned
		if (!oBindingSource == null)
		 {
			//return the binding source to the calling method
			return oBindingSource;
		 }
			else //no binding source was returned
				{
					//let the user know the error
					throw new exception("There was no BindingSource returned");
				}
		 }
		catch (Exception ex)
		{
			MsgBox(ex.Message, "Error Retrieving Data");
		}
		//now we close the connection
		DataAccess.HandleConnection(cnGetRecords);
}



There is essentially one difference in this method and the previous one, its the line cmdGetRecords.Parameters.AddWithValue("@year", year);. The AddWithValue accepts 2 parameters:
  • The parameter name in your query (for this example the name is @year)

  • The variable that is holding that value (in this case we pass it with the signature of the method -> year)

We Will now look at how we would update a current record in our database using the OleDb Data Objects in the .Net
Framework. In this function we pass the values of the record we want to update, along with the id of the record to update (I only have 2 values in this example but you can have more in yours). We then create the objects we need for this, OleDbCommand, OleDbConnection and a String variable query. We set the query variable to the query we want to execute, then we set the properties of our OleDbCommand Object.

Once this is complete we open the connection to the database, we use Parameters.AddWithValue to add the values of our parameters.

NOTE:ExecuteNonQuery is what you use when you're not returning a value from the database. Once we complete the insert we check the status and act accordingly, then we close the connection. Here is the function:

public static bool UpdateRecord(string item1, string item2, int id)
{
	//Create the objects we need to insert a new record
	OleDbConnection cnUpdate = new OleDbConnection(GetConnectionString("YourConnName"));
	OleDbCommand cmdUpdate = new OleDbCommand();
	string query = "UPDATE YourTable SET column1 = @value1,column2 = @value2 WHERE YourId = @id";
	int iSqlStatus;

	//Clear any parameters
	cmdUpdate.Parameters.Clear();
	try {
		//Set the OleDbCommand Object Properties

		//Tell it what to execute
		cmdUpdate.CommandText = query;
		//Tell it its a text query
		cmdUpdate.CommandType = CommandType.Text;
		//Now add the parameters to our query
		//NOTE: Replace @value1.... with your parameter names in your query
		//and add all your parameters in this fashion
		cmdUpdate.Parameters.AddWithValue("@value1", value1);
		cmdUpdate.Parameters.AddWithValue("@value2", value2);
		cmdUpdate.Parameters.AddWithValue("@YourID", id);
		//Set the connection of the object
		cmdUpdate.Connection = cnUpdate;

		//Now take care of the connection
		HandleConnection(cnUpdate);

		//Set the iSqlStatus to the ExecuteNonQuery 
		//status of the insert (0 = success, 1 = failed)
		iSqlStatus = cmdUpdate.ExecuteNonQuery;

		//Now check the status
		if (!iSqlStatus == 0)
		{
			//DO your failed messaging here
			return false;
		}
		else
		{
			//Do your success work here
			return true;
		}
	}
	catch (Exception ex) {
		MsgBox(ex.Message, "Error");
	}
	finally {
		//Now close the connection
		HandleConnection(cnUpdate);
	}
}




The last thing we will look at in this tutorial will be deleting a record from your table. To do this, we pass the ID of the record as a parameter to our query string. We then create the objects we need for this, OleDbCommand, OleDbConnection and a String variable query. We set the query variable to the query we want to execute , then we set the properties of our OleDbCommand Object.

public static bool DeleteRecord(int id)
{
	//Create the objects we need to insert a new record
	OleDbConnection cnDelete = new OleDbConnection(GetConnectionString("YourConnName"));
	OleDbCommand cmdDelete = new OleDbCommand();
	string query = "DELETE FROM YourTable WHERE YourID = @id";
	int iSqlStatus;

	//Clear any parameters
	cmdDelete.Parameters.Clear();
	try {
		//Set the OleDbCommand Object Properties

		//Tell it what to execute
		cmdDelete.CommandText = query;
		//Tell it its a text query
		cmdDelete.CommandType = CommandType.Text;
		//Now add the parameters to our query
		//NOTE: Replace @value1.... with your parameter names in your query
		//and add all your parameters in this fashion
		cmdDelete.Parameters.AddWithValue("@YourID", id);
		//Set the connection of the object
		cmdDelete.Connection = cnDelete;

		//Now take care of the connection
		HandleConnection(cnDelete);

		//Set the iSqlStatus to the ExecuteNonQuery 
		//status of the insert (0 = success, 1 = failed)
		iSqlStatus = cmdDelete.ExecuteNonQuery;

		//Now check the status
		if (!iSqlStatus == 0)
		{
			//DO your failed messaging here
			return false;
		}
		else
		{
			//Do your success work here
			return true;
		}
	}
	catch (Exception ex) {
		MsgBox(ex.Message, "Error");
		return false;
	}
	finally {
		//Now close the connection
		HandleConnection(cnDelete);
	}
}



In this tutorial, we have covered the basics of working with the OleDb Data Objects in C#. The topics we've covered are:
  • Retrieving the connectionString from the app.config

  • Retrieving a BindingSource for binding our data to a control

  • A method to open and close our connection

  • Inserting a new record into the database

  • Updating a record in the database

  • Deleting a record from a database

The last thing I need to show you is how to use what you've learned in this tutorial to bind your data to a control such as the DataGridView

//for binding to returning all the records
DataGridView1.DataSource = (BindingSource)GetAllRecords();

//for binding to to returning certain records
DataGridView1.DataSource = (BindingSource)GetRecordsByYear((int)TextBox1.Text);



That is the end of the basics of working with OleDb Data Objects in C#. This should at least give you the basic tools for interacting with an Access database in your C# application. Here are the objects we used during this tutorial

If you have any questions please post them here so I can asnwer the question for you and anyone else who might have the same questions. Thanks for reading :)

Happy Coding!

Is This A Good Question/Topic? 1
  • +

Replies To: OleDb Data Objects in C#

#2 TREE  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 09-October 07

Posted 09 October 2007 - 04:32 PM

Really Thanks :D :D
Was This Post Helpful? 0
  • +
  • -

#3 madcatrobert  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 25-January 08

Posted 25 January 2008 - 12:55 PM

The function HandleConnection(OleDbConnection oCn) will never close the db connection.

If it is open, it will close then reopen, if it is closed it will open it and the default closes then reopens it.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1