Bagikan melalui

Memodifikasi Data dengan DbDataAdapter

  • Artikel

Metode CreateDataAdapter dari objek DbProviderFactory memberi Anda objek DbDataAdapter yang sangat digolongkan ke penyedia data yang mendasar yang ditentukan pada saat Anda membuat pabrik. Anda kemudian bisa menggunakan DbCommandBuilder untuk membuat perintah untuk menyisipkan, memperbarui, dan menghapus data dari DataSet ke sumber data.

Mengambil Data dengan DbDataAdapter

Contoh ini menunjukkan cara membuat yang diketik DbDataAdapter dengan kuat berdasarkan nama penyedia dan string koneksi. Kode menggunakan CreateConnection metode DbProviderFactory untuk membuat DbConnection. Selanjutnya, kode menggunakan metode CreateCommand untuk membuat DbCommand untuk memilih data dengan mengatur properti CommandText dan Connection. Akhirnya, kode membuat objek DbDataAdapter menggunakan metode CreateDataAdapter dan mengatur properti SelectCommand. Metode Fill memuat DbDataAdapter data ke dalam 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

Memodifikasi Data dengan DbDataAdapter

Contoh ini menunjukkan cara memodifikasi data dalam DataTable menggunakan DbDataAdapter dengan menggunakan untuk menghasilkan perintah yang DbCommandBuilder diperlukan untuk memperbarui data di sumber data. SelectCommand dari DbDataAdapter diatur untuk mengambil CustomerID dan CompanyName dari tabel Pelanggan. Metode GetInsertCommand ini digunakan untuk mengatur properti InsertCommand, metode GetUpdateCommand digunakan untuk mengatur properti UpdateCommand, dan metode GetDeleteCommand digunakan untuk mengatur properti DeleteCommand. Kode menambahkan baris baru ke tabel Pelanggan dan memperbarui sumber data. Kode kemudian menempatkan baris yang ditambahkan dengan mencari di CustomerID, yang merupakan kunci utama yang ditentukan untuk tabel Pelanggan. Ini mengubah CompanyName dan memperbarui sumber data. Akhirnya, kode menghapus baris.

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

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

Parameter Penanganan

Penyedia data .NET Framework menangani penamaan dan menentukan parameter dan tempat penampung parameter secara berbeda. Sintaks ini disesuaikan dengan sumber data tertentu, seperti yang dijelaskan dalam tabel berikut.

Penyedia data Sintaks penamaan parameter
SqlClient Menggunakan parameter bernama dalam format @parametername.
OracleClient Menggunakan parameter bernama dalam format :parmname (atau parmname).
OleDb Menggunakan penanda parameter posisi yang ditunjukkan oleh tanda tanya (?).
Odbc Menggunakan penanda parameter posisi yang ditunjukkan oleh tanda tanya (?).

Model pabrik tidak membantu untuk membuat parameter DbCommand dan DbDataAdapter objek. Anda harus bercabang dalam kode Anda untuk membuat parameter yang disesuaikan dengan penyedia data Anda.

Penting

Menghindari parameter khusus penyedia sama sekali dengan menggunakan penggabungan string untuk membangun pernyataan SQL langsung tidak disarankan untuk alasan keamanan. Menggunakan penggabungan string alih-alih parameter membuat aplikasi Anda rentan terhadap serangan injeksi SQL.

Lihat juga