Modifying Data with a DbDataAdapter

 

The CreateDataAdapter method of a DbProviderFactory object gives you a DbDataAdapter object that is strongly typed to the underlying data provider specified at the time you create the factory. You can then use a DbCommandBuilder to create commands to insert, update, and delete data from a DataSet to a data source.

This example demonstrates how to create a strongly typed DbDataAdapter based on a provider name and connection string. The code uses the CreateConnection method of the DbProviderFactory to create a DbConnection. Next, the code uses the CreateCommand method to create a DbCommand to select data by setting its CommandText and Connection properties. Finally, the code creates a DbDataAdapter object using the CreateDataAdapter method and sets its SelectCommand property. The Fill method of the DbDataAdapter loads the data into a DataTable.

Shared Sub CreateDataAdapter(ByVal providerName As String, _
    ByVal connectionString As String)

    ' Create the DbProviderFactory and DbConnection.
    Try
        Dim factory As DbProviderFactory = _
           DbProviderFactories.GetFactory(providerName)

        Dim connection As DbConnection = _
            factory.CreateConnection()
        connection.ConnectionString = connectionString
        Using connection

            ' Define the query.
            Dim queryString As String = _
              "SELECT CategoryName FROM Categories"

            'Create the DbCommand.
            Dim command As DbCommand = _
                factory.CreateCommand()
            command.CommandText = queryString
            command.Connection = connection

            ' Create the DbDataAdapter.
            Dim adapter As DbDataAdapter = _
                factory.CreateDataAdapter()
            adapter.SelectCommand = command

            ' Fill the DataTable
            Dim table As New DataTable
            adapter.Fill(table)

            'Display each row and column value.
            Dim row As DataRow
            Dim column As DataColumn
            For Each row In table.Rows
                For Each column In table.Columns
                    Console.WriteLine(row(column))
                Next
            Next
        End Using

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
End Sub

This example demonstrates how to modify data in a DataTable using a DbDataAdapter by using a DbCommandBuilder to generate the commands required for updating data at the data source. The SelectCommand of the DbDataAdapter is set to retrieve the CustomerID and CompanyName from the Customers table. The GetInsertCommand method is used to set the InsertCommand property, the GetUpdateCommand method is used to set the UpdateCommand property, and the GetDeleteCommand method is used to set the DeleteCommand property. The code adds a new row to the Customers table and updates the data source. The code then locates the added row by searching on the CustomerID, which is the primary key defined for the Customers table. It changes the CompanyName and updates the data source. Finally, the code deletes the row.

Shared Sub CreateDataAdapter(ByVal providerName As String, _
    ByVal connectionString As String)

    ' Create the DbProviderFactory and DbConnection.
    Try
        Dim factory As DbProviderFactory = _
           DbProviderFactories.GetFactory(providerName)

        Dim connection As DbConnection = _
            factory.CreateConnection()
        connection.ConnectionString = connectionString

        Using connection
            ' Define the query.
            Dim queryString As String = _
              "SELECT CustomerID, CompanyName FROM Customers"

            'Create the select command.
            Dim command As DbCommand = _
                factory.CreateCommand()
            command.CommandText = queryString
            command.Connection = connection

            ' Create the DbDataAdapter.
            Dim adapter As DbDataAdapter = _
                factory.CreateDataAdapter()
            adapter.SelectCommand = command

            ' Create the DbCommandBuilder.
            Dim builder As DbCommandBuilder = _
              factory.CreateCommandBuilder()
            builder.DataAdapter = adapter

            ' Get the insert, update and delete commands.
            adapter.InsertCommand = builder.GetInsertCommand()
            adapter.UpdateCommand = builder.GetUpdateCommand()
            adapter.DeleteCommand = builder.GetDeleteCommand()

            ' Display the CommandText for each command.
            Console.WriteLine("InsertCommand: {0}", _
              adapter.InsertCommand.CommandText)
            Console.WriteLine("UpdateCommand: {0}", _
              adapter.UpdateCommand.CommandText)
            Console.WriteLine("DeleteCommand: {0}", _
              adapter.DeleteCommand.CommandText)

            ' Fill the DataTable
            Dim table As New DataTable
            adapter.Fill(table)

            ' Insert a new row.
            Dim newRow As DataRow = table.NewRow
            newRow("CustomerID") = "XYZZZ"
            newRow("CompanyName") = "XYZ Company"
            table.Rows.Add(newRow)

            adapter.Update(table)

            ' Display rows after insert.
            Console.WriteLine()
            Console.WriteLine("----List All Rows-----")
            Dim row As DataRow
            For Each row In table.Rows
                Console.WriteLine("{0} {1}", row(0), row(1))
            Next
            Console.WriteLine("----After Insert-----")

            ' Edit an existing row.
            Dim editRow() As DataRow = _
              table.Select("CustomerID = 'XYZZZ'")
            editRow(0)("CompanyName") = "XYZ Corporation"

            adapter.Update(table)

            ' Display rows after update.
            Console.WriteLine()
            For Each row In table.Rows
                Console.WriteLine("{0} {1}", row(0), row(1))
            Next
            Console.WriteLine("----After Update-----")

            ' Delete a row.
            Dim deleteRow() As DataRow = _
              table.Select("CustomerID = 'XYZZZ'")
            For Each row In deleteRow
                row.Delete()
            Next

            adapter.Update(table)
            table.AcceptChanges()

            ' Display each row and column value after delete.
            Console.WriteLine()
            For Each row In table.Rows
                Console.WriteLine("{0} {1}", row(0), row(1))
            Next
            Console.WriteLine("----After Delete-----")
            Console.WriteLine("Customer XYZZZ was deleted.")
        End Using

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try
End Sub

The .NET Framework data providers handle naming and specifying parameters and parameter placeholders differently. This syntax is tailored to a specific data source, as described in the following table.

Data provider

Parameter naming syntax

SqlClient

Uses named parameters in the format @parametername.

OracleClient

Uses named parameters in the format :parmname (or parmname).

OleDb

Uses positional parameter markers indicated by a question mark (?).

Odbc

Uses positional parameter markers indicated by a question mark (?).

The factory model is not helpful for creating parameterized DbCommand and DbDataAdapter objects. You will need to branch in your code to create parameters that are tailored to your data provider.

System_CAPS_security Security Note

Avoiding provider-specific parameters altogether by using string concatenation to construct direct SQL statements is not recommended for security reasons. Using string concatenation instead of parameters leaves your application vulnerable to SQL injection attacks.

Show: