Export (0) Print
Expand All

Working 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.
            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 DataTable();
            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);
    }
}

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.
            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: {0}",
                adapter.InsertCommand.CommandText);
            Console.WriteLine("UpdateCommand: {0}",
                adapter.UpdateCommand.CommandText);
            Console.WriteLine("DeleteCommand: {0}",
                adapter.DeleteCommand.CommandText);

            // Fill the DataTable.
            DataTable table = new DataTable();
            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("{0} {1}", 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("{0} {1}", 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("{0} {1}", row[0], row[1]);
            }
            Console.WriteLine("----After Delete-----");
            Console.WriteLine("Customer XYZZZ was deleted.");
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Handling Parameters

.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.

NoteNote

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

Community Additions

ADD
Show:
© 2014 Microsoft