DevCity.NET :: ADO.NET for Beginners Part Three. Page 3

Most of what we have done to this point is pretty self explanatory and doesn’t really impact much on the code that we currently have. However, there is one final step in converting this project and that is to change all of the current inline SQL statements that we currently use to calls to the Stored Procedures that we created earlier.

The first routine that we will change is the RetrieveContacts routine on the main form. This is the easiest of all of the changes as the stored procedure that returns the data from the Contacts table does not require any parameters to be passed, i.e. it does not utilise any WHERE clauses in its original SQL Statement. The only change that you will need to make in this routine is to change the constructor for the SqlDataAdapter which currently looks like:

1 Dim adapter As New SqlDataAdapter("SELECT * FROM Contacts", conn)

Instead of using the inline Select statement, we will now change this for the name of the Stored Procedure that is responsible for retrieving the data, namely Contacts_AllContacts_Select. This line of code should now look like:

1 Dim adapter As New SqlDataAdapter("Contacts_AllContacts_Select", conn)

As no parameters are required for this stored procedure we can simply state the name of the stored procedure as the CommandText property of the SqlDataAdapter object. If you run the program now you will see that the DataGrid is populated with any contacts that are currently in the Contacts table.

The next routine to change in the main form is the click event of the delete button (btnDelete_Click). The code change here however will be a little different to that above as in order to delete a contact we need to specify the contact ID to be deleted. The current SQL statement does this by providing the Contact ID value as part of the WHERE clause, however we now want to pass this contact ID value as a parameter to the Stored Procedure that will delete the contact for us. In order to do this we need to change the routine a little. If you remove the code within the btnDelete_Click event and paste the following in:

1 If dgContacts.CurrentRowIndex > -1 Then

3 Dim connection As New SqlConnection("Data Source=(Local);Initial Catalog=AddressBook;Integrated Security=SSPI")

4 Dim command As New SqlCommand("Contacts_Delete", connection)

6 command.CommandType = CommandType.StoredProcedure

7 command.Parameters.AddWithValue("@ContactID", CType(dgContacts.Item(dgContacts.CurrentRowIndex, 0), Int32))

9 'Open the connection to the database and execute

10 'the SQL Statement

11 connection.Open()

12 command.ExecuteNonQuery()

13 connection.Close()

15 'Update the DataGrid to show new changes

16 '(if applicable)

17 RetrieveContacts()

19 Else

21 MessageBox.Show("Please select a row for deleting.", "No Row Selected", MessageBoxButtons.OK, MessageBoxIcon.Information)

23 End If

The main points of interest in this routine are lines 4 - 7. The remainder work as before.

On line 4 we create a new command object and specify the name of the stored procedure to run and which connection object to use.

Line 6 notifies the command object that the CommandText (notably Contacts_Delete) refers to a stored procedure and not an SQL string.

Now for the important parameter; the Command object has a parameters collection which has methods for adding and removing parameters from this collection.

Line 7 uses the AddWithValue method to add a new parameter to the collection and the syntax specifies the name of the parameter and the value that you want to assign to that parameter. As you can see above we have stated that the parameter’s name is @ContactID and the value is the value of the first column of the selected row in the DataGrid.

There are other methods for adding a parameter to the Parameters collection, such as specifying the name, data type and value as follows:

1 command.Parameters.Add("@ContactID", SqlDbType.Int).Value = CType(dgContacts.Item(dgContacts.CurrentRowIndex, 0), Int32)

But I find the AddWithValue method easier to work with, especially if you find that at a later date you change the definition of a column (data type or size). With the AddWithValue method you will not need to worry about modifying any parameter code.

If we now move onto the ManageData form we can modify the last two routines to make use of the stored procedures that Add and Update data in the Contacts table. The first routine we will change is the btnSave_Click routine. First, replace the existing code with the code below:

1 Dim conn As New SqlConnection("Data Source=(Local);Initial Catalog=AddressBook;Integrated Security=SSPI")

2 Dim command As New SqlCommand()

4 command.Connection = conn

6 command.Parameters.AddWithValue("@Title", txtTitle.Text)

7 command.Parameters.AddWithValue("@FirstName", txtFirstName.Text)

8 command.Parameters.AddWithValue("@LastName", txtLastName.Text)

10 'Determine if we are adding a new record or editing an

11 'existing record based on the value of the _contactID

12 'variable

13 If _contactID = 0 Then

14 command.CommandText = "Contacts_Insert"

15 Else

16 command.CommandText = "Contacts_Update"

17 command.Parameters.AddWithValue("@ContactID", _contactID)

18 End If

20 command.CommandType = CommandType.StoredProcedure

22 'Open the connection to the database and execute the

23 'SQL Statement

24 conn.Open()

26 'Dim command As New SqlCommand(sql, conn)

27 command.ExecuteNonQuery()

29 conn.Close()

31 'Close the form.

32 Me.Close()

The code above works similarly to the previous version we used. However, instead of creating two different SQL Statements based on whether we were updating an existing record or creating a new one, this code simply calls a different procedure. You will also note that we add the common parameters to the Command’s Parameter collection in lines 6 – 8 and only if we are updating an existing record do we add a further parameter on line 17.

The final routine to replace is the RetrieveContactDetails routine:

1 'Retrieves a record from the Contacts table based

2 'on the _contactID value.

3 Dim conn As New SqlConnection("Data Source=(Local);Initial Catalog=AddressBook;Integrated Security=SSPI")

5 Dim command As New SqlCommand("Contacts_Contact_Select", conn)

7 command.CommandType = CommandType.StoredProcedure

8 command.Parameters.AddWithValue("@ContactID", _contactID)

10 'Open the connection to the database

11 conn.Open()

13 'Use the Command object's ExecuteReader method which

14 'will return an SqlDataReader object that we can use

15 'for forward only access through the returned data.

16 Dim contactReader As SqlDataReader = command.ExecuteReader

18 'There should only be one row returned due to the WHERE

19 'clause retrieving a specific ContactID. So initiate the Read

20 'method of the contactReader and read the field values:

21 contactReader.Read()

22 txtTitle.Text = contactReader("Title").ToString

23 txtFirstName.Text = contactReader("FirstName").ToString

24 txtLastName.Text = contactReader("LastName").ToString

26 'Close the connection to the database

27 conn.Close()

Again, this routine is very similar to the previous routine, the only real changes to this routine are found on lines 5 – 8 where the command is created and the @ContactID parameter is added to the parameters collection of the command object.

At this point, the program should now work exactly as it did before but now we are targeting a Microsoft SQL Server database and using Stored Procedures to manage the data in the Contacts table.