Introducing the .NET SQL Server Data Provider

The following code (also accessed from \Ch06\Sample04.vb in the sample files) uses SQL Server security to test the connection.

Sub ConnectSQLSecurity()
    Dim con As New SqlClient.SqlConnection("Data Source=(local);" + _
        "Initial Catalog=AdventureWorks;User ID=sa;Password=P@assword")
    Try
        con.Open()
        MsgBox("Connection Successful")
        con.Close()
    Catch ex As Exception
        MsgBox("Connection Failed")
    End Try
End Sub

Connection strings in ADO.NET are similar to ADO connections. However, in a SqlConnection , it is not necessary to set the database driver because the object model already knows that it is going to access a SQL Server database.

Using the SqlCommand Class

The SqlCommand class is used to represent Transact-SQL (T-SQL) statements to execute in the database. A SqlCommand can be used to execute stored procedures or T-SQL statements, or it can be used to access a table directly. Use the CommandType property to specify which type of statement the object represents.

The SqlCommand class provides three main methods:

• ExecuteNonQuery Executes the command and returns the number of rows affected by the statement.

• ExecuteScalar Executes the command and returns a single value.

• ExecuteReader Builds a SqlDataReader that can be used to read a set of records. (This method is discussed in the next section.)

  Note	SqlCommand also provides the ExecuteXmlReader method (which returns an XmlReader object) that should be used when including the FOR XML clause in the SELECT statement.

The following code (accessed as \Ch06\Sample05.VB in the sample files) reads the name of the department with ID=3 in the database by using the ExecuteScalar method.

Sub DoExecuteScalar()
    Dim con As New SqlClient.SqlConnection( _
        "Server=(local);Database=AdventureWorks;Trusted_Connection=yes")
    Dim Com As New SqlClient.SqlCommand( _
        "SELECT Name FROM HumanResources.Department WHERE DepartmentID=3", con)
    Dim Name As String = ""
    Try
        con.Open()
        Name = Com.ExecuteScalar.ToString
        MsgBox(String.Format("The name is {0}", Name))
        con.Close()
    Catch ex As Exception
        MsgBox("Connection Failed")
    End Try
End Sub

Using the SqlDataReader Class

The SqlDataReader class provides reading access to a forward-only set of streamed rows. This class is used to read rows from a command into the application. The SqlDataReader object is created through the SqlCommand.ExecuteReader method. The SqlDataReader class contains the Read method to obtain the next record, as well as many Get methods to read different types of columns in the row. The Read method can be run multiple times and will return false after the last row has been read. All of the Get methods support an integer parameter to specify the column number in the record.

The following code (accessed as \Ch06\Sample06.vb in the sample files) reads departments from the AdventureWorks database by using a SqlDataReader .

Sub DoExecuteReader()
    Dim con As New SqlClient.SqlConnection( _
        "Server=(local);Database=AdventureWorks;Trusted_Connection=yes")
    Dim Com As New SqlClient.SqlCommand( _
        "SELECT DepartmentID, Name FROM HumanResources.Department", con)
    Dim Name As String = ""
    Dim DepartmentId As Integer = 0
    Dim dr As SqlClient.SqlDataReader
    Try
        con.Open()
        dr = Com.ExecuteReader
        Do While dr.Read
            DepartmentId = CInt(dr(0))
            Name = dr(1).ToString
            MsgBox(String.Format("{0}:{1}", DepartmentId, Name))
        Loop
        dr.Close()
        con.Close()
    Catch ex As Exception
        MsgBox("Connection Failed")
    End Try
End Sub

Using the SqlDataAdapter Class

The SqlDataAdapter class is used to fill a dataset and includes a set of data commands with which to select and update the database. SqlDataAdapter has four commands responsible for each of the operations in the database: SELECT, INSERT, DELETE, and UPDATE. The SqlDataAdapter class hides the complexity of SqlDataReaders and offers a simpler object model with which to create data-centric applications.

To create a SqlDataAdapter graphically by using the SqlDataAdapter control in Visual Studio 2005, complete the following steps.

Creating a SqlDataAdapter Graphically

1. From the Start menu, choose All Programs Microsoft Visual Studio 2005 Microsoft Visual Studio 2005 to open Visual Studio 2005.

2. From the File menu, choose New Project.

3. In the Project Types section, select Visual Basic and then choose the Windows Application template.

   

4. Name the project ADONETTest and click OK to create the project.

5. If the Toolbox is not visible, select Toolbox from the View menu.

6. Right-click the Toolbox and select Choose Items from the context menu.

7. On the .NET Framework Components tab of the Choose Toolbox Items window, select the following components : SqlCommand , SqlConnection , and SqlDataAdapter . The SqlDataReader class does not have a graphical designer. Click OK to add the components.

