Programming SQL Server 2005 Compact Edition with ADO

Tuesday Aug 21st 2007 by DatabaseJournal.com Staff
Share:

Learn how to use ADO.NET to connect to the SQL Server Compact Edition database as well as how to read, update, and merge the changes back to the SQL Server Compact Edition database in this excerpt from "Microsoft SQL Server 2005 Compact Edition".

Microsoft® SQL Server 2005 Compact Edition
By Prashant Dhingra and Trent Swanson
Published by Sams
ISBN-10: 0-672-32922-0
ISBN-13: 978-0-672-32922-7
Buy this book

IN THIS CHAPTER

  • Introducing ADO.NET

  • Building Applications

  • Summary

SQL Server Compact Edition allows you to store data on a desktop or mobile device. A majority of application code consists of retrieving data from a database, updating it, and storing it back to the database. You can use an OLE DB Provider for accessing or manipulating the database or you can use an ADO.Net provider for managed access.

In this chapter, you will learn how to use ADO.NET to connect to the SQL Server Compact Edition database as well as how to read, update, and merge the changes back to the SQL Server Compact Edition database.

Introducing ADO.NET

ADO.NET is the data access component for .NET Framework and .NET Compact Framework. Using the namespace and classes available in ADO.NET, you can access, manipulate, and update the SQL Server Compact Edition database.

ADO.NET provides a standardized mechanism to access a database. It is independent of the data source. Once you have referred to a database namespace, you will use a consistent set of API objects and properties to access the database. If you have used ADO.NET to access an SQL Server database, then you will find accessing SQL Server Compact Edition very similar. Instead of referring to System.Data.SqlClient namespace, you will be referring to System.Data.SqlServerCE namespace.

The data access architecture consists of a physical data store and ADO.NET classes. ADO.NET classes are divided into two categories. The first category communicates with the physical data store and is called Data Provider. The second category represents the in-memory representation of data.

  • The physical data store can be an SQL Server database, XML file, or an SQL Server Compact Edition database.

  • The data provider layer consists of a connection object and a command object to create in-memory representation of data.

  • The uppermost layer is an in-memory representation of data consisting of tables and relationships.

The data provider layer is an abstract layer between the physical store and the in-memory representation on which the application works. Once the data set is created, it does not matter which is the physical data store. This is termed a connectionless or a disconnected solution.

Namespace

ADO.NET provides its classes, methods, and properties through a set of namespaces. The classes combined together in these namespaces are termed ADO.NET. You should refer to the appropriate namespace to use classes in the respective namespace.

System.Data

The ADO.NET objective is to provide data-access functionality. System.Data is perhaps the most important namespace among all the namespaces in ADO.NET. This namespace also contains the most important classes related to Data Access, such as, DataSet, DataTable, DataColumn, DataRow, and DataView.

System.Data.Common

As discussed earlier there are three layers—data source, data provider, and in-memory representation of the data layer. The System.Data.Common namespace consists of classes related to the data provider layer. This namespace has providers for ODBC, OLDB, SQL Server, and so on.

System.Data.SqlClient

The System.Data.SqlClient is the data provider for SQL Server. The SQL Server data provider exists both in .NET Framework and .NET Compact Framework. For desktop-based applications. use a .NET Framework data provider for SQL Server. For a Smart device-based application, use a .NET Compact Framework data provider for SQL Server.

The System.Data.SqlClient namespace in .NET Compact Framework corresponds to the System.Data.SqlClient namespace in .NET Framework.

The SQL Server Data Provider has classes to access the SQL Server database with managed code using .NET Compact Framework. You can use SQL Server Data Provider to connect, execute the command, and retrieve the data from the SQL Server database.

The System.Data.SqlClient classes in .NET Compact Framework match with the System.Data.SqlClient classes in .NET Framework except for a few limitations including:

  • Connection pooling is not supported.

  • Distributed transactions are not supported.

  • Encrypted connections to SQL Server are not supported.

  • Connection string properties related to connection pooling, encryption, network library, and so on are not supported.

  • Windows Authentication should be used to connect to SQL Server from Smart devices. SQL Server authentication to connect to SQL Server from Smart device is not supported.

  • SqlClientPermission and SqlClientPermissionAttribute classes are not supported.

For complete details you should refer to the System.Data.SqlClient documentation.

The System.Data.SqlClient namespace consists of a data provider for SQL Server. The SQL Server Data Provider has classes to access the SQL Server database. You can use the SQL Server Data Provider to access the database to fill data into the DataSet.

System.Data.SqlServerCe

The System.Data.SqlServerCe namespace is the data provider for SQL Server Compact Edition. The SQL Server Compact Edition Data Provider contains classes that are specific to the SQL Server Compact Edition data source. You should add the reference to SqlServerCe namespace in the program using SQL Server Compact Edition classes as shown in Figure 7.1.

Figure 7.1
ADO.NET Object Model

Listing 7.1 SqlServerCe Namespace C#

Using System.Data.SqlServerCe

Listing 7.1 SqlServerCe Namespace VB.NET

Imports System.Data.SqlServerCe

Introducing the ADO.NET Object Model

This section starts with a simplified model of ADO.NET. Figure 7.1 depicts the primary objects in ADO.NET.

The classes are divided into two categories:

  • Data provider that communicates with the SQL Server Compact Edition data source.

  • DataSets that store the memory representation of data.

Understanding Data Providers

The data provider category of classes interacts with a physical data store and provides the data to a DataSet category of classes. The data provider category of classes interacts directly with a physical store. This is the reason a data provider is specific to a data source. For SQL Server Compact Edition, .NET Framework and .NET Compact Framework provide two data providers:

  • OLE DB Data Provider

  • SQL Server Compact Edition Data Provider

As shown in Figure 7.1, a connection object connects to a data source. The Data Consumer can call the Execute Reader method to execute the Data Reader against the connect object. You can execute the command object independently or use it with Data Adapter. Data Adapter acts like glue between the connection object and DataSet.

Connection Object

The connection object represents the unique connection to the data source. The SqlCeConnection object manages the connection to the physical data store. The connection string that determines the path of the SQL Server Compact Edition database is the property of the connection object. Listing 7.2 describes the usage of Open and Close methods to open and close connections to the database.

The connection to the database does not close if the SqlCeConnection object goes out of scope. You must explicitly close the connection.

Listing 7.2 Connection Object C#

SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Mobile\\Northwind.sdf;");
myConnection.Open();
..
..
myConnection.Close();

Listing 7.2 Connection Object VB.NET

Dim myConn As SqlCeConnection = New SqlCeConnection("DataSource=\Mobile\Northwind.sdf")
myConnection.Open()
..
..
myConnection.Close()

NOTE - SQL Server Compact Edition 3.x supports multiple simultaneous connections to a database.


Table 7.1 lists the connection object methods.

Table 7.1 Commonly Used Connection Object Methods

Method

Method Description

BeginTransaction

Begins a database transaction.

ChangeDatabase

Changes the current database.

Close

Closes a database connection

CreateCommand

Creates an SqlCeCommand object.

Dispose

Releases the resources used by the connection object.

Equals

Compares two connection objects and returns TRUE if objects are equal.

GetHashCode

Serves as a hash function for a particular type.

GetSchema

Returns schema information for its data source.

GetType

Gets type of current instances.

Open

Opens a database connection.

ReferenceEquals

Compares two connection objects and returns TRUE if objects are the same.

ToString

Returns a string that represents the current object.

Table 7.2 lists the connection object properties.

Table 7.2 Commonly Used Connection Object Properties

Properties

Property Description

