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 6

By DatabaseJournal.com Staff

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.



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