Category Archives: ADO.NET

Display Excel data in a DataGridView

In this post, I will show you how to use ADO.NET to load data from an Excel file into a DataGridView.

The principle is pretty straightforward, the first thing we will do is ask the user to select an Excel file. Once selected, we will connect to the file using an OleDbConnection object and once connected we will then use the GetSchema method of the OleDbConnection object to load all Worksheets (tables in ADO.NET speak) into a combo box. When the user selects a worksheet name from the combo box we will then select all data from that sheet and display it in a DataGridView by using standard SQL syntax.

So, create a new Windows Form project with any name you like and add the following controls to your form so that it resembles the screen shot below:

Control TypeProperties
LabelName: excelFileLabel
Text: Excel File
Text BoxName: excelFileTextBox
Anchor: Top, Left, Right
ButtonName: openFileButton
Text: ...
Anchor: Top, Right
LabelName: worksheetsLabel
Text: Worksheets
Combo BoxName: worksheetsComboBox
Anchor: Top, Left, Right
Data Grid ViewName: Name: excelDataGridView
Anchor: Top, Left, Right, Bottom
ButtonName: closeButton
Text: Close
Anchor: Bottom, Right

excelDataGridView

Now add the following code to your form:

You can also download a version I made earlier.

Read and Write Images to Access Ole Object Field

This short snippet demonstrates how to write an image (jpg file) to an Ole Object field in Microsoft Access and then how to retrieve that image and display it in a picture box.

First, create an Access database (I was using Office 2013 but earlier versions should work) and a table with one field called pic. Set the field type of the pic field to Ole Object.

Writing the image to the database

The following code write an image to the pic field.

Once you have executed this statement you can check your database table and you should see that the field contains the text “Long Binary Data”, this is our image.

Reading an image from the Ole Object field

This next piece of code will read Long Binary Data value from the Ole Object field and convert it to a byte array. Once converted, the byte array will then be converted to an Image which we can then use to assign to a picture box or use in any other way we like. For this example, add a PictureBox control to your form and then use the following code to read the image:

 

Get database schema using GetSchema

This tutorial demonstrates the use of the GetSchema method to find out the structure of a database. This includes tables within the database, columns and indexes of a table and views.

The GetSchema method was introduced as of .NET Framework 2.0, and is a method available as part of the System.Data.Common.DBConnection class which is inherited by the OleDbConnection and the SqlConnection classes meaning that it is very easy to obtain schema information for both SQL Server databases and OleDb databases such as Access and even Excel.

In this tutorial I will be focusing on OleDb and Access databases but I encourage you to play around with the code and see how easy it is to change to work with an SQL Server. Also, I am only focusing on a small amount of data that is available so again, please explore the schema information that is returned at various points in the code to see the wealth of information that can be obtained.

Creating the project

To start, create a new C# Windows Forms Application and when the project has loaded rename Form1 to mainForm.cs.

To create the UI, add the following controls to the form and set their properties according to the list below. There is a screen shot of the UI to assist in laying out the controls:

  • Label. Name: databasePathLabel, Text: Database
  • TextBox. Name: databasePathTextBox, Anchor: Top, Left, Right
  • Button. Name: openDatabaseButton, Anchor: Top, Right, Text: “…”
  • Label. Name: schemaLabel, Text: Schema
  • TreeView. Name: schemaTreeView, Anchor: Top, Bottom, Left, Right
  • Label. Name: viewDefinitionLabel, Text: View Definition, Anchor: Bottom, Left
  • TextBox. Name: viewDefinitionTextBox, Anchor: Bottom, Left, Right, Multiline: True, ScrollBars: Both
  • Button. Name: retrieveSchemaButton, Anchor: Bottom, Left, Text: Retrieve Schema, Enabled: False
  • Button. Name: exitButton, Anchor, Bottom, Right, Text: Exit

UI Layout

Classes

The following classes are used to store schema details. These are very basic as to capture all information returned would detract from the focus of this tutorial. However, you are encouraged to expand on these to suit your requirements.

Add the following classes to your mainForm code file, outside of the “public partial class mainForm : Form” but within the namespace.

Note that the Table class has properties for both the Column and Index classes so that we essentially have a one to many relationship, one table > many columns and many indexes.

We need two form level variables to reference both the Table and the View classes so add the following two declarations to your code file below “public partial class mainForm : Form”

 Basic UI code

Now add the following code to your form for the basic functions such as opening a dialogue to select a database, exiting the application etc.

 GetSchema code

