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
CODE
< 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
CODE
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
CODE
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
CODE
/// <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.
CODE
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.
- A OleDbCommand Object -> This will perform your query execution
- A OleDbConnection Object -> This will be used to communicate with the database
- A String (query) -> This will hold your database query
Now Lets take a look at how we would insert a record into our database using the OleDb Data Objects in C#
CODE
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.
CODE
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.
CODE
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:
CODE
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.
CODE
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
CODE
//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!
