The following steps show how to create the database and how to add it to a project so that it deploys when you run your project.

To create a new database

1. Start SQL Server Management Studio, select SQL Server Compact Edition,and then choose Connect.
   
   
2. Select New Databasefrom the drop-down list.
   
   
   
   This will bring up the window Create New SQL Server Compact Database.
   
   
3. Enter the name of the database that you want to create, in this case AppDatabase.sdf.
   
   
   
4. Choose OKand accept the warning about creating a database that uses a blank password.
   
   
5. In Object Explorer, expand the database, right-click Tables, and select New Table.
   
   
   
6. Next, create a new table, as shown in the following window. It makes EntryIDan Identityfield and a primary key. This table represents the kind of tables that you would use in your applications.
   
   
   
   The database is now created.
   
   
7. Close SQL Server Management Studio.
   
   

The sample with this article already has the database added. However, to show how to add a database to a project, perform the following steps.

To add a database to a project

1. Open Microsoft Visual Studio 2008, and create a new Smart Deviceproject.
   
   
2. Choose Project, choose Add Existing Item, and then from the Objects of typedrop-down list, select the Microsoft SQL Server Compact 3.5database.
   
   
3. Browse to the AppDatabase.sdf file that you created earlier and choose Add.
   
   The Data Source Configuration Wizard runs automatically.
   
   
4. Select the PerfTesttable from the Data Source Configuration Wizard. Select the Tablescheck box, and enable the project to create a dataset.
   
   
   
   This adds the database to your project, and sets the Deploymentproperty to Copy if newerso that it copies to the target device, if the database is newer than the copy on the device.
   
   

The test application consists of a single form that contains a DataGridcontrol and a menu. The table in the database, which is empty at first, is populated by using three techniques, then the inserted records are displayed in the DataGridcontrol, and finally the application displays the elapsed time for the operation.

To create a test application

1. In Microsoft Visual Studio 2008, add a bindingSourceto the project from the Toolbox, and change the name to perfTestBindingSource. Choose the DataSourceproperty, and select the AppDatabaseDataSetobject that you created earlier.
   
   
   
   The next step is to create a DataSetand TableAdapter.
   
   
2. Choose Project, choose Add New Itemand then select DataSet, which adds a dataset to the project. In Solution Explorer, double-click the dataset that you just created, then open Server Explorer, and drag the Perftable from Server Explorer onto the DataSetdesigner.
   
   
3. Next, add a DataGridcontrol to the project, set the Dockproperty to fill, and the DataSourceproperty to the perfTestBindingSourcecreated earlier.
   
   
4. Next, create the menu for the form with an option to Exiton the left button, and the three tests and a Clear Tableoption on an Optionsmenu on the right button.
   
   
   
   Finally, add a reference to SQL Server Compact Edition.
   
   
5. Choose Project, choose Add Reference, and then select System.Data.SQLServerCeas shown here. Choose OK.
   
   
   
   The pieces are now all assembled to manipulate our data.
   
   

At the start of the program, the connection to the database has to be opened.

	Copy Code
public Form1() { InitializeComponent(); this.perfTestTableAdapter.Connection.Open(); }

You must make sure that you close and dispose of the connection when the form is closed at the end of the run.

	Copy Code
private void Form1_Closing(object sender, CancelEventArgs e) { this.perfTestTableAdapter.Connection.Close(); this.perfTestTableAdapter.Dispose(); }

Each approach that is used to populate the table follows the same pattern. First a new StopWatchobject is started and 1000 records are inserted into the table. The records are then read out of the table again and used to populate the grid. Finally, each approach displays the elapsed time for the operation as recorded by the StopWatchobject.

The following code example shows how to use the DataSetapproach to insert the records.

Copy Code