Before getting into the code below, ensure you have the following two using statements:

In order to get schema information from the database we have to have an open connection to a database. The following code is the click event of the retrieveSchemaButton:

This simply connects to the database chosen and then calls two sub routines (GetTables and GetViews). These two sub routines which are shown below will populate our classes before then calling a final routine to update our TreeView with the output.

The GetTables routine below calls the GetSchema method of the OleDbConnection to request table information. When calling GetSchema, there are three overloads, the default returns high level schema information. The second returns schema information related to a specific type such as Tables or Views and the third which we are using in this instance provides a set of restriction values. In the GetTables routine below you will note for the first argument we are asking for Tables and in the second argument we provide a string array containing the restrictions which in this instance is set to TABLE. (More on restrictions later in further reading). If we were to omit the restrictions then we would have all tables returned regardless of their type. For example, we would retrieve information on Tables, Linked Tables, System Tables etc.

Note also that this code makes additional calls to GetColumns and GetIndexes for each table that is returned. Within those routines you will see a different set of restrictions that are passed, in this case the name of the table for which we want schema information.

The next piece of code is responsible for returning Views (queries) from the database:

In this routine we don’t supply any restrictions to the GetSchema call and simply ask it to return all Views. You will also note that we obtain the VIEW_DEFINITION, this is essentially the SQL statement for the view.

Displaying the output

The final piece of code is responsible for updating the TreeView to show all tables with their columns and indexes and all views.

Note that when we are adding the View information to the TreeView we also add the view object to the Tag property of the node. This is then used in the AfterSelect event of the TreeView to obtain the View Definition in order to display it in the Text Box below.

Conclusion

As you can see, it is relatively straightforward to retrieve information about an Access database. As mentioned previously, I would recommend that you set breakpoints within the code for each of the [inline]GetSchema[/inline] calls and use the inspector to view the contents of the DataTable returned to see what type of information can be retrieved.

Further Reading

The following MSDN pages should be consulted for additional information:

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.

If you don’t want to create the database yourself then you can download one I made earlier.

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:

ContactsTableDefinition

 

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.

You can download the solution but I recommend following the tutorial and creating the solution yourself.

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 typeNameAdditional properties
DataGridViewcontactsDataGridViewAnchor: Top, Left, Right, Bottom
ButtoncloseButtonText: Close
Anchor: Right, Bottom
ButtonaddNewContactButtonText: Add New
Anchor: Left, Bottom
ButtonupdateContactButtonText: Update
Anchor: Left, Bottom
ButtondeleteContactButtonText: Delete
Anchor: Left, Bottom

Your form should look similar to:

Main Form Design

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 typeNameAdditional properties
LabeltitleLabelText: Title
TextBoxtitleTextBoxAnchor: Top, Left, Right
LabelfirstNameLabelText: First name
TextBoxfirstNameTextBoxAnchor: Top, Left, Right
LabellastNameLabelText: Last name
TextBoxlastNameTextBoxAnchor: Top, Left, Right
LabeladdressLabelText: Address
TextBoxaddressTextBoxAnchor: Top, Left, Right
LabeltelephoneLabelText: Telephone number
TextBoxtelephoneTextBoxAnchor: Top, Left, Right
LabelemailLabelText: Email address
TextBoxemailTextBoxAnchor: Top, Left, Right
LabelageLabelText: Age
TextBoxageTextBoxAnchor: Top, Left, Right
LabeldateOfBirthLabelText: Date of birth
TextBoxdateOfBirthTextBoxAnchor: Top, Left, Right
ButtonokButtonText: Ok
Anchor: Bottom, Left, Right
DialogResult: Ok
ButtoncancelButtonText: Cancel
Anchor: Bottom, Left, Right
DialogResult: Cancel

Your form should look similar to:

Contact Details Form Design

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.
  • System.Data.OleDb.OleDbCommand – Provides methods for executing commands (SQL statements) against the database.
  • System.Data.OleDb.OleDbDataAdapter – Provides methods for populating DataSets and DataTables among other things.
  • System.Data.OleDb.OleDbDataReader – Provides a forward only data retrieval mechanism.

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:

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:

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

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

Main Form with records displayed

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()):

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:

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).

Now add the following code to the updateContactButton:

The only difference with this code is the assignment of the ID to the contactId property of the contactDetailsForm. The remainder of the code is identical to the addNewContactButton.

Open the contactDetailsForm and add the following code to the click event of the okButton:

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.

Add the following routine to the contactDetailsForm:

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):

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:

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).

Further reading and resources: