Modificando dados com um DbDataAdapter

O método CreateDataAdapter de um objeto DbProviderFactory fornece um objeto DbDataAdapter fortemente tipado para o provedor de dados subjacente especificado no momento em que você cria a fábrica. Em seguida, você pode usar um DbCommandBuilder para criar comandos para inserir, atualizar e excluir dados de uma DataSet fonte de dados.

Recuperando dados com um DbDataAdapter

Este exemplo demonstra como criar um DbDataAdapter fortemente tipado com base no nome do provedor e na cadeia de conexão. O código usa o CreateConnection método do DbProviderFactory para criar um DbConnection. Em seguida, o código usa o método CreateCommand para criar um DbCommand para selecionar dados definindo suas propriedades CommandText e Connection. Por fim, o código cria um DbDataAdapter objeto usando o CreateDataAdapter método e define sua SelectCommand propriedade. O Fill método do DbDataAdapter carrega os dados em um 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
        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

Modificando dados com um DbDataAdapter

Este exemplo demonstra como modificar dados em um DataTable usando um DbDataAdapter usando um DbCommandBuilder para gerar os comandos necessários para atualizar dados na fonte de dados. O SelectCommand do DbDataAdapter está definido para recuperar o CustomerID e o CompanyName da tabela Customers. O GetInsertCommand método é usado para definir a InsertCommand propriedade, o GetUpdateCommand método é usado para definir a UpdateCommand propriedade e o GetDeleteCommand método é usado para definir a DeleteCommand propriedade. O código adiciona uma nova linha à tabela Clientes e atualiza a fonte de dados. Em seguida, o código localiza a linha adicionada pesquisando na CustomerID, que é a chave primária definida para a tabela Customers. Ele altera o CompanyName e atualiza a fonte de dados. Por fim, o código exclui a linha.

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

Manipulando parâmetros

Os provedores de dados do .NET Framework manipulam a nomenclatura e a especificação de parâmetros e espaços reservados de parâmetros de forma diferente. Essa sintaxe é adaptada a uma fonte de dados específica, conforme descrito na tabela a seguir.

O modelo de fábrica não é útil para criar objetos parametrizados DbCommand e DbDataAdapter. Você precisará ramificar seu código para criar parâmetros personalizados para seu provedor de dados.

Consulte também

• DbProviderFactories
• Obter um DbProviderFactory
• DbConnection, DbCommand e DbException
• Visão geral do ADO.NET