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.

Retrieving Data with a DbDataAdapter

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.

static void CreateDataAdapter(string providerName, string connectionString)
{
    try
    {
        // Create the DbProviderFactory and DbConnection.
        DbProviderFactory factory =
            DbProviderFactories.GetFactory(providerName);

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        using (connection)
        {
            // Define the query.
            const string queryString =
                "SELECT CategoryName FROM Categories";

            // Create the DbCommand.
            DbCommand command = factory.CreateCommand();
            command.CommandText = queryString;
            command.Connection = connection;

            // Create the DbDataAdapter.
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = command;

            // Fill the DataTable.
            DataTable table = new();
            adapter.Fill(table);

            //  Display each row and column value.
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn column in table.Columns)
                {
                    Console.WriteLine(row[column]);
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

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

        ' Create the DbProviderFactory and DbConnection.
        Try
#If NET Then
            DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)
#End If
            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

Modifying Data with a DbDataAdapter

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.

static void CreateDataAdapter(string providerName, string connectionString)
{
    try
    {
        // Create the DbProviderFactory and DbConnection.
        DbProviderFactory factory =
            DbProviderFactories.GetFactory(providerName);

        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        using (connection)
        {
            // Define the query.
            const string queryString =
                "SELECT CustomerID, CompanyName FROM Customers";

            // Create the select command.
            DbCommand command = factory.CreateCommand();
            command.CommandText = queryString;
            command.Connection = connection;

            // Create the DbDataAdapter.
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = command;

            // Create the DbCommandBuilder.
            DbCommandBuilder builder = 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: {adapter.InsertCommand.CommandText}");
            Console.WriteLine($"UpdateCommand: {adapter.UpdateCommand.CommandText}");
            Console.WriteLine($"DeleteCommand: {adapter.DeleteCommand.CommandText}");

            // Fill the DataTable.
            DataTable table = new();
            adapter.Fill(table);

            // Insert a new row.
            DataRow newRow = 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-----");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine($"{row[0]} {row[1]}");
            }
            Console.WriteLine("----After Insert-----");

            // Edit an existing row.
            DataRow[] editRow = table.Select("CustomerID = 'XYZZZ'");
            editRow[0]["CompanyName"] = "XYZ Corporation";

            adapter.Update(table);

            // Display rows after update.
            Console.WriteLine();
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine($"{row[0]} {row[1]}");
            }
            Console.WriteLine("----After Update-----");

            // Delete a row.
            DataRow[] deleteRow = table.Select("CustomerID = 'XYZZZ'");
            foreach (DataRow row in deleteRow)
            {
                row.Delete();
            }

            adapter.Update(table);

            // Display rows after delete.
            Console.WriteLine();
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine($"{row[0]} {row[1]}");
            }
            Console.WriteLine("----After Delete-----");
            Console.WriteLine("Customer XYZZZ was deleted.");
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

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

        ' Create the DbProviderFactory and DbConnection.
        Try
#If NET Then
            DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)
#End If
            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

Handling Parameters

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.

Important

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.

See also

• DbProviderFactories
• Obtaining a DbProviderFactory
• DbConnection, DbCommand and DbException
• ADO.NET Overview