Datasets and Data Adapters in C# .NET

<< Part of an ongoing lesson - first part is here >>

 

The connection to the database has been made. The next step is to pull the records from our workers table. To do that, a Dataset and a DataAdapter are needed.

A Dataset is where all your data is held when it is pulled from the database table. Think of it like a grid that you see on a spreadsheet. The Columns in the grid are the Columns from your database table. The Rows represent a single entry in the table.

The Dataset needs to be filled with data. However, because the Dataset and Connection object can't see each other, they need someone in the middle to help them out - the DataAdapter. The DataAdapter will fill the Dataset with records from the database.

So we need to set up two more objects, a Dataset and a DataAdapter. This is true whether you use a SQL Server database or an Access one.

To create a Dataset object, add the following just above the form load event:

DataSet ds1;

Inside of the form load event, create a new object from the Dataset type we've called ds1:

ds1 = new DataSet();

You coding window will then look like this for Access users:

And like this for SQL Server Express users:

For the DataAdapter, add the following outside of the form load event (Access Users):

System.Data.OleDb.OleDbDataAdapter da;

For SQL Server Express users, you need to add this instead:

System.Data.SqlClient.SqlDataAdapter da;

In both cases, we're setting up a DataAdapter variable and calling it da.

Inside of the form load event, we can create a new object from our da variable. Here's the code for Access users:

string sql = "SELECT * From tblWorkers";

da = new System.Data.OleDb.OleDbDataAdapter( sql, con );

And here's the code for SQL Server Express users:

string sql = "SELECT * From tblWorkers";

da = new System.Data.SqlClient.SqlDataAdapter( sql, con );

The first line of both is the same:

string sql = "SELECT * From tblWorkers";

This sets up a string variable called sql. SQL stands for Structured Query Language. It's a language used to pull records from a database, and variants of it are used for all database systems. Whether you use Access or SQL Server, you use the Structured Query Language on the database itself. (SQL Server's variant is called T-SQL. The T stands for Transact.)

Keywords in SQL are SELECT, UPDATE, WHERE, and a whole lot more besides. The * symbol means "all the records". So we're saying, "Select all the records from the table called tblWorkers".

The DataAdapter object will use your SQL commands to pull the records from the database. But you need to tell it which connection object to use. That's why, in between the round brackets of both code, we have this:

( sql, con );

Our new DataAdapter object will then know what records to pull (sql), and where to pull them from (con).

But here's what your coding windows should look like. Access first:

And here's the code for SQL Server Express (minus the message boxes and dispose line):

Run your programme and see if it works. The only way you can tell at the moment, though, is through any message boxes you may have. Or if it crashes!

To fill the dataset with records from the database, you use the DataAdapter and issue the Fill command. This is the same for both Access and SQL Server Express:

da.Fill( ds1, "Workers" );

What this does is to Fill a Dataset called ds1. After the comma, you can type an identifying name for this particular Fill. We've called ours "Workers".

After the Fill command has been issued, the records from the SQL command are stored in the Dataset. This, remember, is just like a grid with Columns and Rows.

So add the line to your code. Put it just before the con.Close() line.

We can now display the data from the database on a form.

 

<-- Connect an Access Database | Display Data from the Dataset -->

<--Back to the C# .NET Contents Page

View all our Home Study Computer Courses