Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 21, 2007

Programming SQL Server 2005 Compact Edition with ADO - Page 3

By DatabaseJournal.com Staff

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.

  1. Create tables in DataSet and fill data in the tables from the data source.

  2. Insert, update, or delete the data in the data table.

  3. Call the GetChanges method. This method creates a DataSet with modified data.

  4. Call the Update method of Data Adapter with the DataSet created in the previous step as an argument.

  5. 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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date