private void menuItemDataSet_Click(object sender, EventArgs e) { Stopwatch sw = Stopwatch.StartNew(); Cursor.Current = Cursors.WaitCursor; // Insert 1000 records this.appDatabaseDataSet = new AppDatabaseDataSet(); for (int i = 0; i < 1000; i++) { appDatabaseDataSet.PerfTest.AddPerfTestRow ("Some test data", DateTime.Now); } // Insert into the database this.perfTestTableAdapter.Update(appDatabaseDataSet); // Refresh display this.perfTestBindingSource.DataSource = this.appDatabaseDataSet; this.perfTestBindingSource.DataMember = "PerfTest"; Cursor.Current = Cursors.Default; MessageBox.Show( "Elapsed ms: " + sw.ElapsedMilliseconds.ToString()); }

The code adds 1000 rows to a DataSetinstance, uses the Updatemethod on the DataAdapterinstance to insert the records into the table, and then displays the results in a grid. The following screen display results from using a DataSetobject.

The code that runs for the T-SQLmenu option uses a parameterized insert and then calls the Preparemethod that yields optimal performance when you use this technique. This means that everything possible is being done to optimize the query.

Copy Code

private void menuItemTSQL_Click(object sender, EventArgs e) { Stopwatch sw = Stopwatch.StartNew(); Cursor.Current = Cursors.WaitCursor; // Insert 1000 records SqlCeCommand cmd = new SqlCeCommand( "INSERT INTO PERFTEST(EntryData, TimeInserted) " + "VALUES(@EntryData, @TimeInserted)", this.perfTestTableAdapter.Connection); cmd.CommandType = CommandType.Text; cmd.Parameters.Add( new SqlCeParameter( "@EntryData", SqlDbType.NVarChar, 100, "EntryData")); cmd.Parameters.Add( new SqlCeParameter( "@TimeInserted", SqlDbType.DateTime, 8, "TimeInserted")); cmd.Prepare(); for (int i = 0; i < 1000; i++) { cmd.Parameters[0].Value = "Some test data"; cmd.Parameters[1].Value = DateTime.Now; cmd.ExecuteNonQuery(); } // Refresh display SqlCeCommand cmd1 = new SqlCeCommand( "PERFTEST", this.perfTestTableAdapter.Connection); cmd1.CommandType = CommandType.TableDirect; rsltSet = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable); this.perfTestBindingSource.DataSource = rsltSet.ResultSetView; Cursor.Current = Cursors.Default; MessageBox.Show( "Elapsed ms: " + sw.ElapsedMilliseconds.ToString()); }

The code creates a parameterized query, prepares it, and then performs 1000 iterations calling the ExecuteNonQuerymethod of the SqlCeCommandobject to insert the records. The code then uses a SqlCeResultSetobject to read the records from the database and to bind them to the BindingSourcefor display. The following screen shows the output from using this Transact-SQL technique.

This gives an improvement of 4236 milliseconds.

Finally, the following code shows how to use the SqlCeResultSetapproach.

Copy Code

private void menuItemResultSet_Click(object sender, EventArgs e) { Stopwatch sw = Stopwatch.StartNew(); Cursor.Current = Cursors.WaitCursor; // Insert 1000 records SqlCeCommand cmd = new SqlCeCommand( "PERFTEST", this.perfTestTableAdapter.Connection); cmd.CommandType = CommandType.TableDirect; rsltSet = cmd.ExecuteResultSet( ResultSetOptions.Scrollable | ResultSetOptions.Updatable); rsltSet.Read(); for (int i = 0; i < 1000; i++) { SqlCeUpdatableRecord record = rsltSet.CreateRecord(); record.SetString(1, "Some test data"); record.SetDateTime(2, DateTime.Now); rsltSet.Insert(record, DbInsertOptions.PositionOnInsertedRow); } // Refresh display this.perfTestBindingSource.DataSource = rsltSet.ResultSetView; Cursor.Current = Cursors.Default; MessageBox.Show( "Elapsed ms: " + sw.ElapsedMilliseconds.ToString()); }

A SqlCeCommandinstance is used in CommandType.TableDirectmode against the Perftable and the ExecuteResultSetmethod called to create a SqlCeResultSetobject that initially is empty of data. You must then call SqlCeResultSet.Readto position the cursor at the start of the record set. After that, the code loops to create 1000 records. The following screen shows the output from using SqlCeResultSet.

This is a 1931 millisecond improvement over Transact-SQL and 6137 milliseconds improvement over the DataSetapproach, a near 4-fold improvement.

Many queries are posted on newsgroups asking how to improve performance when you use SQL Server Compact Edition. As the application and chart show, the SqlCeResultSetobject is the best way to achieve faster record inserts.

For this application, use the NorthWind sample database to perform the tests. The interface resembles the last project, reading the database, and displaying the results in a grid.

The first query uses a DataSetapproach, joining the Order Details table to the Product Details and displaying the results. The query joins the tables on a common column that in this case is the Product ID column. The following code example shows the Transact-SQL statement that is used to retrieve the data.

Copy Code

SELECT [Order Details].[Order ID], [Order Details].[Product ID], Products.[Product Name], [Order Details].[Unit Price], [Order Details].Quantity, [Order Details].Discount, CONVERT(money, ([Order Details].[Unit Price] * [Order Details].Quantity) * (1 - [Order Details].Discount) / 100) * 100 AS ExtendedPrice FROM Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID] private void mnuiDataSet_Click(object sender, EventArgs e) { StringBuilder bldr = new StringBuilder(); bldr.Append("SELECT [Order Details].[Order ID], "); bldr.Append("[Order Details].[Product ID], "); bldr.Append("Products.[Product Name], "); bldr.Append("[Order Details].[Unit Price], "); bldr.Append("[Order Details].Quantity, "); bldr.Append("[Order Details].Discount, "); bldr.Append("CONVERT(money, ([Order Details].[Unit Price] "); bldr.Append(" * [Order Details].Quantity) * (1 -"); bldr.Append(" [Order Details].Discount) / 100) * 100 "); bldr.Append("AS ExtendedPrice"); bldr.Append(" FROM Products INNER JOIN"); bldr.Append(" [Order Details] ON Products.[Product ID] "); bldr.Append(" = [Order Details].[Product ID] "); Stopwatch sw = Stopwatch.StartNew(); Cursor.Current = Cursors.WaitCursor; SqlCeDataAdapter da; DataSet dsGeneral = null; sqlcmd = new SqlCeCommand(bldr.ToString(), connection); sqlcmd.CommandType = CommandType.Text; da = new SqlCeDataAdapter(sqlcmd); dsGeneral = new DataSet(); da.Fill(dsGeneral, "General"); int rowCount = dsGeneral.Tables["General"].Rows.Count; rsBindingSource.DataSource = dsGeneral.Tables["General"]; Cursor.Current = Cursors.Default; MessageBox.Show(rowCount + " Records retrieved in " + sw.ElapsedMilliseconds.ToString() + " elapsed ms"); }

The following screen shows the output from this code example by using a DataSetobject.

Next, use the same query. However, this time, use a SqlCeResultSetobject to retrieve the data.

Copy Code

private void mnuiJoined_Click(object sender, EventArgs e) { StringBuilder bldr = new StringBuilder(); bldr.Append("SELECT [Order Details].[Order ID], "); bldr.Append("[Order Details].[Product ID], "); bldr.Append("Products.[Product Name], "); bldr.Append("[Order Details].[Unit Price], "); bldr.Append("[Order Details].Quantity, "); bldr.Append("[Order Details].Discount, "); bldr.Append("CONVERT(money, ([Order Details].[Unit Price] "); bldr.Append(" * [Order Details].Quantity) * (1 -"); bldr.Append(" [Order Details].Discount) / 100) * 100 "); bldr.Append("AS ExtendedPrice"); bldr.Append(" FROM Products INNER JOIN"); bldr.Append(" [Order Details] ON Products.[Product ID] "); bldr.Append(" = [Order Details].[Product ID] "); Stopwatch sw = Stopwatch.StartNew(); Cursor.Current = Cursors.WaitCursor; sqlcmd = new SqlCeCommand(bldr.ToString(), connection); rs = sqlcmd.ExecuteResultSet(ResultSetOptions.Scrollable); rsBindingSource.DataSource = rs.ResultSetView; int rowCount = ((IBindingList)rs.ResultSetView).Count; Cursor.Current = Cursors.Default; MessageBox.Show(rowCount + " Records retrieved in " + sw.ElapsedMilliseconds.ToString() + " elapsed ms"); }

The following screen shows the result by using a SqlCeResultSetobject.

The following illustration shows an improvement of 2522 milliseconds over the DataSetapproach or a 2.4-fold speed increase.

You have already seen how to use a SqlCeCommandobject in TableDirectmode as a fast way to retrieve records from a single table through a SqlCeResultSet. You can also set the SetRangeproperty to retrieve a subset of records from a table by filtering on a range of values of a named index from the table. The following example fetches records from the Products table fetching only those records with a ProductID between 30 and 40.

Use a SqlCeCommandobject that has its CommandTypeproperty set to CommandType.TableDirect, setting the IndexNameproperty to the name of the index that you want to use, and setting the SetRangeproperty to the range of values that you want to retrieve. In this case, the index is called Products_PK, and the range is 30 to 40 inclusive.

Copy Code

private void mnuiSetRange_Click(object sender, EventArgs e) { Stopwatch sw = Stopwatch.StartNew(); Cursor.Current = Cursors.WaitCursor; sqlcmd = connection.CreateCommand(); sqlcmd.CommandType = CommandType.TableDirect; sqlcmd.CommandText = "Products"; sqlcmd.IndexName = "Products_PK"; sqlcmd.SetRange( DbRangeOptions.InclusiveStart | DbRangeOptions.InclusiveEnd, new object[] { 30 }, new object[] { 40 }); rs = sqlcmd.ExecuteResultSet(ResultSetOptions.Scrollable); rsBindingSource.DataSource = rs.ResultSetView; int rowCount = ((IBindingList)rs.ResultSetView).Count; Cursor.Current = Cursors.Default; MessageBox.Show(rowCount + " Records retrieved in " + sw.ElapsedMilliseconds.ToString() + " elapsed ms"); }

The following screen shows the output from the test program from this operation.

The sample code includes examples that perform similar filtering by using a SqlCeResultSetand a DataSetobject but filtering the records by using a WHERE clause in a Transact-SQL statement as the following code example shows.

	Copy Code
SELECT * FROM Products WHERE [Product ID] >= 30 and [Product ID] <= 40

Using these other techniques, the SqlCeResultSetapproach using the Transact-SQL selection example takes 96 milliseconds. The DataSetapproach using Transact-SQL selection example takes 139 milliseconds to perform the same record retrieval.

To this point in this article, you have learned how to use the SqlCeResultSetobject to insert and retrieve records, but SqlCeResultSetalso gives better performance than a DataAdapterand DataSetobject or Transact-SQL when it updates records.

The following code example shows how to use a SqlCeResultSetobject to update a column on a table.

Copy Code

private void mnuiLastUpdated_Click(object sender, EventArgs e) { string strSql = "SELECT * from Products"; Stopwatch sw = Stopwatch.StartNew(); Cursor.Current = Cursors.WaitCursor; sqlcmd = new SqlCeCommand(strSql, connection); rs = sqlcmd.ExecuteResultSet( ResultSetOptions.Scrollable | ResultSetOptions.Updatable); rsBindingSource.DataSource = rs.ResultSetView; int rowCount = ((IBindingList)rs.ResultSetView).Count; foreach (SqlCeUpdatableRecord rec in rs) { rs.SetSqlDateTime( rs.GetOrdinal("last_updated"), System.DateTime.Now); rs.Update(); } Cursor.Current = Cursors.Default; MessageBox.Show(rowCount + " Records retrieved and updated in " + sw.ElapsedMilliseconds.ToString() + " elapsed ms"); }

The data is retrieved by using the SqlCeResultSetobject, and then loop through updating each record with the current date and time by using the foreachconstruct. The following screen shows the results.