Home and Learn: Intermediate Programming
Stored Procedures and Parameters with C# and VB Net code
Add a text box and a button to the form you have already created. You can add a label, as well, so that your form looks something like this:
What we're going to do here is to enter a Student ID in the text box. When the button is clicked, the Student's details will appear in the DataGrid. We'll use our Stored Procedure for this, the one we set up with Parameters.
Double-click your button to open up the code stub.
For convenience sake, we'll just copy and paste the code from the first button, and then make a few additions. Obviously, it would be better to set up a few functions, or maybe a class, to cut down on duplicate code. But it's nice to compare the two versions, and see how they differ.
Highlight all you code from your first button. Copy and paste it into the code stub for the second button.
The first change we can make is to the name of the Stored Procedure. Change it from NamesAndCourses to the name of our second Stored Procedure, which was GetStudentByID:
C#
SqlCommand cmd = new SqlCommand("GetStudentByID", con);
VB Net
Dim cmd As New SqlClient.SqlCommand("GetStudentByID", con)
The SqlCommand object has a Propertry called CommandType. You can set this to Stored Procedure. Add this line just after your cmd line:
C# and VB Net (without the semicolon)
cmd.CommandType = CommandType.StoredProcedure;
Another Property of the SqlCommand object is Parameters. This will allow us to Add that GetStudentID parameter we set up. Add this line just below the new one:
C#
cmd.Parameters.Add(new SqlParameter("@GetStudentID", 10));
VB Net
cmd.Parameters.Add(New SqlClient.SqlParameter("@GetStudentID", 10))
The Parameters Property has a method called Add. In between the round brackets of Add, you need a new SqlParameter object. In between the round brackets of SqlParameter you can add your parameters and any values for them:
"@GetStudentID", 10
The parameter goes between double quotes, and with the @ sign at the start. After a comma, you type a value for the parameter. Remember how we did it in SQLServer:
EXEC dbo.GetStudentByID 10
A value of 10 is passed to the parameter in the GetStudentByID Stored Procedure. By saying, SqlParameter("@GetStudentID", 10), you're doing the same thing - handing a value to the parameter.
However, we don't want to hard-code a value. We'll replace that soon with a value from the text box on the form. But here's what the code for your new button should look like so far, with the changes and additions highlighted:
C#
VB Net
Try it out with the hard-coded value of 10. You should see this:
The only thing left to do is to get the text box working.
Add the following to the top of your button code:
C#
int studentIdValue = int.Parse(textBox1.Text);
VB Net
Dim studentIdValue As Integer = Val(TextBox1.Text)
This sets up an integer called studentIdValue. But we need to convert the string from the text box to an integer. You can convert a string to an integer in C# with int.Parse(). You'd probably need to do some error checking as well, here, just to make sure it is a valid integer. But we won't bother. In VB Net, you can just use Val().
Now delete the hard-coded 10 from the SqlParameter line and replace it with your int variable:
C#
cmd.Parameters.Add(new SqlParameter("@GetStudentID", studentIdValue));
VB Net
cmd.Parameters.Add(New SqlClient.SqlParameter("@GetStudentID", studentIdValue))
Here's what you code should look like in C#, with the new additions highlighted:
And here's the code for VB Net:
Run your form again. Enter a value in the text box and click your Search button. Here's what you should see if a value of 12 is entered:
Try out the other numbers we set up as Student IDs, 10 and 11. Try a different number, one we didn't add, and see what happens. Does it crash?
So that's it. That's how to open multiple database tables with C# and VB Net code. Just remember: Stored Procedures can make your life much easier. In the next lesson, you'll see how to create a Stored Procedure to update a table. We'll then write code to execute it.
Stored Procedure to Update a Database Table >>
Back to the Intermediate Programming Contents Page