ConnectionString

Gets or sets the string used to open a database.

ConnectionTimeOut

Gets the amount of time to wait while attempting to open a connection before generating an error.

DataBase

Gets the name of the database.

DataSource

Gets the physical file name including path of data source.

ServerVersion

Returns SQL Server Compact Edition version number.

Site

Gets or sets the ISite of a component.

State

Gets the current state of a connection.

In the connection string, you can specify the location and size of the temporary database. To specify the temporary database, the temp file directory and temp file max size properties must be specified in the connection string.

Command Object

The SQLCeCommand object allows you to execute an SQL command against the SQL Server Compact Edition database. A data command object is simply an SQL command that is executed against a connection object. Like most of the object, the data command object can be created at design time or at runtime in code. Listing 7.3 describes how a command object can be created.


NOTE - Multiple commands can share the same connection object.


Listing 7.3 Command Object C#

SqlCeCommand myCmd = myConnection.CreateCommand();

Listing 7.3 Command Object VB.NET

Dim myCmd As SqlCeCommand = myConn.CreateCommand()

Commonly used command object methods are listed in Table 7.3.

Command Object Properties

The data command commonly used properties are described in Table 7.4. The CommandText property contains the actual text of the SQL command to be executed. The Connection property contains a reference to a connection object. The Parameters property contains a collection of parameters required for the SQL command. The Command object properties will be validated for syntax errors. Final validation occurs when a command is executed by a data source.

Table 7.3 Commonly Used Command Object Methods

Method

Method Description

Cancel

Cancels execution.

CreateParameter

Creates a new parameter.

ExecuteNonQuery

Executes the query and returns the number of rows affected.

ExecuteReader

Builds a Data Reader.

ExecuteScalar

Executes the query and returns the first column of the first row of the result set.

ExecuteXMLReader

Builds an XML Reader.

Prepare

Builds a compiled version of command on data source.

ResetCommandTimeout

Sets the timeout to its default value.

Table 7.4 Commonly Used Command Object Properties

Properties

Property Description

CommandText

Gets or sets the SQL command that executes on a database.

CommandTimeOut

Gets or sets the amount of time to wait while executing a command before returning an error.

CommandType

Gets or sets a value that determines how CommandType is interpreted.

 

Text—when passing a query.

 

TableDirect—returns all rows and columns of a table. Command Text is the name of the table.

Connection

Gets or sets the SqlCeConnection.

IndexName

Specifies the index to be opened.

Parameters

Gets the SqlCeParameterCollection.

Site

Gets or sets the ISite of a component.

Transaction

Gets or sets the transaction that SqlCeCommand executes.


NOTE - SQL Server Compact Edition Data Provider does not support batched queries.


ExecuteReader, ExecuteScalar, and ExecuteNonQuery are the most import methods to execute an SQL command against a connection object.

The following section discusses the SqlCeDataReader class. Using Data Reader the above methods can be executed.

Data Reader

SQL Server Compact Edition provides an SqlCeDataReader class to implement Data Reader. A Data Reader is like a forward-only cursor for reading a data source. To use Data Reader, you need to create an SqlCeDataReader object and execute an ExecuteReader method of the object. To retrieve the rows, use the read method in a loop. Each iteration in a loop will read a row.

Listing 7.4 opens an SQLCeConnection. It creates a command. A command type property is set to TableDirect, and the CommandText property is set to a table name. A TableDirect command is similar to a Select statement that returns all rows of a table.

When you define a CommandType property to TableDirect, you use a base table cursor. You do not use a base table cursor with a Select statement. Instead, you can use it with Data Reader and a result set by setting the CommandType property to TableDirect.

The code creates the Data Reader by the ExecuteReader method while the loop reads the rows and populates the listbox.

Listing 7.4 Data Reader C#

// Code 
SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Program Files\\DataReader\\Northwind.sdf");
myConnection.Open();
SqlCeCommand myCmd = myConnection.CreateCommand();
SqlCeDataReader myDataReader ;
myCmd.CommandText = "Products";
myCmd.CommandType = CommandType.TableDirect;
myDataReader = myCmd.ExecuteReader();
while (myDataReader.Read()){
listbox1.Items.Add(myDataReader.GetValue(0));
}
myDataReader.Close();
}

Listing 7.4 Data Reader VB.NET

Dim myConnection As SqlCeConnection
myConnection = New SqlCeConnection("Data Source=\Program Files\DataReader\Northwind.sdf;")
myConnection.Open()
Dim myCmd As SqlCeCommand = myConnection.CreateCommand()
myCmd.CommandText = "Products"
myCmd.CommandType = CommandType.TableDirect
Dim myDataReader As SqlCeDataReader = myCmd.ExecuteReader()
While myDataReader.Read()
   listbox1.Items.Add(myDataReader.GetValue(0))
End While
myDataReader.Close()
myConnection.Close()

The method exposed by Data Reader is shown in Table 7.5.

The Open method is used to open the DataReader. The DataReader is positioned at the beginning of a result set, before the first row. The Read method reads the row from the result. Any subsequent Read method will read the next row from the result set.

Table 7.5 Commonly Used Data Reader Object Methods

Method

Method Description

Close

Closes the Data Reader object.

Equals

Returns TRUE if two object instances are equal.

GetBoolean

Gets the value of a specified column as Boolean.

GetByte

Gets the value of a specified column as Byte.

GetBytes

Reads a stream of bytes from a column offset.

GetChars

Reads a stream of characters from a column offset.

GetData

Returns a DBDataReader object.

GetDataTypeName

Gets the name of a data source type

GetDateTime

Gets the specified column as a DateTime object.

GetDecimal

Gets the specified column as a Decimal object.

GetDouble

Gets the specified column as a Double object.

GetFieldType

Gets the type of the object.

GetFloat

Gets the specified column as a Float object.

GetGuid

Gets the specified column as a Globally Unique Identified (GUID).

GetInt16

Gets the specified column as a 16-bit integer.

GetInt32

Gets the specified column as a 32-bit integer.

GetInt64

Gets the specified column as a 64-bit integer.

GetName

Gets the name of a column.

GetOrdinal

Gets the ordinal of a column whose name is specified.

GetSchemaTable

Returns a Data Table specifying the structure of DataReader.

GetSqlBinary

Gets the value of a given column as SqlBinary.

GetSqlBoolean

Gets the value of a given column as SqlBoolean.

GetSqlByte

Gets the value of a given column as SqlByte.

GetSqlDateTime

Gets the value of a given column as SqlDateTime.

GetSqlDecimal

Gets the value of a given column as SqlDecimal.

GetSqlDouble

Gets the value of a given column as SqlDouble.

GetSqlGuid

Gets the value of a given column as SqlGuid.

GetSqlInt16

Gets the value of a given column as SqlInt16.

GetSqlInt32

Gets the value of a given column as SqlInt32.

GetSqlInt64

Gets the value of a given column as SqlInt64.

GetSqlMoney

Gets the value of a given column as SqlMoney.

GetSqlSingle

Gets the value of a given column as SqlSingle.

GetSqlString

Gets the value of a given column as SqlString.

GetType

Gets the type of a current instance.

GetValue

Gets the value of a column.

IsDBNull

Gets the value to indicate whether a column contains nonexistent value.

Read

Advances the SqlCeDataReader to the next row.

Seek

Puts the SqlCeDataReader to a given record whose index is specified.

ToString

Converts the object to String and returns the string.


NOTE - The SQL Server Compact Edition Data Provider does not support the GetChar and Next ResultSet methods. These methods are supported by the SQL Server Data Provider.


