Programming SQL Server 2005 Compact Edition with ADO - Page 6

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

Table 7.22 Commonly Used SqlCeTransaction Object Public Properties

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

Table 7.24 Commonly Used SqlCeTransactionInProgressException Object Public Properties

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

Table 7.26 Commonly Used SqlCeException Object Public Properties

---

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

Table 7.28 Commonly Used SqlCeError Object Public Properties

---

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

Table 7.30 Commonly Used SqlError Object Public Properties

---

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.