Introduction

Code shown below is in project DataSet\DtAdapter.

• Data adapters move data between data sources and DataSets.
• Each framework provider gives its own.
  • OleDbDataAdapter
  • SqlDataAdapter,
  • etc.
• Base classes : DataAdapter, DbDataAdapter
• Interfaces: IDbDataAdapter, IDataAdapter

            IDataAdpter
            /          \
           /            \
  IDbDataAdapter      DataAdapter
           \            /
            \          /
            DbDataAdapter
             /    |    \
  SqlDataAdapter  ...  OleDbDataAdpater

IDataAdapter provides methods and properties relating to filling DataSets and mapping tables and columns (more detail later).

IDataAdapter
Properties	Description
MissingMappingAction	Indicates or specifies whether unmapped source tables or columns are passed with their source names in order to be filtered or to raise an error.
MissingSchemaAction	Indicates or specifies whether missing source tables, columns, and their relationships are added to the data set schema, ignored, or cause an error to be raised.
TableMappings	Indicates how a source table is mapped to a data set table.
Method	Description
Fill	Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table".
FillSchema	Adds a DataTable named "Table" to the specified DataSet and configures the schema to match that in the data source based on the specified SchemaType.
GetFillParameters	Gets the parameters set by the user when executing an SQL SELECT statement.
Update	Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table".

Interface IDbDataAdapter inherits from IDataAdapter; it adds command-related properties.

IDbDataAdapter
Public Properties	Description
DeleteCommand	Gets or sets an SQL statement for deleting records from the data set.
InsertCommand	Gets or sets an SQL statement used to insert new records into the data source.
SelectCommand	Gets or sets an SQL statement used to select records in the data source.
UpdateCommand	Gets or sets an SQL statement used to update records in the data source.

Base class DataAdapter implements interface IDataAdapter.

DataAdapter
Properties	Description
AcceptChangesDuringFill	Gets or sets a value indicating whether AcceptChanges is called on a DataRow after it is added to the DataTable during any of the Fill operations.
AcceptChangesDuringUpdate	Gets or sets whether AcceptChanges is called during a Update.
ContinueUpdateOnError	Gets or sets a value that specifies whether to generate an exception when an error is encountered during a row update.
FillLoadOption	Gets or sets the LoadOption that determines how the adapter fills the DataTable from the DbDataReader.
ReturnProviderSpecificTypes	Gets or sets whether the Fill method should return provider-specific values or common CLS-compliant values.
Method	Description
ResetFillLoadOption	Resets FillLoadOption to its default state and causes Fill to honor AcceptChangesDuringFill.
ShouldSerializeAcceptChangesDuringFill	Determines whether the AcceptChangesDuringFill property should be persisted.
ShouldSerializeFillLoadOption	Determines whether the FillLoadOption property should be persisted.
Event	Description
FillError	Returned when an error occurs during a fill operation.

DbDataAdapter inherits from base class DataAdapter and implements interface IDbDataAdapter; it adds mostly protected properties useful for implementers. This is the base class for the provider-specific data adapters.

DbDataAdapter
Public Properties	Description
UpdateBatchSize	Gets or sets a value that enables or disables batch processing support, and specifies the number of commands that can be executed in a batch.

 

Filling a DataSet

• The SelectCommand value is used by the Fill method to pull data to fill a table in a dataset.
• The Fill method will fill in the schema for the table if it does not exist, or will add columns if necessary.
• Primary keys are not created unless they exist in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey. Other values for this property:
  

MissingSchemaAction value	Description
Add	Adds the necessary columns to complete the schema.
AddWithKey	Adds the necessary columns and primary key information to complete the schema. For more information about how primary key information is added to a DataTable, see FillSchema.To function properly with the .NET Framework Data Provider for OLE DB, AddWithKey requires that the native OLE DB provider obtains necessary primary key information by setting the DBPROP_UNIQUEROWS property, and then determines which columns are primary key columns by examining DBCOLUMN_KEYCOLUMN in the IColumnsRowset. As an alternative, the user may explicitly set the primary key constraints on each DataTable. This ensures that incoming records that match existing records are updated instead of appended. When using AddWithKey, the .NET Framework Data Provider for SQL Server appends a FOR BROWSE clause to the statement being executed. The user should be aware of potential side effects, such as interference with the use of SET FMTONLY ON statements. See SQL Server Books Online for more information.
Error	An InvalidOperationException is generated if the specified column mapping is missing.
Ignore	Ignores the extra columns.

