The SqlDataAdapter Class

The SqlDataAdapter Class

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.

Table 10.1: 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:

  • Error means a SystemException is thrown.

  • Ignore means the table or column is ignored and not read.

  • Passthrough means the table or column is added to the DataSet with its original name.

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:

  • Add means the column is added to the DataTable.

  • AddWithKey means the column and primary key information is added to the DataTable.

  • Error means a SystemException is thrown.

  • Ignore means the column is ignored and not read.

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.

Table 10.2: 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.

Table 10.3: 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.

Creating a SqlDataAdapter Object

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.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net