Just another software blog

ADO.NET For Beginners Tutorial

Introduction

The purpose of this article is to introduce those new to database programming the basics of using ADO.NET (the database API provided by the .NET Framework). When I first started database programming, there were many books on the subject but they all focused on using the tools and wizards available within the IDE. What they left out was how to write the code yourself. There is nothing inherently wrong with using tools (and in many cases you should do) but it really is worth understanding how to do this by hand coding your database access and manipulation code so that you can then understand what the tools are doing.

This article will focus on several areas of ADO.NET in order to build a fully working database application. The application in question is a simple address book. We will look at how we connect to a database, perform CRUD (Create, Read, Update and Delete) operations on the database and finally how we can query the database for data.

Requirements

For this tutorial, I am using Microsoft Access 2013 and Visual Studio Professional 2013 with C#. However, all of the code in this article should be compatible with .NET 2.0 (Visual Studio 2005) and upwards. Also, if you have an older version of Microsoft Access (or newer – depending on when you are reading this) then it should simply be a case of providing the appropriate connection string to work with your version. See the Further reading section at the end of this article.

If you are developing with Visual Basic.NET then you can download a VB version of the solution which you can use while following this tutorial.

Disclaimer

Before we begin, I want to point out that this tutorial is focused purely on demonstrating how to perform various actions using ADO.NET. The code in this tutorial is certainly not production quality. For example, I am using straight forward SQL with no validation or use of parameters to avoid SQL Injection Attacks. Likewise, I do not provide any validation code to ensure values are present before submitting them to the database so exceptions can occur. I will however point out further resources that you can refer to after you have been through the tutorial.

Creating the database

The first activity is to create the Microsoft Access database that will be used for storing the address book information. In order to keep this tutorial focused, the address book database is kept very simple with one table called Contacts.

Open Microsoft Access and create a new database called AddressBook.accdb. You can go ahead and delete the default table. Now, create a new table with the following fields and save it. Call the new table Contacts:

As mentioned earlier, this database is very simple and will not win any awards for good database design but is ideal for this tutorial.

At this point it would be useful to populate the Contacts table with a few entries so that we have something to display when we start creating the application.

Creating the application

Now we will create a Windows Forms application in Visual Studio that will be used to display and manage data in the address book database.

Open Visual Studio and create a new Windows Forms application. Feel free to call it whatever you like. Once the project has been created you will have the default Form1 form open. Rename this form to mainForm. If you are asked if you would like to update all references within the project, say yes. This form will display all contacts within the address book and provide buttons for managing the data (add new, update and delete).

Add the following controls to the mainForm:

Main Form Controls

Control type

Name

Additional properties

DataGridView

contactsDataGridView

Anchor: Top, Left, Right, Bottom

Button

closeButton

Text: Close
Anchor: Right, Bottom

Button

addNewContactButton

Text: Add New
Anchor: Left, Bottom

Button

updateContactButton

Text: Update
Anchor: Left, Bottom

Button

deleteContactButton

Text: Delete
Anchor: Left, Bottom

Your form should look similar to:

Next, add a new Windows Form to the solution by right clicking on the project within the Solution Explorer and selecting Add > Windows Form. When presented with the Add New Item dialogue enter contactDetailsForm as the name for the new Windows Form.

Add the following controls to the contactDetailsForm:

Contact Details Form Controls

Control type

Name

Additional properties

Label

titleLabel

Text: Title

TextBox

titleTextBox

Anchor: Top, Left, Right

Label

firstNameLabel

Text: First name

TextBox

firstNameTextBox

Anchor: Top, Left, Right

Label

lastNameLabel

Text: Last name

TextBox

lastNameTextBox

Anchor: Top, Left, Right

Label

addressLabel

Text: Address

TextBox

addressTextBox

Anchor: Top, Left, Right

Label

telephoneLabel

Text: Telephone number

TextBox

telephoneTextBox

Anchor: Top, Left, Right

Label

emailLabel

Text: Email address

TextBox

