DbDataAdapter를 사용하여 데이터 수정

CreateDataAdapter 개체의 DbProviderFactory 메서드는 팩터리를 만들 때 지정된 기본 데이터 제공자에 강력하게 형식화된 DbDataAdapter 개체를 제공합니다. 그런 다음 DbCommandBuilder 사용하여 DataSet 데이터 원본으로 데이터를 삽입, 업데이트 및 삭제하는 명령을 만들 수 있습니다.

DbDataAdapter를 사용하여 데이터 검색

이 예제에서는 공급자 이름 및 연결 문자열을 기반으로 강력한 형식의 DbDataAdapter 만드는 방법을 보여 줍니다. 이 코드는 CreateConnection의 DbProviderFactory 메서드를 사용하여 DbConnection를 만듭니다. 다음으로, 코드는 CreateCommand 메서드를 사용하여 DbCommand 및 CommandText 속성을 설정하여 데이터를 선택하는 Connection 만듭니다. 마지막으로 코드는 DbDataAdapter 메서드를 사용하여 CreateDataAdapter 개체를 만들고 해당 SelectCommand 속성을 설정합니다. Fill DbDataAdapter 메서드는 데이터를 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

DbDataAdapter를 사용하여 데이터 수정

이 예제에서는 DataTable 사용하여 데이터 원본에서 데이터를 업데이트하는 데 필요한 명령을 생성하여 DbDataAdapter 사용하여 DbCommandBuilder 데이터를 수정하는 방법을 보여 줍니다. SelectCommand의 DbDataAdapter은 Customers 테이블에서 CustomerID와 CompanyName을 검색하도록 설정됩니다. GetInsertCommand 메서드는 InsertCommand 속성을 설정하는 데 사용되고, GetUpdateCommand 메서드를 사용하여 UpdateCommand 속성을 설정하고, GetDeleteCommand 메서드를 사용하여 DeleteCommand 속성을 설정합니다. 이 코드는 Customers 테이블에 새 행을 추가하고 데이터 원본을 업데이트합니다. 그런 다음, 코드는 Customers 테이블에 대해 정의된 기본 키인 CustomerID를 검색하여 추가된 행을 찾습니다. CompanyName을 변경하고 데이터 원본을 업데이트합니다. 마지막으로 코드는 행을 삭제합니다.

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

매개 변수 처리

.NET Framework 데이터 공급자는 매개 변수 및 매개 변수 자리 표시자를 다르게 명명하고 지정하는 것을 처리합니다. 이 구문은 다음 표에 설명된 대로 특정 데이터 원본에 맞게 조정됩니다.

팩터리 모델은 매개 변수가 있는 DbCommand 및 DbDataAdapter 개체를 만드는 데 유용하지 않습니다. 데이터 공급자에 맞게 조정된 매개 변수를 만들려면 코드에서 분기해야 합니다.

참고하십시오

• DbProviderFactories
• DbProviderFactory 가져오기
• DbConnection, DbCommand 및 DbException
• ADO.NET 개요