|
|
The DataAdapter object is used as a bridge between DataSet objects inside client applications and the data sources from which they were populated. The two key functions of the DataAdapter object are to update the data source with data inside the DataSet and vice versa.
This means that the DataAdapter is implemented to perform both retrieve and update operations on data sources. The generic System.Data.Common.DbDataAdapter class is quite limited in terms of the operations that it can perform. For this reason, we will look at a highly optimized DataAdapter class—the System.Data.SqlClient.SqlDataAdapter class.
| Note |
The properties and methods that are not inherited from the generic DataAdapter class are marked appropriately. |
The constructor is worth mentioning for the DataAdapter object. It overloads the generic constructor of the DbDataAdapter class and allows your application more flexibility to initialize a communication channel between client and database objects.
Taking advantage of the robust OOP concept of polymorphism, you can initialize a DataAdapter object in four different ways depending on the situation.
Initializing a DataAdapter object this way simply creates a new instance of the DataAdapter object in memory. This is recommended when you are coding in a situation where you are not exactly sure what type of communication will take place between the data source and client ADO .NET objects.
Example:
Dim objDA as New System.Data.SqlClient.SqlDataAdapter()
This type of initialization creates an instance of the DataAdapter object and initializes its SelectCommand property as a reference to the SqlCommand object passed as the parameter. Note that the SelectCommand property is not an initialized, new instance of SqlCommand in this case; rather it is a reference to the SqlCommand object that was passed as a parameter. This means that the two objects are pointing to the same value in memory.
The database command referenced by the SelectCommand property is directly executed against the database, and the result set is stored inside the DataAdapter object.
This type of initialization is used when you already know what type of command needs to be executed against the database and the command is already prepared.
Example:
Dim objConn as SqlConnection = New SqlConnection
("DataSource=LOCALHOST;Database=Northwind;
UserID=sa;password=sa")
Dim objCmd as SqlCommand = New SqlCommand("SELECT * FROM
Employees")
Dim objDA as SqlDataAdapter = New SqlDataAdapter(objCmd)
This type of initialization creates an instance of the DataAdapter object by specifying a command text and connection text that it uses to connect and issue commands to a database.
The DataAdapter object first connects to the database using the parameters contained within the connection string parameter. To perform that action, it utilizes an internally optimized SqlConnection object.
The command parameter is used to issue an SQL command to the database through the object’s SelectCommand property. The SQL command must be an SQL SELECT command or a command that executes a stored procedure expected to return results.
Example:
Dim strCommand as String
Dim strConn as String
strConn = "DataSource=LOCALHOST;Database=Northwind;
UserID=sa;password=sa"
strCommand = "SELECT * FROM Employees"
Dim objDA as SqlDataAdapter = New SqlDataAdapter
(strCommand, strConn)
This type of initialization creates an instance of the DataAdapter object by specifying a command text and a valid connection object that it uses to connect and issue commands to a database.
The DataAdapter object uses the connection parameter to connect to the database. Note that the connection parameter must already contain an open connection to the database.
The command parameter is used to issue an SQL command to the database through the object’s SelectCommand property. The SQL command must be an SQL SELECT command or a command that executes a stored procedure expected to return results.
Example:
Dim strCommand as String
Dim objConn as SqlConnection = New SqlConnection
("DataSource=LOCALHOST;Database=Northwind;
UserID=sa;password=sa")
strCommand = "SELECT * FROM Employees"
Dim objDA as SqlDataAdapter = New
SqlDataAdapter(strCommand, objConn)
Type: Boolean
Attribute: Read/Write
Default: Null
Description: This property is used to determine whether the DataRow method AcceptChanges() is called when a new DataRow is added to a DataTable within the DataAdapter object.
If this property is set to True, the AcceptChanges() method is called immediately after every DataRow object is added to the DataTable object.
Type: Boolean
Attribute: Read/Write
Default: Null
Description: This property is used to determine whether the DataAdapter should generate an exception when an error occurs while updating a row in the database or continue with the update of the same row and any other rows.
When this property is set to True, the DataAdapter object continues updating rows and does not generate any exception or error messages when an error occurs.
Type: SqlCommand
Attribute: Read/Write
Default: Null
Description: This property is a reference to an SqlCommand object that is optimized to delete data from a database. The CommandText property of that object must be either an SQL statement that deletes data from a database or a call to a stored procedure that performs this function. This is important because it is impossible to return values using this property. A typical scenario would be to prepare the SqlCommand object in code and then assign it to this property.
Type: SqlCommand
Attribute: Read/Write
Default: Null
Description: This property is a reference to an SqlCommand object that is optimized to insert data into a database. The CommandText property of that object must be either an SQL statement that inserts data from a database or a call to a stored procedure that performs this function. This is important because it is impossible to return values or delete data using this property. A typical scenario would be to prepare the SqlCommand object in code and assign it to this property.
Type: MissingMappingAction enumeration
Attribute: Read/Write
Default: Null
Description: This property is used to evaluate which action the DataAdapter performs when data that it is given does not have a valid mapping or match a DataTable object inside a DataSet object. The possible values are:
Error: An exception is generated by the DataAdapter.
Ignore: The DataAdapter ignores the error and inserts a null value into the column.
Passthrough: The DataAdapter causes the DataSet to create a column or table matching the unmatched value.
Type: MissingSchemaAction Enumeration
Attribute: Read/Write
Default: Null
Description: This property is used to evaluate which action the DataAdapter performs when data that it is given does not have a valid mapping to any existing DataTable or DataColumn object inside a DataSet. In other words, the DataTable or DataColumn that is referred to is missing. The possible values are:
Add: The DataAdapter adds the DataColumn or DataTable object to the DataSet.
AddWithKey: The DataAdapter recreates the schema for the DataSet so that it incorporates information for the missing DataTable or DataColumn object.
Ignore: The DataAdapter ignores the missing DataColumn or DataTable object(s) that the code is referring to. In this scenario, data will be lost.
Error: Generates an exception
Type: SqlCommand
Attribute: Read/Write
Default: Null
Description: This property is a reference to an SqlCommand object that is optimized to retrieve data from a database. The CommandText property of that object must be either an SQL statement that retrieves data from a database or a call to a stored procedure that performs this function. This is important because it is impossible to delete or insert data using this property. A typical scenario would be to prepare the SqlCommand object in code and assign it to this property.
Type: DataTableMappingCollection
Attribute: Read-only
Default: Null
Description: This property is a collection of all the available table mapping information that is present between DataTable objects within a DataSet and the Table objects in the database from which data has just been retrieved by the DataAdapter. This data is about to be filled into the DataSet.
Type: SqlCommand
Attribute: Read/Write
Default: Null
Description: This property is a reference to an SqlCommand object that is optimized to update data from a DataSet into a database. The CommandText property of that object must be either an SQL statement that updates data into a database or a call to a stored procedure that performs this function. This is important because it is impossible to retrieve, delete, or insert data using this property. A typical scenario would be to prepare the SqlCommand object in code and assign it to this property.
System.Runtime.Remoting.ObjRef: The returned object contains all the required information to generate a proxy that can communicate with another object residing remotely.
Call this method when you need a reference to a valid object that contains all the required information to generate a proxy for the DataAdapter, which will allow it to communicate with another object residing remotely. This method is used if you want the DataAdapter to communicate with remote objects.
This method has a very solid polymorphic mechanism. The actions performed by this method hinge on the type of parameters passed to it. The following gives a list of parameters and their corresponding descriptions.
System.Data.DataTable: A DataTable object into which data is to be copied.
Use this parameter only when you need to fill a single DataTable object for use on the client.
System.Data.DataSet: A DataSet object into which data is to be copied.
Use this parameter when you need to fill an entire DataSet object for use on the client. A typical scenario is when the DataAdapter contains multiple result sets.
System.Data.DataSet: A DataSet object into which data is to be copied.
String: A string containing the name of a source table that contains the mapping information for the retrieved result set.
Use this parameter when you need to fill an entire DataSet object for use on the client. The schema of the database will also be created inside the DataSet using the information from the source table. A typical scenario is when the DataAdapter contains multiple result sets and you also want to put a schema inside the DataSet.
System.Data.DataSet: A DataSet object into which data is to be copied
Integer: The record at which you want to start populating
Integer: The maximum number of records to populate
String: A string containing the name of a source table that contains the mapping information for the retrieved result set
System.Data.DataTable: A valid DataTable object into which the DataAdapter populates a valid schema
This method also has a very solid polymorphic mechanism. The actions performed by this method hinge on the type of parameters passed to it. The following gives a list of parameters and their corresponding descriptions.
System.Data.DataTable: A DataTable object into which the schema is copied
SchemaType: A valid value from the SchemaType enumeration.
Use this parameter only when you need to fill a single DataTable object to contain the schema information of a result set.
System.Data.DataSet: A DataSet object into which the schema is copied
SchemaType: A valid value from the SchemaType enumeration.
Use this parameter only when you need to fill a single DataSet object to contain the schema information of a result set.
System.Data.DataSet: A DataSet object into which to copy the schema
SchemaType: A valid value from the SchemaType enumeration
String: A string value containing the name of a source table that contains the mapping information for the retrieved result set.
Use this parameter only when you need to fill a single DataSet object to contain the schema information of a result set based on table mapping information.
Integer: An integer value indicating the number of rows that were affected by the Update() method
This method also has a very solid polymorphic mechanism. The actions performed by this method hinge on the type of parameters passed to it. The following gives a list of parameters and their corresponding descriptions.
System.Data.DataSet: The DataSet that you wish to use to update a data source.
Passing this parameter instructs the method to update the data source with the data values found inside the DataSet object.
System.Data.DataRow: An array of DataRow objects that you wish to use to update a data source.
Passing this parameter instructs the method to update the data source with the data values found inside an array of DataRow objects.
System.Data.DataTable: The DataTable that you wish to use to update a data source.
Passing this parameter instructs the method to update the data source with the data values found inside the DataTable object.
System.Data.DataSet: The DataSet that you wish to use to update a data source.
String: The name of the source table that is used for table/DataColumn mapping.
Passing this parameter instructs the method to update the data source with the data values found inside the DataSet object. If table mapping is necessary, the table specified by the second parameter is used for that purpose.
|
|