emailTextBox

Anchor: Top, Left, Right

Label

ageLabel

Text: Age

TextBox

ageTextBox

Anchor: Top, Left, Right

Label

dateOfBirthLabel

Text: Date of birth

TextBox

dateOfBirthTextBox

Anchor: Top, Left, Right

Button

okButton

Text: Ok
Anchor: Bottom, Left, Right
DialogResult: Ok

Button

cancelButton

Text: Cancel
Anchor: Bottom, Left, Right
DialogResult: Cancel

Your form should look similar to:

Now that we have everything setup we can start looking at the appropriate code to display and manage data from the address book database.

The code

When using ADO.NET we make use of assemblies provided by the System.Data namespace and in particular the System.Data.OleDb namespace which provides the necessary API’s and objects for working with Microsoft Access.

If you open the Object Browser in Visual Studio (View > Object Browser) you will see an entry called System.Data. If you expand this entry you will see many namespaces such as System.Data.OleDb, System.Data.SqlClient (for working with SQL Server) and System.Data.Odbc (for working with Odbc databases) among others.

The objects that we will be using in this tutorial are:

System.Data.DataTable – An in memory table containing data that can be bound to controls or manipulated in code.

System.Data.OleDb.OleDbConnection – Provides methods for connecting to a database.

Retrieving all contacts from the Contacts table

When we run the address book application we will want to see all entries in our Contacts table (I hope you have added some records to the Contacts table otherwise the display will be empty). To do this, we will first need to connect to the database. Let’s start by creating a sub routine within our mainForm that will connect to the database and populate a DataTable with all records from the Contacts table. This DataTable will then be used as the data source for the Data Grid View. So open the code window for the mainForm and add the following routine:

//Create the command object that will execute the above sql statement. Note that the constructor allows you to pass the statement

//to be executed and the connection on which to execute the statement.

command=newOleDbCommand(contactsSql,connection);

//Open the connection.

connection.Open();

//Create a DataSet to store the returned data and a DataAdapter to fetch the data based on the above command

DataTable contactsDataTable=newDataTable();

OleDbDataAdapter contactsAdapter=newOleDbDataAdapter(command);

//Populate the DataSet

contactsAdapter.Fill(contactsDataTable);

//We can close the connection to the database now that the DataSet has been populated

connection.Close();

//Bind the DataSet to the contactsDataGridView.

contactsDataGridView.DataSource=contactsDataTable;

}

catch(OleDbExceptione)

{

MessageBox.Show(e.Message);

}

catch(InvalidOperationExceptione)

{

MessageBox.Show(e.Message);

}

catch(Exceptione)

{

MessageBox.Show(e.Message);

}

finally

{

if(connection!=null)

connection.Dispose();

if(command!=null)

command.Dispose();

}

}

Be sure to replace the <Your path> within the connectionString with the fully qualified path to your address book database.

The RetrieveAllContacts() routine will be called from several places hence the reason for adding it to its own sub routine.

If you look at the code you can see that we are creating a number of objects (OleDbConnection, OleDbCommand, OleDbDataAdapter and DataTable). The OleDbConnection is used to connect to the database. The OleDbCommand object will be used to execute the SELECT SQL statement against the database. Notice in the instantiation of the OleDbCommand that we pass both the connection object and the SQL statement that we want to execute. The next thing we do is setup the OleDbDataAdapter which will be used to populate the DataTable with the results of the SELECT statement (i.e. all rows from the Contacts table). With all of these pieces in place we then open the connection to the database and execute the SELECT SQL statement via the OleDbDataAdapter’s Fill() method. Note: Technically, we do not need to explicitly open a connection to the database as the Fill() method of the OleDbDataAdapter will do this for us if the connection is not already open. Finally, with the DataTable populated, we close the connection and bind the DataTable to the contactsDataGridView via its DataSource property.

Now, add a call to the RetrieveAllContacts() sub routine within the load event of the mainForm:

C#

1

2

3

4

privatevoidMainForm_Load(objectsender,EventArgse)

