Home and Learn: Intermediate Programming

Writing the code for the Stored Procedure

Now that you have a form and a connection object set up, double-click your button to open up a code stub.

We're going to need something called a Binding Source. This is used to bind the Data Grid to the Data Table we'll create shortly. Add the following line to you code, just above the button code stub but inside of the class:

BindingSource binder = new BindingSource();

VB Net

Dim binder As New BindingSource()

For C# users, at the top of the code, add the following using statement:

using System.Data.SqlClient;

You code should look like this:

Adding a binding source for a datagrid in C# code

Or this, in VB Net:

Adding a binding source for a datagrid in VB NET code

We can set up a connection string and get that property we set up. Add these two lines inside your button code stub:

string conString;
conString = Properties.Settings.Default.SDB;

VB Net

Dim conString As String
conString = My.Settings.SDB

The SDB part was the one we set up. If you typed something else as your connection name, select that instead.

We now need a SqlConnection object so we can open up a connection:

SqlConnection con = new SqlConnection(conString);

VB Net

Dim con As New SqlClient.SqlConnection(conString)

In between the round brackets of you SqlConnection object, you need your connection string. Open a connection with this line:

con.Open();

VB Net

con.Open()

To check if everything is working correctly, you can add a message box (without the semicolon on the end, for VB users):

MessageBox.Show("All OK");

Then close the connection with: (Again without the semicolon on the end, for VB users.)

con.Close();

C# .NET users, your code should look like this:

Setting up a Sql Connection object in C# code

VB NET users, this is your code:

VB Net code to Set up a Sql Connection object

Next, we can create a SqlCommand object and grab one of our Stored Procedures.

After con.Open, add this line: (you can get rid of your message box, if you want, or comment it out.)

SqlCommand cmd = new SqlCommand("NamesAndCourses", con);

VB Net

Dim cmd As New SqlClient.SqlCommand("NamesAndCourses", con)

In between the round brackets of the new SqlCommand we have two things. The first is the name of a Stored Procedure in double quotes. After a comma, we then have the connection object.

You can add parameter information to the command object, and we'll do that with the second button. But our first Stored Procedure didn't have any, so the single line is enough.

We now need a Data Adapter. This is an object that sits between the database and the data table we'll add. So here's your next line of code:

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

VB Net

Dim adapter As New SqlClient.SqlDataAdapter(cmd)

Notice that in between the round brackets of the SqlDataAdapter is the command object we set up, the one with the Stored Procedure and the Connection object.

We need a Data Table. This table will be filled with the result we get back from the Stored Procedure. (Because we have a Data Grid we can't use a DataSet, like last time.) To set up a Data Table, add this line:

DataTable dataTable = new DataTable();

VB Net

Dim dataTable As New DataTable()

You use the adapter to fill the table with the results. Here's the line: (VB Net usrs, delete the semicolon at the end):

C# and VB Net

adapter.Fill(dataTable);

The Data Grid has a DataSource property. You can set this to the Binding Source we set up at the top of the code:

dataGridView1.DataSource = binder;

VB Net

DataGridView1.DataSource = binder

The Binding Source object also has a DataSource property. We can point our Data Table at this. The Data Table, remember, now has all the results from the database. Add this: (Again, no semicolon on the end, if you're coding in Visual Basic.)

binder.DataSource = dataTable;

The final line is to close the connection:

con.Close();

Your code should look like this in C#:

Using a SqlCommand object for a Stored Procedure using C# code

And this in VB Net:

Using a SqlCommand object for a Stored Procedure using VB Net code

You can run your code, now. Click your button and you should see all the results appear in your Data Grid:

Windows Form showing data pulled from two Sql Server database tables

We have successfully grabbed data from two database tables and displayed the results in a DataGrid. Now let's use our other Stored Procedure so that we can search for a record based on the StudentID.

Stored Procedure to Search a Database >>

Back to the Intermediate Programming Contents Page