8. From the Toolbox, drag a DataGridView control onto the form. Right-click it and select Properties from the context menu.

9. In the Properties window, locate the (Name) property and change it to dgDepartments . Locate the Dock property and select Left (by clicking the left bar in the selector window when you expand the property value).

10. From the Toolbox, drag a button onto the form, and then drag a second button onto the form.

11. As you did previously with the grid, locate the (Name) property for one of the buttons . Change the name to btnRead and then change the other buttons name to btnUpdate .

12. Change the Text property of each button to Read and Update to match their names .

    

13. Add a SqlDataAdapter control (from the DataSet group of the Toolbox) to the window. The Data Adapter Configuration Wizard is displayed.

14. Select the connection to use. If this is the first time that you have accessed SQL Server 2005 from Visual Studio, click the New Connection button. Configure the server name, authentication method, and database in the Add Connection window.

15. Click Next to continue.

16. In the Choose A Command Type screen of the Data Adapter Configuration Wizard, make sure that the Use SQL Statements option is selected. In Chapter 9, Retrieving Data Using Programmable Objects, you will learn about stored procedures.

    

17. In the Generate The SQL Statements screen of the wizard, type the following command. You can access this code from \Ch06\Sample07.sql in the sample files.

    SELECT     DepartmentID, Name, GroupName
    FROM       HumanResources.Department
    

18. Click Next to continue. Review how the SqlDataAdapter will be configured, including the four commands: SELECT, INSERT, UPDATE, and DELETE. Click Finish to configure the SqlDataAdapter .

19. Double-click the Read button and write the following code (included in the sample files as \Ch06\Sample08.vb).

    .    Private Sub btnRead_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles btnRead.Click
            Dim ds As New DataSet
            SqlDataAdapter1.Fill(ds)
            With dgDepartments
                .DataSource = ds
                .DataMember = "Department"
            End With
        End Sub
    

20. Select the Form1.cs [Design] tab or click the Form1.cs node in Solution Explorer to return to the Form1 design window. Double-click the btnUpdate button.

21. Write the following code (included in the sample files as \Ch06\Sample09.vb).

    Private Sub btnUpdate_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim ds As DataSet = CType(dgDepartments.DataSource, DataSet)
        SqlDataAdapter1.Update(ds)
    End Sub
    

22. Press F5 to save and run the application. Click the Read button to fill the DataGridView. Make modifications, and then click the Update button to save the information in the database.

    Important

    Do not delete the first rows from the Departments table (DepartmentID 1 to 16) because they are referenced by the Employee table. If you want to test how the DELETE command works, add a row, update the database, and then delete the row later.

Using the TableAdapter Class

ADO.NET 2.0 introduces a new class called TableAdapter . TableAdapter substitutes the functionality of SqlDataAdapter by providing built-in communications between the application and the database. TableAdapters may contain any number of T-SQL statements or stored procedures that return filled data tables or update data from the data table back to the database.

Creating a TableAdapter

1. From the Start menu, choose All Programs Microsoft Visual Studio 2005 Microsoft Visual Studio 2005.

2. From the File menu, select New Project.

3. In the Project Types section, select Visual Basic and choose the Windows Application template.

4. Name the project ADONETTest2 and click OK to create the project.

5. If the Toolbox is not visible, choose Toolbox from the View menu.

6. Add a DataGridView and one button to the form. Name the DataGridView dgDepartments , and name the button btnUpdate . Change the text of btnUpdate to Update.

7. Select the dgDepartments data grid view, and click the DataGridView Tasks button (small right-arrow button in the upper-right corner of the data grid view).

   

8. Click the down arrow on the Choose Data Source drop-down listbox and select Add Project Data Source at the bottom.

9. In the Datasource Configuration Wizard, select the Database icon and click Next to continue.

10. Select the AdventureWorks connection that you created in the previous procedure and click Next to continue.

11. In the Save The Connection String To The Application Configuration File screen of the wizard, click Next to continue.

12. In the Choose Your Database Objects screen of the wizard, expand the Tables node. Expand the Department table, and select the DepartmentID, Name, and GroupName columns by putting a check in the checkboxes.

    

13. Click Finish to configure the data source.

14. Notice that the wizard created a dataset in the project (visible in Solution Explorer) and three database objects in the tray below the form: AdventureWorksDataset, departmentBindingSource, and departmentTableAdapter.

15. Double-click the Update button and write the following code:

    private void btnUpdate_Click(object sender, EventArgs e)
    {
        this.departmentTableAdapter.Update(this.adventureWorksDataSet.Department);
    }
    

16. Save and run the application. The data grid is automatically filled with data when the window is displayed. Make modifications, and then click the Update button to save the information back to the database.