Table 7.6 lists the SqlCeDataReader object properties.

Table 7.6 Commonly used Data Reader Object Properties

Properties

Property Description

Depth

Indicates the depth of nesting for the current row.

FieldCount

Gets the number of columns in a current row.

HasRows

Gets a value to indicate whether Data Reader contains at least one row.

HiddenFieldCount

Gets the number of a hidden field.

IsClosed

Indicates whether Data Reader is closed.

Item

Gets the value of a column in its native format.

RecordsEffected

Gets number of rows affected by insert, delete, update operation.

VisibleFieldCount

Gets number of fields that are not hidden.

Data Adapter

Data Adapter sits between the Connection object and DataSet and provides a gateway between the SQL Server Compact Edition Connection object and the DataSet object. Data Adapter receives the data from the Connection object and passes it to DataSet. After updates are done, it passes the changes back from the DataSet to the connection that updates the data in the database. Instead of looping through a set of rows with Data Reader, Data Adapter fills the DataSet with data.

DataAdapter contains four command objects:

  • SelectCommand

  • UpdateCommand

  • InsertCommand

  • DeleteCommand

SelectCommand is used to fill a DataSet. The other three command objects are used to transmit changes back to the data source.

You can create the Data Adapter at design time and at runtime.

Listing 7.5 demonstrates the usage of DataAdapter. The listing creates a command. It creates DataAdapter and DataSet. The Fill method loads the data from a database into DataTables of a DataSet.

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.

Listing 7.6 Data Row Object C#.NET

DataRow myDataRow = myDataSet.Tables[0].NewRow();
myDataRow ["Title"] = "New Title";
myDataRow["Price"] = 25;
myDataRow["Publisher"] = "My Publisher";
myDataRow["Description"] = "New Book Description";
myDataSet.Tables[0].Rows.Add(myDataRow);

Listing 7.6 Data Row Object VB.NET

Dim myDataRow As DataRow = myDataSet.Tables(0).NewRow()
myDataRow("Title") = "New Title"
myDataRow("Price") = 25
myDataRow("Publisher") = "My Publisher"
myDataRow("Description") = "New Book Description"
myDataSet.Tables(0).Rows.Add(myDataRow)
Using a DataSet Relationship

It is also possible to define a relationship between multiple DataTables in DataSet. To define a relationship between two DataTables you will use a DataRelation object. The DataRelation object is similar to a Foreign key in a database and it can enforce referential integrity.

For example, you might have two tables, Product and Part, that have a master–detail relationship. Using the DataRelation object, you can enable a cascading delete of all part rows when the product is deleted.

Using DataView Object

DataSet also provides a DataView object. A DataView object is a layer on top of the DataTable object. You can define multiple DataView objects on a single DataTable. DataView provides Databinding, sorting, filtering, etc. DataView can be created and configured both at design and runtime.

For example, you might have lot of tasks in DataTable. You can define views such as Task with status closed, Task with status open, etc.

Updating Data with DataSet

We have discussed the various ADO.NET objects that you can use to fetch data. Now we will describe the sequence and objects that you will use to manipulate the data.

Fetch the data from SQL Server Compact Edition database and the data is placed in local memory. Use the Fill method of Data Adapter to populate the tables of DataSet. You can update the data programmatically or by using Data Binding control. Once you have made changes in the local memory, use the Update method Adapter to send the changes back to the data source.

Listing 7.7 describes the usage of DataAdapter Update method. The Update method checks each row and executes the appropriate command—insert, update, or delete.

Listing 7.7 Update Data Source C#.NET

myDataAdapter.InsertCommand = myConnection.CreateCommand();
myDataAdapter.InsertCommand.CommandText = "INSERT INTO BOOKS (Title,Price, 
Publisher, Description) values (@Title,@Price,@Publisher, @Description)";
myDataAdapter.InsertCommand.Parameters.Add("@Title", SqlDbType.NChar, 100);
myDataAdapter.InsertCommand.Parameters.Add("@Price", SqlDbType.Money);
myDataAdapter.InsertCommand.Parameters.Add("@Publisher", SqlDbType.NChar, 50);
myDataAdapter.InsertCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 
2000);
// Insert a row
myDataAdapter.InsertCommand.Parameters["@Title"].Value = "New Title";
myDataAdapter.InsertCommand.Parameters["@Price"].Value = 25;
myDataAdapter.InsertCommand.Parameters["@Publisher"].Value = "My Publisher";
myDataAdapter.InsertCommand.Parameters["@Description"].Value = "New Book Description";
myDataAdapter.Update(myDataSet);

Listing 7.7 Update Data Source VB.NET

myDataAdapter.InsertCommand = myConnection.CreateCommand()
myDataAdapter.InsertCommand.CommandText = 
  "INSERT INTO BOOKS (Title,Price, Publisher, Description) 
   values (@Title,@Price,@Publisher, @Description)"
myDataAdapter.InsertCommand.Parameters.Add
      ("@Title", SqlDbType.NChar, 100)
myDataAdapter.InsertCommand.Parameters.Add("@Price", SqlDbType.Money)
myDataAdapter.InsertCommand.Parameters.Add(
      "@Publisher", SqlDbType.NChar, 50)
myDataAdapter.InsertCommand.Parameters.Add
    ("@Description", SqlDbType.NVarChar, 2000)
myDataAdapter.InsertCommand.Parameters("@Title").Value = "New Title"
myDataAdapter.InsertCommand.Parameters("@Price").Value = 25
myDataAdapter.InsertCommand.Parameters("@Publisher").Value = 
    "My Publisher"
myDataAdapter.InsertCommand.Parameters("@Description").Value = 
    "New Book Description"
myDataAdapter.Update(myDataSet)

The Update method calls the AcceptChanges method automatically.


NOTE - The DataAdapter Update method is an easy way of saving data. However, Update method is not always the best choice. Use command objects to update the database when you are not using DataAdapter or you need to save changes in a particular order.


Considering ADO.NET Additional Features

Now that we have discussed the various ADO.NET objects in Data Provider and DataSet categories, we will explore the features of ADO.NET that enable you to build multitier applications. ADO.NET is connectionless, based on XML, and provides data binding.

Understanding Data Binding

Binding the ADO.NET object to the UI controls on your application creates a link between control and the data source. The main advantage of data binding is that a developer can focus on application writing and can use a built-in data binding mechanism to get data, store it locally, and display it in UI control. If the data value changes, then the corresponding changes are reflected in UI.

You can have two text boxes—First Name and Last Name—in your application. You can bind these text boxes to columns in your tables to display the values. To bind the values, specify the Text property in Data Bindings as shown in Figure 7.3.

Figure 7.3
Data Bindings Properties

Specify the data source and Table and Column names from where the Text property will bind as shown in Figure 7.4.

Figure 7.4
Add Project Data Source

Understanding Connectionless

ADO.NET is connectionless and also enables data binding. This means that the ADO.NET object does not require live connections to a data source. You can connect to a data source to get the data. You can disconnect from a data source after getting the data and can manipulate the data offline. Later on, you can reestablish the connection and update the local store with data. ADO.NET has mechanisms to manage the details of data versions and status. It uses these details to merge the changes to a local store.

Understanding Updateable Cursor

In the beginning of the chapter we discussed SqlCeConnection, SqlCeCommand, SqlCeDataAdapater, and SqlCeDataReader classes. You will always use SqlCeConnection to connect to a database. Once you are connected, you can use SqlCeDataReader to fetch and display data in the application. You can also use a combination of Data Adaptor and DataSet to fetch and display data.

