SoFunction
Updated on 2025-03-07

C# DataAdapter

1. Fill in data

DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("select * from Catogories;select * from Customers", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);//da can be used multiple times in differentDataTableMapping map = ("Table", "Customer");//The parameters are, tables in the data source and tables in the DataSet("Name", "CustomerName");
//= ;
 = ;// Used to handle mode conflicts, default is Add.(ds);//The newly added DataTable table name defaults to "Table", and the newly added table name defaults to Table1, Table2, etc.(ds, "Customer");
(ds, 0, 10000, "Customer");// Used for paging fill(ds, , "Customer");//Just fill mode information

If the connection is not opened, it will be opened automatically and will be closed automatically after Fill. If you still need to use this connection, you need to open it again. If conn is manually turned on, you need to manually close the connection after Fill.

2. Insert multiple DataTables into Tables collection

1. Use multiple DataAdapters to fill

SqlCommand cmd = new SqlCommand("select * from Catogories;s", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
(ds, "Catogories");

 = "select * from Customers";
SqlDataAdapter da2 = new SqlDataAdapter(cmd);
(ds, "Customer");

2. Use the same DataAdapter and fill it with different SelectCommandTexts

SqlCommand cmd = new SqlCommand("select * from Catogories;s", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
(ds, "Catogories")

 = "select * from Customers";
(ds, "Customer");

3. Return SQL for multiple result sets (recommended)

SqlCommand cmd = new SqlCommand("select * from Catogories;select * from Customers", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
(ds);

3. Update data using DataAdapter

DataSet dsChanged = ();
if (dsChanged != null)
{
    SqlCommandBuilder builder=new SqlCommandBuilder(da);
    //Automatically generate InsertCommand, UpdateCommand, DeleteCommand commands, assuming: The Select statement is a single table, and this statement contains a primary key or a unique column.    (ds,"Author");//Update method detects each record in the DataSet. If the row state is not Unchanaged, call different SQL statements according to its row state.    ();
}
  • Method: Commit all changes made to the table since the last call to AcceptChanges.
    All Added and Modified lines become Unchanged; Deleted lines are removed.
  • Method: Roll back all changes made to the table since the table is loaded or the last time AcceptChanges was called.
    Added was removed. The row with DataRowState Modified or Deleted returns to its initial state.

4. Use SQL

OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand("insert into  [A_Emp_Dept_20190522](empname,line) values (?,?)", conn);
(new OleDbParameter("empname", , 11, , false, 0, 0, "empname", , null));
(new OleDbParameter("line", , 11, , false, 0, 0, "line", , null));
 = cmd;
//= ;
DataSet ds = new DataSet();
DataRow row = [0].NewRow();
row["empname"] = "222";
row["line"] = "FBd";
[0].(row);
(ds);

UpdatedRowSource

  • Both: Both the output parameter and the first row of the returned result set can be mapped to the changed row in the DataSet.
    FirstReturnedRecord: Only data in the first row of the returned result set can be mapped to changed rows in the DataSet.
    None: Ignore any output parameters or rows in the returned result set.
    OutputParameters: Only output parameters can be mapped to changed rows in DataSet

5. DataAdapter Event

  • RowUpdating: Occurs during Update(DataSet) before executing commands to the data source.
  • RowUpdated: Occurs during Update(DataSet) after executing commands to the data source.

SqlRowUpdatedEventArgs class attributes

  • Command: Gets or sets the Update(DataSet) executed when SqlCommand is called.
  • Errors: Gets any errors generated by the .NET Framework data provider when Command is executed.
  • RecordsAffected: Gets the number of changed, inserted, or deleted rows by executing SQL statements.
  • Row: Gets the DataRow sent through Update(DataSet).
  • RowCount: Gets the number of rows processed in a batch of update records.
  • StatementType: Gets the type of SQL statement executed.
  • Status: Gets the UpdateStatus of the Command property.
  • TableMapping: Gets the DataTableMapping sent through Update(DataSet).

This is all about this article about C# data adapter DataAdapter. I hope it will be helpful to everyone's learning and I hope everyone will support me more.