{

RetrieveAllContacts();

}

Also add the following code to the Close buttons click event:

C#

1

2

3

4

privatevoidcloseButton_Click(objectsender,EventArgse)

{

Application.Exit();

}

If you run the application now you should be presented with the mainForm showing all records from the Contacts table:

Creating and updating a contact

Now that we can view all existing contacts, we can move onto creating new contacts. As we have the contactDetailsForm that will be used for both creating and updating contact details we need a mechanism for knowing whether we should be performing an update or a create. For this reason, open the code view of the contactDetailsForm and add the following property just above the constructor for the form (i.e. public ContactDetailsForm()):

C#

1

publicintcontactId{get;set;}//Used to determine if we are adding or updating a record

The above property will be accessed from the mainForm as you will see later. If the value of contactId is zero then we know that we are inserting (creating) a new record, otherwise an existing Id has been passed and therefore we are updating an existing record.

Note: The above property is declared using the syntax supported from VS 2008 upwards.

To open the contactDetailsForm we need to add code to both the addNewContactButton and the updateContactButton. So firstly, add the following code to the addNewContactButton:

C#

1

2

3

4

5

6

7

8

9

privatevoidaddNewContactButton_Click(objectsender,EventArgse)

{

ContactDetailsForm detailsForm=newContactDetailsForm();

detailsForm.ShowDialog();

//If the user has pressed the Ok button then refresh the contactsDataGridView

if(detailsForm.DialogResult==System.Windows.Forms.DialogResult.OK)

RetrieveAllContacts();

}

In the above code we use the ShowDialog() method to open the form which basically means that the mainForm cannot be accessed until the contactDetailsForm is closed. Note the check to see if the DialogResult of the contactDetailsForm is equal to an OK result. This is achieved via the DialogResult property that you set when creating this button and allows us to determine if we should do something (in this case, refresh the list of contacts as the Ok button was pressed and therefore we assume a new contact has been added).

//Provide the connection object with the connection string used to connect to the database.

connection=newOleDbConnection(connectionString);

//Create the command object using the contactSqlStatement and connection object

command=newOleDbCommand(contactSqlStatement,connection);

//Open the connection, execute the statement and then close the connection

connection.Open();

command.ExecuteNonQuery();

connection.Close();

}

catch(OleDbException ex)

{

MessageBox.Show(ex.Message);

}

catch(InvalidOperationException ex)

{

MessageBox.Show(ex.Message);

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

if(connection!=null)

connection.Dispose();

if(command!=null)

command.Dispose();

}

this.Close();

}

There are quite a few things to note about this code. First, note the check to see if we have an Id or not to determine which type of SQL Statement to run. When creating a new record we run an INSERT INTO SQL statement and when we are updating an existing record we run an UPDATE SQL statement.