• If there are primary key columns, data can be overwritten. If not, rows are appended.
• TableMappings control table and column names in the generated table. See below.
• A connection is needed, set on the SelectCommand.
• If the connection is not open, it will be opened and then closed.
• If you need to do multiple fills from same connection, it will be more efficient to open the connection yourself, do all the fills, then close the connection yourself.
• There are three overloads of the Fill method.
  • One takes a DataSet and creates a table named "Table".
  • One takes a DataTable.
  • One takes a DataSet and a table name.

Updating the database

• The InsertCommand, UpdateCommand and DeleteCommand values are used by Update method to resolve changes to a DataSet back to a data source.
• The Update method has the same arguments as Fill.
• Update processes all rows in the data tables looking for added, modified and deleted rows. It will process them in order.
• These queries need to be parameterized. Filling in values can be automated by setting the parameter's SourceColumn and SourceVersion properties. This is going to require an example.

Assume we have the following:

 static DataSet m_ds;
 static DbDataAdapter m_da;

We will provide our usual helper methods to get things from the provider factory:

        private static DbProviderFactory GetProviderFactory()
        {
            return DbProviderFactories
                    .GetFactory(ConfigurationManager.AppSettings["provider"]);
        }

        private static DbConnection CreateDbConnection()
        {
            string connStr = ConfigurationManager
                                .ConnectionStrings["SqlConnStr"]
                                .ConnectionString;
            DbConnection conn = GetProviderFactory().CreateConnection();
            conn.ConnectionString = connStr;
            return conn;
        }

        private static DbDataAdapter CreateDataAdapter()
        {
            return GetProviderFactory().CreateDataAdapter();
        }

        private static DbCommand CreateDbCommand()
        {
            return GetProviderFactory().CreateCommand();
        }

        private static DbParameter CreateDbParameter()
        {
            return GetProviderFactory().CreateParameter();
        }

To initialize, we first need to create the DataSet and DataAdapter.

            m_ds = new DataSet();
            m_da = CreateDataAdapter();

The SELECT command is easy. In this case, we are going to pull in all students from the table, so no parameterization is needed.

            DbCommand cmd;
            DbParameter param;
            
            cmd = CreateDbCommand();
            cmd.CommandText = "select Id, FirstName, LastName, Age from Student";
            m_da.SelectCommand = cmd;

Each of the remaining commands presents choices and complexity. Let's start with DELETE. The DELETE command itself can be written in several ways, depending on how much concurrency conflict checking you want to do.

• Specify the rows to delete using only the primary key.
• Specify the rows to delete using the primary key and additional fields.
  • This prevents a decision to delete from being made on stale data.

The data for the id and possibly other fields will come from a deleted DataRow in the DataTable. We need to pick up original values because a deleted row does not have current values. Here goes:

            cmd = CreateDbCommand();
            cmd.CommandText = "Delete from Student where Id = @Id and "
                + " FirstName = @FN and LastName = @LN and Age = @Age";
            m_da.DeleteCommand = cmd;

            param = CreateDbParameter();
            param.DbType = DbType.Int32;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "@Id";
            param.SourceColumn = "Id";
            param.SourceVersion = DataRowVersion.Original;
            cmd.Parameters.Add(param);

            param = CreateDbParameter();
            param.DbType = DbType.String;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "@FN";
            param.SourceColumn = "FirstName";
            param.SourceVersion = DataRowVersion.Original;
            cmd.Parameters.Add(param);

            param = CreateDbParameter();
            param.DbType = DbType.String;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "@LN";
            param.SourceColumn = "LastName";
            param.SourceVersion = DataRowVersion.Original;
            cmd.Parameters.Add(param);

            param = CreateDbParameter();
            param.DbType = DbType.Int32;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "@Age";
            param.SourceColumn = "Age";
            param.SourceVersion = DataRowVersion.Original;
            cmd.Parameters.Add(param);