SqlCeDataReader provides a forward read only cursor. SqlCeDataSet allows you to update data but first it loadsgets the data in local memory. SqlCeResultSet is a new player in this domain. Result Set class is only available for the SQL Server Compact Edition database. There is no corresponding class for the SQL Server database.

SqlCeResultSet

SQL Server Compact Edition provides an updatable scrollable cursor. SQLCEResultSet derives from SQLCeDataReader and it is updatable, scrollable, and bindable. Using this cursor, you can get the performance of Data Reader and functionality similar to DataSet. It also requires less memory as it does not perform double buffering of the database as DataSet does.


NOTE - In a traditional central database, the data is fetched from a central server into DataSet. SQL Server Compact Edition is used as an embedded database on a mobile device or desktop. As the database is already in memory, there is no need to make additional buffering of data in memory.


In .NET Framework, all DML operations are done through a query processor. As the database is on a local store, SQLCEResultset provides DML operations against base tables as well as a Query Processor Updatable cursor. Table 7.11 describes the methods exposed by SQLCeResultSet.

Table 7.11 Commonly Used SQL Server Compact Edition ResultSet Object Methods

Method

Method Description

Close

Closes the Data Reader object.

Equals

Returns TRUE if two object instances are equal.

GetBoolean

Gets the value of a specified column as Boolean.

GetByte

Gets the value of a specified column as Byte.

GetBytes

Reads a stream of bytes from a column offset.

GetChars

Reads a stream of characters from a column offset.

GetData

Returns a DBDataReader object.

GetDataTypeName

Gets the name of a data source type.

GetDateTime

Gets the specified column as DateTime object.

GetDecimal

Gets the specified column as Decimal object.

GetDouble

Gets the specified column as Double object.

GetFieldType

Gets the type of the object.

GetFloat

Gets the specified column as Float object.

GetGuid

Gets the specified column as Globally unique identified (GUID).

GetInt16

Gets the specified column as a 16-bit integer.

GetInt32

Gets the specified column as a 32-bit integer.

GetInt64

Gets the specified column as a 64-bit integer.

GetName

Gets the name of a column.

GetOrdinal

Gets the ordinal of a column whose name is specified.

GetProviderSpecifiedFieldType

Gives an object corresponding to a provider-specific field type.

GetProviderSpecificValue

Gets the value of a specified column.

GetProviderSpecificValues

Gets provider-specific columns for the current row.

GetSchemaTable

Returns a Data Table specifying the structure of DataReader.

GetSqlBinary

Gets the value of a given column as SqlBinary.

GetSqlBoolean

Gets the value of a given column as SqlBoolean.

GetSqlByte

Gets the value of a given column as SqlByte.

GetSqlDateTime

Gets the value of a given column as SqlDateTime.

GetSqlDecimal

Gets the value of a given column as SqlDecimal.

GetSqlDouble

Gets the value of a given column as SqlDouble.

GetSqlGuid

Gets the value of a given column as SqlGuid.

GetSqlInt16

Gets the value of a given column as SqlInt16.

GetSqlInt32

Gets the value of a given column as SqlInt32.

GetSqlInt64

Gets the value of a given column as SqlInt64.

GetSqlMetaData

Returns the MetaData information for a column.

GetSqlMoney

Gets the value of a given column as SqlMoney.

GetSqlSingle

Gets the value of a given column as SqlSingle.

GetSqlString

Gets the value of a given column as SqlString.

GetType

Gets the type of current instance.

GetValue

Gets the value of a column.

GetValues

Gets array of all the fields for a record.

Insert

Inserts the SqlCeUpdateableRecord.

IsDBNull

Gets the value to indicate whether a column contains a nonexistent value.

IsSetAsDefault

Returns TRUE if the field at a specified position will use the underlying default value.

Read

Advances the SqlCeDataReader to the next row.

ReadAbsolute

Moves the reader to a specified record.

ReadFirst

Positions the reader at first record.

ReadLast

Positions the reader at the last record.

ReadPrevious

Positions the reader to the previous cursor.

ReadRelative

Moves the reader from the current position.

ReferenceEquals

Returns TRUE if the specified object instances are the same.

Seek

Puts the reader to a given record whose index is specified.

SetBoolean

Sets the value of a given column with passed Boolean value.

SetBye

Sets the value of a given column with passed Bye value.

SetBytes

Sets the value of a given column with set of bytes in a buffer.

SetChar

Sets the value of a given column with passed Char value.

SetChars

Sets the value of a given column with a set of characters in a buffer.

SetDateTime

Sets the value of a given column with passed DateTime value.

SetDecimal

Sets the value of a given column with passed Decimal value.

SetDefault

Sets the given column with a Default value.

SetFloat

Sets the value of a given column with passed Float value.

SetGuid

Sets the value of a given column with passed Guid value.

SetInt16

Sets the value of given column with passed SetInt16 value.

SetInt32

Sets the value of a given column with passed SetInt32 value.

SetInt64

Sets the value of a given column with passed SetInt64 value.

SetSqlBinary

Sets the value of a given column with passed SqlSqlBinary value.

SetSqlBoolean

Sets the value of a given column with passed SqlBoolean value.

SetSqlByte

Sets the value of a given column with passed SqlByte value.

SetSqlDateTime

Sets the value of a given column with passed SqlDateTime value.

SetSqlDecimal

Sets the value of a given column with passed SqlDecimal value.

SetSqlDouble

Sets the value of a given column with passed SqlDouble value.

SetSqlGuid

Sets the value of a given column with passed SqlGuid value.

SetSqlInt16

Sets the value of a given column with passed SqlInt16 value.

SetSqlInt32

Sets the value of a given column with passed SqlInt32 value.

SetSqlInt64

Sets the value of a given column with passed SqlInt64 value.

SetSqlMoney

Sets the value of a given column with passed SqlMoney value.

SetSqlSingle

Sets the value of a given column with passed SqlSingle value.

SetSqlString

Sets the value of a given column with passed SqlString value.

SetString

Sets the value of a given column with passed string value.

SetValue

Sets the value of a given column with value.

SetValues

Sets values of all fields in record.

ToString

Converts the object to a String and returns the string.

Update

Updates changes on the current row on the database.


NOTE - The NextResult method is not supported.


Table 7.12 describes the usage of SqlCeResultSet properties.

Table 7.12 Commonly Used SQL Server Compact Edition ResultSet Object Properties

Properties

Property Description

Depth

Indicates the depth of nesting for the current row.

FieldCount

Gets the number of columns in the current row.

HasRows

Gets a value to indicate whether Data Reader contains at least one row.

HiddenFieldCount

Gets the number of hidden fields.

IsClosed

Indicates whether Data Reader is closed.

Item

Gets the value of a column in its native format.

RecordsEffected

Gets the number of rows affected by insert, delete, update operations.

ResultSetView

Useful to bind a UI control with SqlCeResult.

Scrollable

Returns TRUE if SqlCeResultSet is scrollable. Default value is FALSE.

Sensitivity

The property determines the sensitivity of ResultSet. Sensitivity specifies whether ResultSet is aware of changes to the data source.

Updatable

Returns TRUE if SqlCeResultSet is updatable.

VisibleFieldCount

Gets the number of fields that are not hidden.

Listing 7.8 SqlCeResultSet C#

//Fetch Data using Result Set
      SqlCeCommand myCmd = myConn.CreateCommand();
      myCmd.CommandText = "SELECT BookID, Title FROM Books";
      myResultSet = myCmd.ExecuteResultSet(ResultSetOptions.Updatable | 
ResultSetOptions.Scrollable);
      while (myResultSet.Read())
      {
        listBox1.Items.Add(myResultSet.GetInt32(0).ToString() + 
" " + myResultSet.GetString(1));
      }
// Insert new record
        SqlCeUpdatableRecord myRec = myResultSet.CreateRecord();
        myRec.SetString(1, txtTitle.Text);
        myResultSet.Insert(myRec);
//Update Record
        myResultSet.SetString(1, txtTitle.Text.ToString());
        myResultSet.Update();
        txtTitle.Text = "";
//Delete Record
        myResultSet.Delete();

Listing 7.8 SqlCeResultSet VB.NET

' INSERT RECORD
      Dim myRec As SqlCeUpdatableRecord = myResultSet.CreateRecord
      myRec.SetString(1, txtTitle.Text)
      myResultSet.Insert(myRec)
      txtTitle.Text = ""
' UPDATE RECORD
      myResultSet.SetString(1, txtTitle.Text.ToString())
      myResultSet.Update()
      txtTitle.Text = ""
' DELETE RECORD
    myResultSet.Delete()

SqlCeUpdateableRecord

You will use SQLCeUpdateableRecord with SqlCeResultSet. The SqlCeUpdateableRecord object represents a row. The SqlCeResultSet consists of one or more SqlCeUpdateableRecord objects. Table 7.13 shows the methods exposed by SqlCeUpdatableRecord. Table 7.14 describes the properties exposed by SqlCeUpdatableRecord.

Table 7.13 Commonly Used SqlCeUpdatable Object Public Methods

Method

Method Description

Equals

Returns TRUE if the specified two object instances are equal.

GetFieldType

Returns the CLR data type for a given field.

GetOrdinal

Return the ordinal for a given field.

GetType

Gets the type of a current instance.

GetValue

Returns the value of a given record.

GetValues

Returns the value of all fields in a record.

IsDBNull

Returns TRUE if field is NULL.

IsSetAsDefault

Returns TRUE if a given field is marked to use the Default value.

ReferenceEquals

Returns TRUE if a specified instance is the same as a given instance.

ToString

Returns a string having the name of the object.

Table 7.14 Commonly Used SqlCeUpdatableRecord Public Properties

Property

Property Description

FieldCount

Number of fields in a record.

Updatable

Returns TRUE if field is updatable.

SqlCeEngine

The SqlCeEngine class represents the SQL Server Engine object. This class cannot be inherited. You can use this class to create an instance of an SQL Server Compact Edition database. You can also use this class to Compact, Repair or Shrink SQL Server Compact Edition Database programmatically. Table 7.15 and Table 7.16 provide details of SqlCeEngine method and properties.

Table 7.15 Commonly Used SqlCeEngine Object Public Methods

Method

Method Description

Compact

This method reclaims the wasted space created by fragmentation. You should have a temporary space available to compact the database. The Compact mechanism creates a new database file and copies the database content to a new file.

CreateDatabase

This method creates an empty SQL Server Compact Edition database file.

Equals

Returns TRUE if the specified object instances are equal.

Repair

This method repairs a corrupted SQL Server Compact Edition database file.

Shrink

Similar to compact, Shrink also reclaims the wasted space and moves empty pages toward the end of a file and truncates the file.

ToString

Returns a string having the name of the component.

Verify

This method validates the SQL Server Compact Edition database. The Verify method recalculates the checksum for all pages in the database and compares the checksum with the actual value to validate it.

Table 7.16 Commonly Used SqlCeEngine Object Public Properties

Method

Method Description

LocalConnectionString

Gets or sets a connection String to the SQL Server Compact Edition database.

Listing 7.9 SqlCeEngine C#

      if (File.Exists("MyDatabase.sdf"))
        File.Delete("MyDatabase.sdf");
      string myConnectionStr = "Data Source = MyDatabase.sdf;";
      SqlCeEngine engine = new SqlCeEngine(myConnectionStr);
      engine.CreateDatabase();

Listing 7.9 SqlCeEngine VB.NET

      If File.Exists("MyDatabase.sdf") Then
      File.Delete("MyDatabase.sdf")
    End If
    Dim myConnectionStr As String = "Data Source = MyDatabase.sdf;"
    Dim myEngine As SqlCeEngine
    myEngine = New SqlServerCe.SqlCeEngine()
    myEngine.LocalConnectionString = myConnectionStr
    myEngine.CreateDatabase()    

Understanding Parameterized Queries

SQL Server Compact Edition supports parameterized queries. Parameterized queries give better performance as the queries are compiled once. Each time you execute an SQL Statement through ADO.NET, SQL Server Compact Edition will create a query plan. If you are executing the same query continuously with varying parameter values, you should consider using SqlCeParameterCollection. Parameterized queries also help protect against Sql Injections. You will create an SqlCeParameterCollection and add various parameters using the SqlCeParameter class. Parameterized statements will compile the query once and execute the same compiled plan on each execution.


Note - Preparing a command has overhead. If a statement is executed only once, there is no need to prepare it.

ExecuteScalar, ExecuteReader, or ExecuteNonQuery commands can be prepared.


SqlCeParameterCollection

The SqlCeParameterCollection object contains all SqlCeParameters and their respective mappings for SqlCeCommand. The number of parameter placed in SqlCeCommand should match with the parameters in the parameters collection.

Table 7.17 Commonly Used SqlCeParameterCollection Object Public Methods

Method

Method Description

Add

Adds an SqlCeParameter object to anSqlCeCommand object.

AddRange

Adds an array of SqlCeParameter objects to anSqlCeCommand object.

AddWithValue

Adds a new SqlCeParameter object to an SqlCeCommand object. The method also sets the value of a new SqlCeParameter object.

Clear

Removes all SqlCeParameters from SqlCeParameterCollection.

Contains

Returns TRUE if the SqlCeParameter exists in an SqlCeParameterCollection.

CopyTo

Copies SqlCeParameter objects from SqlCeParameterCollection to an array.

Equals

Returns TRUE if specified two object instances are equal.

GetType

Gets the type of an object instance.

IndexOf

Gets the index location of a specified SqlCeParameter in an SqlCeParameter collection.

Insert

Adds the SqlCeParameter in an SqlCeParameterCollection at a specified location.

ReferenceEquals

Returns TRUE if the specific object instances are the same.

Remove

Removes the given SqlCeParameter from SqlCeParameterCollection.

RemoveAt

Removes the SqlCeParameter from a specified position in SqlCeParameterCollection.

ToString

Returns a string having the name of the object.

Table 7.18 Commonly Used SqlCeParameterCollection Object Public Properties

Property

Property Description

Count

Returns the count of SqlCeParameter in the SqlCeParameterCollection.

Item

Gets or sets the SqlCeParameter with a given value.

SqlCeParameter

Use the SqlCeParameter class to create an instance of parameters to be used in SQL Server Compact Edition queries. You can populate the SQLCeParameter object associated with the SqlCeCommand.

First define the parameters in an SQL query with a parameter name. Table 7.19 and Table 7.20 define the SqlCeParameter object methods and properties. Then use the Add method of the parameter property to add the value of the parameter. The Add method accepts the name of the parameter and the value of the parameter. Add values of all parameters one by one.

Table 7.19 Commonly Used SqlCeParameter Object Public Methods

Method

Method Description

Equals

Returns TRUE if the specified two object instances are equal.

GetType

Gives the type of a current instance.

ReferenceEquals

Returns TRUE if the specified instance is the same as the given instance.

ResetDbType

Resets the type of SqlCeParameter.

ToString

Returns a string having the name of the component.

Use the Prepare method of SqlCeCommand to prepare the execution plan. Finally, execute the query.

Table 7.20 Commonly Used SqlCeParameter Object Public Properties

Property

Property Description

DbType

Gets and sets the DbType of the parameter.

IsNullable

Gets or sets a value to specify whether a parameter can have NULL values.

ParameterName

Gets or sets the name of the SqlCeParameter.

Precision

Gets or sets the maximum number of digits to represent Value Property.

Scale

Gets or sets the number of decimal places.

Size

Gets or sets the maximum data length.

SourceColumn

Gets or sets the source column corresponding to DataSet.

SourceVersion

Gets or sets the version of row. The possible values are Current, Default, Original, and Proposed.

SqlDbType

Gets or sets the SqlDbType parameter.

Value

Gets or sets the parameter value.


Note - The SQLCeParameter class cannot be inherited.

SQL Server CE 2.0 does not support named parameters. Named parameter support is added to SQL Server Compact Edition 3.x.

The same command object needs to be used for running all queries. If not, Destroy the Object and Create New Object queries will be compiled again.


Listing 7.10 describes the usage of a parameterized query. The code opens a connection and creates a command object. The code sets the CommandText property with SQL statements. Instead of defining the actual values, you define the parameters in command text. Then the code adds values to the parameter collection using the Add method. In conclusion, you prepare the command and execute the query.

Listing 7.10 ParameterQuery C#

SqlCeConnection myConn = new SqlCeConnection("DataSource=\\Program Files\\ 
ParameterQuery\\AppDatabase1.sdf");
      
SqlCeCommand myCmd = myConn.CreateCommand();
      
 myCmd.CommandType = CommandType.Text;
 myCmd.CommandText = "INSERT INTO BOOKS 
 (Title,Price, Publisher, Description) 
values (@Title,@Price,@Publisher, @Description)";
      
 myCmd.Parameters.Add("@Title", SqlDbType.NChar, 100);
 myCmd.Parameters.Add("@Price", SqlDbType.Money);
 myCmd.Parameters.Add("@Publisher", SqlDbType.NChar, 50);
 myCmd.Parameters.Add("@Description", SqlDbType.NVarChar, 2000);
      
 myConn.Open();
 myCmd.Prepare();
     
 // First Insert  
 myCmd.Parameters["@Title"].Value = "Integration Services 2005";
 myCmd.Parameters["@Price"].Value = 35;
 myCmd.Parameters["@Publisher"].Value = "My Publisher";
 myCmd.Parameters["@Description"].Value = "Insert 1";
 myCmd.ExecuteNonQuery();
 // Second Insert
 myCmd.Parameters["@Title"].Value = "Analysis Services 2005";
 myCmd.Parameters["@Price"].Value = 45;
 myCmd.Parameters["@Publisher"].Value = "My Publisher";
 myCmd.Parameters["@Description"].Value = "Insert 2";
 myCmd.ExecuteNonQuery();
 // Third Insert
 myCmd.Parameters["@Title"].Value = "Reporting Services 2005";
 myCmd.Parameters["@Price"].Value = 55;
 myCmd.Parameters["@Publisher"].Value = "My Publisher";
 myCmd.Parameters["@Description"].Value = "Insert 3";
 myCmd.ExecuteNonQuery();
 myConn.Close();

Listing 7.10 ParameterQuery Visual Basic .NET

Dim myConn As SqlCeConnection = New SqlCeConnection
("DataSource=\Program Files\ParameterQuery\AppDatabase1.sdf")
Dim myCmd As SqlCeCommand = myConn.CreateCommand()
myCmd.CommandType = Data.CommandType.Text
myCmd.CommandText = "INSERT INTO BOOKS 
(Title,Price, Publisher, Description) 
values (@Title,@Price,@Publisher,@Description)"
    
myCmd.Parameters.Add("@Title", SqlDbType.NChar, 100)
myCmd.Parameters.Add("@Price", SqlDbType.Money)
myCmd.Parameters.Add("@Publisher", SqlDbType.NChar, 50)
myCmd.Parameters.Add("@Description", SqlDbType.NVarChar, 2000)
myConn.Open()
myCmd.Prepare()
' First Insert  
myCmd.Parameters("@Title").Value = "Integration Services 2005"
myCmd.Parameters("@Price").Value = 35
myCmd.Parameters("@Publisher").Value = "My Publisher"
myCmd.Parameters("@Description").Value = "Insert 1"
myCmd.ExecuteNonQuery()
' Second Insert
myCmd.Parameters("@Title").Value = "Analysis Services 2005"
myCmd.Parameters("@Price").Value = 45
myCmd.Parameters("@Publisher").Value = "My Publisher"
myCmd.Parameters("@Description").Value = "Insert 2"
myCmd.ExecuteNonQuery()
'// Third Insert
myCmd.Parameters("@Title").Value = "Reporting Services 2005"
myCmd.Parameters("@Price").Value = 55
myCmd.Parameters("@Publisher").Value = "My Publisher"
myCmd.Parameters("@Description").Value = "Insert 3"
myCmd.ExecuteNonQuery()
myConn.Close()

Understanding Transactions

Transaction is used when you want multiple SQL statements to act as one unit. The set of statements are committed if all queries execute successfully. SQL Server Compact Edition supports the transactions.

SqlCeTransaction

The SqlCeTransaction object provides the ability to pack multiple SQL statements and works with SqlCeConnection and the SqlCeCommand object. You use the BeginTransaction method of the SqlCeConnection object to start the transaction. After the transaction is initiated, any subsequent query execution happens as part of the transaction. Tables 7.21 and 7.22 describe the commonly used SqlCeTransaction object methods and properties.

Table 7.21 Commonly Used SqlCeTransaction Object Public Methods

Method

Method Description

Commit

Commits the transaction.

Equals

Returns TRUE if the specified two object instances are equal.

GetType

Gets the type of the object instance.

ReferenceEquals

Returns TRUE if the specified object instances are the same.

Rollback

Rolls back a transaction.

ToString

Returns a string having the name of the object.

Table 7.22 Commonly Used SqlCeTransaction Object Public Properties

Property

Property Description

Connection

Connection object associated with transaction.

Isolation Level

Gives the Isolation level for a transaction. The possible values are ReadCommitted, ReadRepeatable, and Serializable.

SqlCeTransactionInProgressException

The SqlCeTransactionInProgressException object is created when a transaction is already in progress and you attempt to modify a database. Tables 7.23 and 7.24 describe the commonly used SqlCeTransactionInProgressException object methods and properties.

Table 7.23 Commonly Used SqlCeTransactionInProgressException Object Public Methods

Method

Method Description

Equals

Returns TRUE if the specified two object instances are equal.

GetBaseException

Returns the root cause Exception.

GetType

Gives the type of current instance.

ReferenceEquals

Returns TRUE if the specified instance is the same as a given instance.

ToString

Returns a string having the name of the component.

Table 7.24 Commonly Used SqlCeTransactionInProgressException Object Public Properties

Property

Property Description

Data

Gets a collection of key/value pairs to provide additional information.

Errors

Gets a collection of SqlCeError objects.

HelpLink

Gets or sets a link to help a file corresponding to the exception.

InnerException

Gets the inner exception of the current exception.

Message

Provides the text corresponding to the first SqlCeError.

NativeError

Gives the native error number of the first SqlCeError

Source

Gets the name of the OLE DB Provider that generated the error.

StackTrace

Provides a string representation of the call stack.

TargetSite

Gets the method that has thrown the current exception.

Transaction is closed by Commit or Rollback.

Listing 7.11 creates and opens a connection to the SQL Server Compact Edition database. A Data Command object is created.

The transaction is completed in three steps: (1) the transaction is assigned to command; (2) the database commands are executed; and (3) the transaction is closed by calling the Commit method.

Listing 7.11 SqlCeTransaction C#

// C#.NET 
SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Mobile\\Northwind.sdf;");
myConnection.Open();
SqlCeCommand myCmd = myConnection.CreateCommand();
// Transaction steps
SqlCeTransaction myTrans = myConnection.BeginTransaction ();
myCmd.Transaction = myTrans;
// do Database updated here
...
...
...
myTrans.Commit();

Listing 7.11 SqlCeTransaction VB.NET

    Dim myConnection As SqlCeConnection
    myConnection = New SqlCeConnection("Data Source=\Program Files\
SqlCeExceptionError\Northwind.sdf;")
    myConnection.Open()
    Dim myCmd As SqlCeCommand = myConnection.CreateCommand()
    Dim myTrans As SqlCeTransaction
    myTrans = myConnection.BeginTransaction()
    myCmd.Transaction = myTrans
    ' do database updates here
    '...
    '...

    myTrans.Commit()

Using Error and Exceptional Classes

Things can go wrong when you access data using ADO.NET. SQL Server Compact Edition Provider throws SqlCeException in case of error.

You will be using a try-catch-finally block to trap and catch exceptions. If an exception is thrown inside the try block, the code in the catch block gets executed. In the catch block, you can handle the exception. The code in the finally block always executes regardless of whether the exception is thrown into the try block.


NOTE - You can also write a custom Data Access component that catches SqlCeException and throws a more specific exception.


SqlCeException

The SqlCeException object is created when Data Provider for SQL Server Compact Edition gets an error generated by the SQL Server Compact Edition engine. The SqlCeException class contains at least one instance of SqlCeError. Tables 7.25 and 7.26 list the SqlCeException class methods and properties.

Table 7.25 Commonly Used SqlCeException Object Public Methods

Method

Method Description

Equals

Returns TRUE if the specified two object instances are equal.

GetBaseException

Returns the root cause exception.

GetType

Gives the type of current instance.

ReferenceEqual

Returns TRUE if the specified instance is the same as a given instance.

ToString

Returns a string having the name of the component.

Table 7.26 Commonly Used SqlCeException Object Public Properties

Property

Property Description

Data

Gets a collection of key/value pairs to provide additional information.

Errors

Gets a collection of SqlCeError objects.

HelpLink

Gets or sets a link to help the file corresponding to an exception.

InnerException

Gets the inner exception of the current exception.

Message

Provides the text corresponding to the first SqlCeError.

NativeError

Gives the native error number of the first SqlCeError

Source

Gets the name of the OLE DB Provider that generated the error.

StackTrace

Provides a string representation of call stack.

TargetSite

Gets the method that has thrown the current exception.


NOTE - The SqlCeException class cannot be inherited.


SqlCeError

The SqlCeError class collects error and warning information from the data source and returns it to the calling program. Tables 7.27 and 7.28 describe the methods and properties of the SqlCeError class.

Table 7.27 Commonly Used SqlCeError Object Public Methods

Method

Method Description

Equals

Returns TRUE if the specified object instances are equal.

GetType

Gives the type of current instance.

ToString

Returns a string having the name of the component.

Table 7.28 Commonly Used SqlCeError Object Public Properties

Property

Property Description

ErrorParameters

Gets the last three error parameters.

HResult

Return the HResult value for an error.

Message

Gets the error message that specifies the error.

NativeError

Gets the native error number for SQL Server Compact Edition error.

Source

Gets the source of the error.


NOTE - The SqlCeError class cannot be inherited.


SqlCeErrorCollection

The SqlCeErrorCollection class collects all errors generated by the SQL Server Compact Edition Data Provider. SQLCeErrorCollection will have at least one instance of the SqlCeError class and cannot be inherited.

Tables 7.29 and 7.30 describe the methods and properties exposed by the SqlCeErrorCollection class.

Table 7.29 Commonly used SqlErrorCollection Object Public Methods

Method

Method Description

CopyTo

Copies the SqlCeCollection object into an array.

Equals

Returns TRUE if the specified two object instances are equal.

GetType

Gives the type of current instance.

ReferenceEquals

Returns TRUE if the specified instance is the same as a given instance.

ToString

Returns a string having the name of a component.

Table 7.30 Commonly Used SqlError Object Public Properties

Property

Property Description

Count

Gets the count of SqlCeError objects in a collection.

Item

Gets the specific error.


NOTE - The SqlCeErrorCollection class cannot be inherited.


Listing 7.12 describes the usage of the SqlCeException class. The listing uses the try-catch-finally block. The catch block displays all the error messages in an exception. In the finally block, connection to the database is closed.

Listing 7.12 SqlCeException C#

try
{
SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Mobile\\Northwind.sdf;");
myConnection.Open();
SqlCeCommand myCommand = myConnection.CreateCommand();
SqlCeDataReader myDataReader ;
myCommand.CommandText = "Products";
myCommand.CommandType = CommandType.TableDirect;
myDataReader = myCommand.ExecuteReader();
while (myDataReader.Read()){
listbox1.Items.Add(myDataReader.GetValue(0));
}
myDataReader.Close();
}
catch (SqlCeException MyExp)
{
  foreach (SqlCeError MyError in MyExp.Errors)
  {
    MessageBox.Show(MyError.Message);
  }
}
finally
{
  myConnection.Close();
}
}

