|
|
You use an object of the SqlDataAdapter class to synchronize data stored in a DataSet object with a SQL Server database. You use an object of the OleDbDataAdapter class to synchronize data with a database that supports OLE DB, such as Oracle or Access. You use an object of the OdbcDataAdapter class to synchronize data with a database that supports ODBC.
Note | Although the SqlDataAdapter class is specific to SQL Server, many of the properties and methods in this class are the same as those for the OleDbDataAdapter and OdbcDataAdapter classes. |
Table 10.1 shows some of the SqlDataAdapter properties.
PROPERTY | TYPE | DESCRIPTION |
---|---|---|
AcceptChangesDuringFill | bool | Gets or sets a bool that indicates whether the AcceptChanges() method is called after a DataRow object has been added, modified, or removed in a DataTable object. The default is true. |
ContinueUpdateOnError | bool | Gets or sets a bool that indicates whether to continue updating the database when an error occurs. When set to true, no exception is thrown when an error occurs during the update of a row. The update of the row is skipped and the error information is placed in the RowError property of the DataRow that caused the error. The DataAdapter continues to update subsequent rows. When set to false, an exception is thrown when an error occurs. The default is false. |
DeleteCommand | SqlCommand | Gets or sets a command containing a SQL DELETE statement or stored procedure call to remove rows from the database. |
InsertCommand | SqlCommand | Gets or sets a command containing a SQL INSERT statement or stored procedure call to add rows to the database. |
MissingMappingAction | MissingMappingAction | Gets or sets the action to take when the incoming table or column doesn't have a matching table or column in the TableMappings collection. The values for this action come from the System.Data.MissingMappingAction enumeration with the members Error, Ignore, and Passthrough:
The default is Passthrough. |
MissingSchemaAction | MissingSchemaAction | Gets or sets the action to take when the incoming column doesn't have a matching column in the DataTable object's Column collection. The values for this action come from the System.Data.MissingSchemaAction enumeration with the members Add, AddWithKey, Error, and Ignore:
The default is Add. |
SelectCommand | SqlCommand | Gets or sets a command containing a SQL SELECT statement or stored procedure call to retrieve rows from the database. |
TableMappings | DataTableMappingCollection | Gets a DataTableMappingCollection that contains the mapping between a database table and a DataTable object in the DataSet. |
UpdateCommand | SqlCommand | Gets or sets a command containing a SQL UPDATE statement or stored procedure call to modify rows in the database. |
Table 10.2 shows some of the SqlDataAdapter methods.
METHOD | RETURN TYPE | DESCRIPTION |
---|---|---|
Fill() | int | Overloaded. Synchronizes the rows in the DataSet object to match those in the database. The int returned by this method is the number of rows synchronized in the DataSet with the database. |
FillSchema() | DataTable DataTable[] | Overloaded. Adds a DataTable to a DataSet object and configures the schema to match the database. |
GetFillParameters() | IDataParameter[] | Returns an array of any parameters set for the SQL SELECT statement. |
Update() | int | Overloaded. Calls the respective SQL INSERT, UPDATE, or DELETE statements or stored procedure call (stored in the InsertCommand, UpdateCommand, and DeleteCommand properties, respectively) for each row that has been added, modified, or removed from a DataTable object. The int returned by this method is the number of rows updated. |
Table 10.3 shows some of the SqlDataAdapter events.
EVENT | EVENT HANDLER | DESCRIPTION |
---|---|---|
FillError | FillErrorEventHandler | Fires when an error occurs during a fill operation. |
RowUpdating | RowUpdatingEventHandler | Fires before a row is added, modified, or deleted in the database. |
RowUpdated | RowUpdatedEventHandler | Fires after a row is added, modified, or deleted in the database. |
You'll learn how to use some of these properties and methods to store data in DataSet objects in this chapter. You'll see how to use the other properties, methods, and the events in Chapter 11, in which you'll learn how to modify data in DataSet objects, and then push those modifications to the database.
You create a SqlDataAdapter object using one of the following SqlDataAdapter constructors:
SqlDataAdapter() SqlDataAdapter(SqlCommand mySqlCommand) SqlDataAdapter(string selectCommandString, SqlConnection mySqlConnection) SqlDataAdapter(string selectCommandString, string connectionString)
where
mySqlCommand specifies your SqlCommand object.
selectCommandString specifies your SELECT statement or stored procedure call.
mySqlConnection specifies your SqlConnection object.
connectionString specifies your connection string to connect to the database.
The following example uses the SqlDataAdapter() constructor to create a SqlDataAdapter object:
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
Before using mySqlDataAdapter to populate a DataSet, you must set its SelectCommand property to a SqlCommand that contains a SELECT command or stored procedure call. The following example creates a SqlCommand object with its CommandText property set to a SELECT statement that will retrieve the top five rows from the Products table, and sets the CommandText property of mySqlDataAdapter to that SqlCommand object:
SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; mySqlDataAdapter.SelectCommand = mySqlCommand;
The next example uses the SqlDataAdapter(SqlCommand mySqlCommand) constructor:
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySqlCommand);
The next example uses the SqlDataAdapter(string selectCommandString, SqlConnection mySqlConnection) constructor:
SqlConnection mySqlConnection = new SqlConnection( "server=localhost;database=Northwind;uid=sa;pwd=sa" ); string selectCommandString = "SELECT TOP 10 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(selectCommandString, mySqlConnection);
The final example uses the SqlDataAdapter(string selectCommandString, string connectionString) constructor:
string selectCommandString = "server=localhost;database=Northwind;uid=sa;pwd=sa"; string connectionString = "SELECT TOP 10 ProductID, ProductName, UnitPrice " + "FROM Products " + "ORDER BY ProductID"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(selectCommandString, connectionString);
Warning | This constructor causes the SqlDataAdapter object to create a separate SqlConnection object. This is typically undesirable from a performance perspective because opening a connection to the database using a SqlConnection object takes a relatively long time. You should therefore avoid using the SqlDataAdapter(string selectCommandString, string connectionString) constructor. Instead, you should use an existing SqlConnection object with your SqlDataAdapter object. |
A DataAdapter object doesn't store rows: it merely acts as a conduit between the database and an object of the DataSet class. In the next section, you'll learn about the DataSet class.
|
|