For the UPDATE command:

• you need to specify the changed values and also indicate which row in the table to update.
• The changed values will be the current values in the row.
• The WHERE clause specifying the row to update has the same concurrency considerations as does DELETE.

              cmd = CreateDbCommand();
              cmd.CommandText = "Update Student " +
                                "Set FirstName = @FN, LastName = @LN, Age = @Age " +
                                " Where Id = @IDOLD and Firstname = @FNOLD " +
                                "  and LastName = @LNOLD and Age = @AGEOLD";
              m_da.UpdateCommand = cmd;
  
              param = CreateDbParameter();
              param.DbType = DbType.String;
              param.Direction = ParameterDirection.Input;
              param.ParameterName = "@FN";
              param.SourceColumn = "FirstName";
              param.SourceVersion = DataRowVersion.Current;
              cmd.Parameters.Add(param);
  
              param = CreateDbParameter();
              param.DbType = DbType.String;
              param.Direction = ParameterDirection.Input;
              param.ParameterName = "@LN";
              param.SourceColumn = "LastName";
              param.SourceVersion = DataRowVersion.Current;
              cmd.Parameters.Add(param);
  
              param = CreateDbParameter();
              param.DbType = DbType.Int32;
              param.Direction = ParameterDirection.Input;
              param.ParameterName = "@Age";
              param.SourceColumn = "Age";
              param.SourceVersion = DataRowVersion.Current;
              cmd.Parameters.Add(param);
  
              param = CreateDbParameter();
              param.DbType = DbType.Int32;
              param.Direction = ParameterDirection.Input;
              param.ParameterName = "@IDOLD";
              param.SourceColumn = "Id";
              param.SourceVersion = DataRowVersion.Original;
              cmd.Parameters.Add(param);
  
              param = CreateDbParameter();
              param.DbType = DbType.String;
              param.Direction = ParameterDirection.Input;
              param.ParameterName = "@FNOLD";
              param.SourceColumn = "FirstName";
              param.SourceVersion = DataRowVersion.Original;
              cmd.Parameters.Add(param);
  
              param = CreateDbParameter();
              param.DbType = DbType.String;
              param.Direction = ParameterDirection.Input;
              param.ParameterName = "@LNOLD";
              param.SourceColumn = "LastName";
              param.SourceVersion = DataRowVersion.Original;
              cmd.Parameters.Add(param);
  
              param = CreateDbParameter();
              param.DbType = DbType.Int32;
              param.Direction = ParameterDirection.Input;
              param.ParameterName = "@AGEOLD";
              param.SourceColumn = "Age";
              param.SourceVersion = DataRowVersion.Original;
              cmd.Parameters.Add(param);            
  

• The INSERT command would be relatively straightforward if we didn't have to deal with auto-generated keys.
• This command will be run over added rows, so current values are used.
• Getting the value for the auto-generated key is database-specific.
  • We will show here one way to do this with SQL Server.
  • We show other ways elsewhere.
• There are three things coordinated here to get the key.
  • The query contains the INSERT statement plus a SELECT statement to retrieve the generated id.
  • We set the command's UpdatedRowSource property to be UpdateRowSource.FirstReturnedRecord.
  • We create an output parameter to receive the updated value and map it to update the id value in the current row.

            cmd = CreateDbCommand();
            cmd.CommandText = "insert Student(FirstName, LastName, Age) " +
                              "VALUES( @FN, @LN, @Age);" + 
                              "select Scope_Identity() Id";
            m_da.InsertCommand = cmd;

            
            param = CreateDbParameter();
            param.DbType = DbType.String;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "@FN";
            param.SourceColumn = "FirstName";
            param.SourceVersion = DataRowVersion.Current;
            cmd.Parameters.Add(param);

            param = CreateDbParameter();
            param.DbType = DbType.String;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "@LN";
            param.SourceColumn = "LastName";
            param.SourceVersion = DataRowVersion.Current;
            cmd.Parameters.Add(param);

            param = CreateDbParameter();
            param.DbType = DbType.Int32;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "@Age";
            param.SourceColumn = "Age";
            param.SourceVersion = DataRowVersion.Current;
            cmd.Parameters.Add(param);

            param = CreateDbParameter();
            param.DbType = DbType.Int32;
            param.Direction = ParameterDirection.Output;
            param.ParameterName = "@Id";
            param.SourceColumn = "Id";
            param.SourceVersion = DataRowVersion.Current;
            cmd.Parameters.Add(param);

            cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