Listing 7.12 SqlCeException VB.NET

    Dim myConnection As SqlCeConnection
    Try

      myConnection = New SqlCeConnection("Data Source=\Program Files\ 
SqlCeExceptionError\Northwind.sdf;")
      myConnection.Open()
      Dim myCommand As SqlCeCommand = myConnection.CreateCommand()
      myCommand.CommandText = "Products"
      myCommand.CommandType = CommandType.TableDirect
      Dim myDataReader As SqlCeDataReader = myCommand.ExecuteReader()
      While myDataReader.Read()
        listbox1.Items.Add(myDataReader.GetValue(0))
      End While
      myDataReader.Close()

    Catch MyExp As SqlCeException
      Dim myError As SqlCeError
      For Each myError In MyExp.Errors
        MessageBox.Show(myError.Message)
      Next
    Finally
      myConnection.Close()
    End Try

Building Applications

This section explains the following types of solutions that you can build using the SQL Server Compact Edition data provider (System.Data.SqlServerCe) and the SQL Server data provider (System.Data.Sqlclient ) in .NET Compact Framework:

  • Desktop applications using local cache in an SQL Server Compact Edition database.

  • Smart device applications using local cache in an SQL Server Compact Edition database.

  • Smart device applications connected to a backend SQL Server using SQL Server Data Provider (SqlClient namespace).

  • Smart device applications that use an SQL Server Compact Edition database as a local store and occasionally connect to a backend SQL Server to synchronize data.


NOTE - You should use the .NET Framework provider for connecting desktop applications to an SQL Server.


Developing Desktop Applications with SQL Server Compact Edition

SQL Server Compact Edition is ideal for many desktop-based applications where an application is needed to store data locally. For example, Outlook stores email in a .pst file. In the future, an email client can download data to an SQL Server Compact Edition database file (.sdf) instead of using a .pst file. The advantage of using a .sdf file is that you can use database relational capabilities to find database information. The .sdf file can also be transferred to a mobile device where emails can be viewed.

Create an Email .sdf File

To demonstrate this scenario, the following example uses an Email.sdf file that contains emails.

Column

Data Type

Nullability

Description

ID

bigint

Not Null

Email identity column Primary key

Sent

datetime

Not Null

Date/time that email was sent

Sub

Nvarchar (100)

Null

Email subject

Content

Nvarchar (2000)

Null

Email message body

Flag

bit

Null

A flag to indicate an urgent email

FromEmail

Nvarchar (100)

Null

Email address of person who sent email

ToEmail

nvarchar (100)

Null

Email address in the To list

CCEmail

nvarchar (100)

Null

Email address in the CC list

BccEmail

nvarchar (100)

Null

Email address in Bcc list

Create the Emails table in the Email database. Fill the table with a set of emails.

Store the Email.sdf file and note down the path it uses in a Windows application. In the following example, the Email.sdf file path is C:\Database\Email\Email.sdf.

Create a Windows Project

  1. Start Visual Studio 2005 from the Start menu.

  2. Click File | New | Project.

  3. Click on Visual C# or Visual Basic.

  4. Click on Windows Project and select Windows Application.

  5. Enter the Name and Location of the project.

  6. Click OK on the New Project dialog box.

  7. Design the user interface for an email project as shown in Figure 7.5

Figure 7.5
Desktop Email Application

Add a reference to the System.Data.SqlServerCe namespace in the project. In Solution Explorer, right click on References and select the Add Reference option. In the Add Reference dialog box, select System.Data.SqlServerCe dll.

Listing 7.9 has a complete code for this exercise. The code has the following methods:

  • The Form_Load method loads the form and sets the initial query to select all emails.

  • The btnFind_Click method triggers when a user clicks the Find button. This method opens a connection to the Email database and calls the FillEmailList method.

  • The FillEmailList method executes a query given by the user. The result set of the query is shown back to the user in the List box.

Listing 7.13 Desktop Email C#.NET

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace Email
{
  public partial class Form1 : Form
  {
    SqlCeConnection myConn;
    public Form1()
    {
      InitializeComponent();     
     }
    private void Form1_Load(object sender, EventArgs e)
    {
      txtSQL.Text = "Select Sent, FromEmail, ToEmail, Sub, Content From Emails";
      // Use the DataDirectory macro to get database path.
      AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\Database\Email");
    }
    private void btnFind_Click(object sender, EventArgs e)
    {
      myConn = new SqlCeConnection
       ("Data Source=|DataDirectory|\\Email.sdf;");
      myConn.Open();
      FillEmailList();
      myConn.Close();
    }
    private void FillEmailList()
    {
      SqlCeDataReader myEmailDataReader;
      // Opens a data reader and fetch the rows
      listEmail.Items.Clear();
      SqlCeCommand myCmd = myConn.CreateCommand();
      myCmd.CommandText = txtSQL.Text;
      myEmailDataReader = myCmd.ExecuteReader();
      while (myEmailDataReader.Read())
      {
        listEmail.Items.Add
           (myEmailDataReader.GetDateTime(0).ToShortDateString() + " "
           + myEmailDataReader.GetSqlString(1) + " "
           + myEmailDataReader.GetSqlString(2) + " "
           + myEmailDataReader.GetSqlString(3) + " "
           + myEmailDataReader.GetSqlString(4) + " "
           );       
      }
    }
  }
}

Listing 7.13 Desktop Email VB.NET

Imports system.Data
Imports System.Data.SqlServerCe
Public Class Form1
  Dim myConn As SqlCeConnection
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) 
Handles MyBase.Load
    'Set the initial query.
    txtSQL.Text = _
      "Select Sent, FromEmail, ToEmail, Sub, Content From Emails"
    ' Use the DataDirectory macro to get database path.
    AppDomain.CurrentDomain.SetData _
      ("DataDirectory", "C:\Database\Email")
  End Sub
  Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles btnFind.Click
    'Open a connection to local .sdf file that contains Emails
    myConn = New SqlCeConnection _
     ("Data Source=|DataDirectory|\\Email.sdf;")

    myConn.Open()
    'Find the matching emails and fill the list box.
    FillEmailList()
    'Close the connection
    myConn.Close()
  End Sub
  Private Sub FillEmailList()
    Dim myEmailDataReader As SqlCeDataReader
    Dim myCmd As SqlCeCommand
    'Opens a data reader and fetch the rows
    listEmail.Items.Clear()
    myCmd = myConn.CreateCommand()
    'Read the query types by user
    myCmd.CommandText = txtSQL.Text
    myEmailDataReader = myCmd.ExecuteReader()
    'Display the matching rows in a listbox.
    While (myEmailDataReader.Read())
      listEmail.Items.Add( _
      (myEmailDataReader.GetDateTime(0).ToShortDateString() + " " _
           + myEmailDataReader.GetSqlString(1) + " " _
           + myEmailDataReader.GetSqlString(2) + " " _
           + myEmailDataReader.GetSqlString(3) + " " _
           + myEmailDataReader.GetSqlString(4) + " " _
           ))
    End While
  End Sub  
