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.