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 5

By DatabaseJournal.com Staff

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.



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