Next, the SQL statements themselves. Note the use of apostrophes around the arguments (VALUES ‘{0}’, ‘{1}’ etc.). When writing string data to a field, that data must be enclosed within apostrophes. However, note that argument {6} does not have any surrounding characters. This is because it is a number value and therefore does not need to be enclosed in any special characters. Finally, note that {7} is enclosed with the hash (#) character. This is because it is a DateTime field. Finally, also note that when providing textual values to the SQL statement I am using the Replace function to take a single apostrophe and replacing them with two apostrophes. This is known as escaping and is done to stop the SQL string from throwing an error if the textual value already contains an apostrophe. What I mean by this is consider the following SQL Statement:

INSERT INTO Table (Field1) VALUES (‘Fred’)

This is perfectly valid and will not throw any SQL errors. However, consider the following:

INSERT INTO Table (Field1) VALUES (‘O’Reilly’)

This now breaks the string as there is an additional apostrophe within the textual value. To ensure that we can handle textual values that contain an apostrophe we can escape it by supplying two apostrophes so that the string becomes:

INSERT INTO Table (Field1) VALUES (‘O”Reilly’)

A better approach to this is to use parameters, but that is out of the scope of this tutorial (see further reading at the bottom of this article).

Now that we have the appropriate SQL statement to be processed we setup similar objects to those that we have already seen but this time we use the ExecuteNonQuery() method of the OleDbCommand object to execute the SQL statement.

Now if you run the application again you should be able to add a new record. Do not try to update a record yet as we haven’t provided the mechanism for displaying the existing details to be updated yet.

Retrieving a record

In order to update an existing record we first need to display the existing details on the contactDetailsForm so that they can be modified by the user. We have everything in place for providing the Id of the record and for performing the update, we just don’t have the display of the record yet.

//Determine if any rows have been returned before attempting to read it.

if(reader.HasRows)

{

//We have some data so add each field to the text box. We should only have one row due to the use of the WHERE criteria on ID

while(reader.Read())

{

titleTextBox.Text=reader["Title"].ToString();

firstNameTextBox.Text=reader["FirstName"].ToString();

lastNameTextBox.Text=reader["LastName"].ToString();

addressTextBox.Text=reader["Address"].ToString();

telephoneTextBox.Text=reader["Telephone"].ToString();

emailAddressTextBox.Text=reader["Email"].ToString();

ageTextBox.Text=reader["Age"].ToString();

dateOfBirthTextBox.Text=reader["DateOfBirth"].ToString();

}

}

connection.Close();

}

catch(OleDbException ex)

{

MessageBox.Show(ex.Message);

}

catch(InvalidOperationException ex)

{

MessageBox.Show(ex.Message);

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

if(connection!=null)

connection.Dispose();

if(command!=null)

command.Dispose();

}

}

The above code is similar to the code used to create and update a record. The main difference here is the use of the OleDbDataReader and the ExecuteReader() method of the oleDbCommand object. When we want to read a single row or move through a resultset in a forward only way then the DataReader is the most performant way to do this. In the code above, we first check that we have some data returned and if we do we then use the Read() method of the DataReader to access the row. We can then access each individual column within the row via its index. The index can be an ordinal number (position within the table) or accessed via the column name. For readability, I suggest you use the name of the column.

Add the following code to the load event of the contactDetailsForm that will retrieve the contact record if a contactId has been provided (i.e. we are updating an existing record):

C#

1

2

3

4

5

6

privatevoidContactDetailsForm_Load(objectsender,EventArgse)

{

//If a contactId was passed then retrieve and display the contact

if(contactId>0)

RetrieveContactDetails();

}

Ok, now you can run the application again and you should now be able to add and update records.

Deleting a record

The final piece is to create the code to delete a record. This code is very similar to the code used to update or add a new contact, the only difference is the use of the DELETE SQL statement. Add the following code to the deleteContactButton on the mainForm:

C#

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

privatevoiddeleteContactButton_Click(objectsender,EventArgse)

{

stringdeleteContactSqlStatement=String.Empty;

deleteContactSqlStatement="DELETE FROM Contacts WHERE ID="+(int)contactsDataGridView.CurrentRow.Cells[0].Value;

//Provide the connection object with the connection string used to connect to the database.

connection=newOleDbConnection(connectionString);

//Create the command object using the deleteContactSqlStatement and connection object

command=newOleDbCommand(deleteContactSqlStatement,connection);

//Open the connection, execute the statement and then close the connection

connection.Open();

command.ExecuteNonQuery();

connection.Close();

//Update the contactsDataGridView

RetrieveAllContacts();

}

catch(OleDbException ex)

{

MessageBox.Show(ex.Message);

}

catch(InvalidOperationException ex)

{

MessageBox.Show(ex.Message);

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

if(connection!=null)

connection.Dispose();

if(command!=null)

command.Dispose();

}

}

That’s it, you now have an application that can retrieve all records or a single record from a table, update and add new records and delete records.

As I mentioned at the beginning of this tutorial, this code is not production code and I would highly recommend that you do some further reading, especially regarding the use of SQL statements and parameterised queries to avoid SQL Injection attacks (especially when developing web applications).