My, that seems like a lot of work. Fortunately, command builders can be used to generate the UPDATE, DELETE and INSERT commands from the SELECT statement. More here.

Here is code to drive this:

        private static void LoadStudents()
        {
            m_ds.Clear();

            using (DbConnection conn = CreateDbConnection())
            {
                m_da.SelectCommand.Connection = conn;
                m_da.Fill(m_ds,"Student");
            }
        }

        private static void InsertStudents()
        {
            DataTable dt = m_ds.Tables["Student"];

            DataRow dr;

            for (int i = 0; i < 5; ++i)
            {
                dr = dt.NewRow();
                dr["FirstName"] = "FN" + i;
                dr["LastName"] = "LN" + i;
                dr["Age"] = i;
                dt.Rows.Add(dr);
            }
        }

        private static void Update()
        {
            using ( DbConnection conn = CreateDbConnection() )
            {
                m_da.DeleteCommand.Connection = conn;
                m_da.InsertCommand.Connection = conn;
                m_da.UpdateCommand.Connection = conn;
               
                m_da.Update(m_ds.Tables["Student"]);
            }

        }


        private static void DeleteNewStudents()
        {
            foreach (DataRow dr in m_ds.Tables["Student"].Rows)
                if (((string)dr["FirstName"]).StartsWith("FN"))
                    dr.Delete();
        }

        private static void ShowStudents(string p)
        {
            DataTable dt = m_ds.Tables["Student"];

            Console.WriteLine("Here are the students {0}", p);

            foreach (DataRowView drv in dt.DefaultView)
            {
                foreach (DataColumn col in dt.Columns)
                    Console.Write("{0}\t", drv[col.Ordinal]);
                Console.WriteLine();
            }
        }

Output:

Here are the students before
1       Fred    Flintstone      45
2       Yogi    Bear    12
3       Jonny   Quest   16
4       George  Jetson  42
5       Tom     Smith   50
6       Tim     Smith   55
7       Jim     Smith   3
8       Tom     Jones   22
9       Tim     Jones   33
10      Joe     Jones   44


Here are the students after inserts
1       Fred    Flintstone      45
2       Yogi    Bear    12
3       Jonny   Quest   16
4       George  Jetson  42
5       Tom     Smith   50
6       Tim     Smith   55
7       Jim     Smith   3
8       Tom     Jones   22
9       Tim     Jones   33
10      Joe     Jones   44
        FN0     LN0     0
        FN1     LN1     1
        FN2     LN2     2
        FN3     LN3     3
        FN4     LN4     4


Here are the students after update
1       Fred    Flintstone      45
2       Yogi    Bear    12
3       Jonny   Quest   16
4       George  Jetson  42
5       Tom     Smith   50
6       Tim     Smith   55
7       Jim     Smith   3
8       Tom     Jones   22
9       Tim     Jones   33
10      Joe     Jones   44
34      FN0     LN0     0
35      FN1     LN1     1
36      FN2     LN2     2
37      FN3     LN3     3
38      FN4     LN4     4


Here are the students after deletes
1       Fred    Flintstone      45
2       Yogi    Bear    12
3       Jonny   Quest   16
4       George  Jetson  42
5       Tom     Smith   50
6       Tim     Smith   55
7       Jim     Smith   3
8       Tom     Jones   22
9       Tim     Jones   33
10      Joe     Jones   44