Listing 7.5 Data Adapter and DataSet Object C#
SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Program
Files\\ParameterQuery\\AppDatabase1.sdf;");
myConnection.Open();
SqlCeCommand myCmd = myConnection.CreateCommand();
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = "Select * From Books";
SqlCeDataAdapter myDataAdapter = new SqlCeDataAdapter(myCmd);
DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet);
dataGrid1.DataSource = myDataSet.Tables[0];
Listing 7.5 Data Adapter and DataSet Object VB.NET
Dim myConnection As SqlCeConnection = New SqlCeConnection("DataSource=\Program Files\ParameterQuery\AppDatabase1.sdf")
Dim myCmd As SqlCeCommand = myConnection.CreateCommand()
myConnection.Open()
myCmd.CommandType = CommandType.Text
myCmd.CommandText = "Select * From Books"
Dim myDataAdapter As SqlCeDataAdapter = New SqlCeDataAdapter(myCmd)
Dim myDataSet As DataSet = New DataSet()
myDataAdapter.Fill(myDataSet)
DataGrid1.DataSource = myDataSet.Tables(0)
Table 7.7 describes the usage of SqlCeDataAdapter methods.
Table 7.7 Commonly Used Data Adapter Object Public Methods
|
Method |
Method Description |
|
Equals
|
Returns TRUE if two object instances are equal.
|
|
Fill
|
Fills data from a data source into one or more tables of DataSet.
|
|
FillSchema
|
Adds a DataTable to DataSet and configures the schema to match in data source.
|
|
GetType
|
Gets the type of instance.
|
|
ReferenceEquals
|
Returns TRUE if specified object instances are equal.
|
|
ToString
|
Returns a string having the name of component.
|
|
Update
|
For each row in DataSet that has changed, the Update method calls the InsertCommand, DeleteCommand or UpdateCommand.
|
Table 7.8 shown below lists the SqlCeDataAdapter properties you can set.
Table 7.8 Commonly Used Data Adapter Object Public Properties
|
Properties |
Property Description |
|
AcceptChangesDuringFill
|
Gets or sets a value to indicate whether AcceptChange is called for the DataRow added to the DataTable during Fill operations.
|
|
AcceptChangesDuringUpdate
|
Gets or sets a value to indicate whether AcceptChange is called for the DataRow added to the DataTable during Update operations.
|
|
DeleteCommand
|
Data command to delete a data row.
|
|
InsertCommand
|
Gets or sets the data command to insert rows in data source.
|
|
MissingMappingAction
|
Specifies the action to be taken when incoming data does not match an existing table/column.
|
|
MissingSchemaAction
|
Specifies the action to be taken when incoming data does not match an existing DataSet schema.
|
|
SelectCommand
|
Gets or sets the data command to select rows from the data source.
|
|
Site
|
Gets or sets the ISite of the component.
|
|
TableMappings
|
Collection of DataTableMapping between the source table and DataTable.
|
|
UpdateBatchSize
|
Gets or sets a value to indicate the number of commands that can be executed in a batch.
|
|
UpdateCommand
|
Gets or sets data command to update rows in the data source.
|
Understanding DataSet
DataSet is a memory resident structure consisting of relational data. DataSet consists of the collection of DataTable objects as shown in Figure
7.2. The DataTable object contains the actual data. You can use the DataRelation object in DataSet to relate the collection of tables in DataSet. For ensuring data integrity you can use the UniqueConstraint and ForeignKeyConstraint objects.
Figure 7.2
DataSet
You can create DataSet interactively in Visual Studio or programmatically.
To use DataSet, follow these setps.
Create tables in DataSet and fill data in the tables from the data source.
Insert, update, or delete the data in the data table.
Call the GetChanges method. This method creates a DataSet with modified data.
Call the Update method of Data Adapter with the DataSet created in the previous step as an argument.
Call the Merge method.
Call the AcceptChanges or RejectChanges methods of DataSet.
Table 7.9 describes the SqlCeDataSet methods.
Table 7.9 Commonly Used DataSet Object Public Methods
|
Method |
Method Description |
|
AcceptChanges
|
Commits all changes to DataSet.
|
|
BeginInit
|
Begins the initialization of the DataSet.
|
|
Clear
|
Empties all tables in DataSet.
|
|
Clone
|
Copies the structure of DataSet.
|
|
Copy
|
Copies the structure and content of DataSet.
|
|
CreateDataReader
|
Returns a DataTableReader with one result set for each data table. The result set is returned in the same sequence as tables appear in the tables collection.
|
|
EndInit
|
Ends the initialization of DataSet.
|
|
Equals
|
Determines whether two object instances are equal.
|
|
GetChanges
|
Gets a copy of DataSet containing only the changed rows in all the tables in DataSet.
|
|
GetDataSetSchema
|
Gets the Schema of DataSet.
|
|
GetType
|
Gets the Type of current instance.
|
|
GetXml
|
Gets the XML representation of DataSet.
|
|
GetXmlSchema
|
Gets the XML representation of data stored in DataSet.
|
|
HasChanges
|
Gets a value to specify whether DataSet has changes.
|
|
InferXmlSchema
|
Applies XML schema to DataSet.
|
|
Load
|
Fills DataSet with values from Data Source.
|
|
Merge
|
Merges specified DataSet, DataTable or array of rows into Current DataSet or DataTable.
|
|
ReadXml
|
Reads XML schema and data into DataSet.
|
|
ReadXmlSchema
|
Reads XML Schema into DataSet.
|
|
ReferenceEquals
|
Checks whether there is a specified object.
|
|
RejectChanges
|
Rolls back all the pending changes in DataSet.
|
|
Reset
|
Resets the DataSet to its original state.
|
|
ToString
|
Returns the string name.
|
Table 7.10 lists the SqlCeDataSet properties that can be set.
Table 7.10 Commonly Used DataSet Object Public Properties
|
Properties |
Property Description |
|
CaseSensitive
|
Gets or sets whether a string comparison is case sensitive or not.
|
|
Container
|
Gets the container for a component.
|
|
DataSetName
|
Gets or sets the name of DataSet.
|
|
DefaultViewManager
|
Defaults sorting and filtering for DataSet.
|
|
DesignMode
|
Gets a value to specify if component is in Design Mode.
|
|
EnforceConstraints
|
Gets or sets values to specify whether constraint rules are followed during update.
|
|
ExtendedProperties
|
Gets the custom user information.
|
|
HasErrors
|
Gets a value to indicate whether there are any errors in any DataTable objects in DataSet.
|
|
IsInitialized
|
Gets a value to specify whether DataSet is initialized or not.
|
|
Locale
|
Gets or sets the locale information to be used for a string comparison.
|
|
Namespace
|
Gets or sets the namespace for DataSet.
|
|
Prefix
|
Gets or sets the XML prefix used as an alias for namespace for DataSet.
|
|
Relations
|
Gets collection of DataRelations objects that relate the DataTables from DataSet. Using this you can navigate from parent table to child tables.
|
|
RemotingFormat
|
Gets or sets the SerializeFormat.
|
|
SchemaSerializationMode
|
Gets or sets the SchemaSerializationMode for DataSet.
|
|
Site
|
Gets or sets System.ComponentModel.ISite for DataSet.
|
|
Tables
|
Gets the collection of tables in DataSet.
|
DataSet is capable of representing multiple tables like a relational database. The DataSet object uses a collection of dependant objects. A DataSet object can be comprised of one or more DataTable objects. DataTable consists of a collection of DataRows objects. DataRow is similar to a row in a database table except there is no concept of a Current row.
The attributes of each column such as data type, length, etc., are represented by a collection of DataColumn objects.
Listing 7.6 describes an addition of a data row in an existing DataSet. Using the code, a new row will be added in the DataSet described in Listing 7.5.
» 