End Class

To execute the email program, type an SQL statement to find an email and press the Find button. The emails matching the specified criteria will be displayed in a list box as shown in Figure 7.5.

Developing Disconnected Smart Device Applications with SQL Server Compact Edition

You will use the same code to develop an email application for a mobile device. Create a Smart device project using a Windows mobile platform. First develop a user interface for a mobile device application. Copy the code used in Listing 7.14 into your device project. Change the path in the code to point to the database file.

Listing 7.14 Device Email C#.NET

  myConn = new SqlCeConnection
   ("DataSource=\\Program Files\\Email_Device\\Email.sdf") 

Listing 7.14 Device Email VB.NET

   myConn = New SqlCeConnection _
    ("DataSource=Program Files\Email_Device\Email.sdf")

Using these simple steps, you can use an email application on a Smart device as well.

Transfer the Email.sdf file that you created in the previous section to your Smart device and note down the path it uses in the device application. In the following example, the Email.sdf file path is \Program Files\Email_Device\Email.sdf.

Build and deploy the program onto a Smart device or emulator. To execute the email program, type an SQL statement to find an email and press the Find button. The emails matching the specified criteria will be displayed in a list box as shown in Figure 7.6.

Figure 7.6
Device Email Application

The application that you created for a desktop and a device uses the local data store in SQL Server Compact Edition. The enterprise applications need to synchronize the data with a backend SQL Server. In Chapter 12, Synchronizing Data with Merge Replication, you will create an application that synchronizes data with a backend SQL Server.

The application that works on a local SQL Server Compact Edition database and synchronizes data with a backend Server is termed as an occasionally connected application. From a device, you can directly access and manipulate data on a backend server. These types of applications are called connected solutions as they require connectivity to a server while accessing the database. Chapter 12 will also demonstrate how to use an Sqlclient namespace of the .NET Compact Framework to develop a connected solution.

Summary

ADO.NET consists of a set of objects. One way to learn ADO.NET is to learn all the objects together. Instead of describing all the objects together, we first discussed the main objects that are needed to connect to the SQL Server database and execute SQL Commands to fetch data. We discussed Data Set in detail. After understanding the framework of ADO.NET, we discussed objects that use parameterized queries, transactions, and exception handling.

This chapter will not make you an ADO.NET expert. However, it will give you a fundamental understanding of ADO.NET objects for SQL Server Compact Edition.

Microsoft® SQL Server 2005 Compact Edition
By Prashant Dhingra and Trent Swanson
Published by Sams
ISBN-10: 0-672-32922-0
ISBN-13: 978-0-672-32922-7
Buy this book
Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved