Home and Learn: Intermediate Programming
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();
Dim binder As New BindingSource()
For C# users, at the top of the code, add the following using statement:
You code should look like this:
Or this, in VB Net:
We can set up a connection string and get that property we set up. Add these two lines inside your button code stub:
conString = Properties.Settings.Default.SDB;
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);
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:
To check if everything is working correctly, you can add a message box (without the semicolon on the end, for VB users):
Then close the connection with: (Again without the semicolon on the end, for VB users.)
C# .NET users, your code should look like this:
VB NET users, this is your code:
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);
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);
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();
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
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;
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:
Your code should look like this in C#:
And this in VB Net:
You can run your code, now. Click your button and you should see all the results appear in your Data Grid:
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.
Email us: enquiry at homeandlearn.co.uk