26.15 Using a SQL Server CE Database

You want to add, delete, and modify records in a SQL Server CE database.

Technique

This recipe continues where the last recipe left off. The last recipe created a database using SQL Server CE edition, which is used to hold song information. This recipe creates an application that lets you view that information as well as modify it.

To begin, create a new Smart Device Application. This application uses a DataGrid , but when you initially create the project, the DataGrid does not show up in the toolbox. To enable data controls, you must first add a reference to the SQL Server CE assembly named System.Data.SqlServerCe . After you add the reference, drag and drop a DataGrid control onto the device form.

Because the database is located on the device itself, you will be unable to use the Windows Form designer to automatically create the necessary data objects. Within the source file, create four fields to be used for SQL Server CE data access. These fields include a SqlCeConnection to connect to the database; a SqlCeAdapter to extract data from the database; a SqlCeCommandBuilder to automatically generated INSERT , DELETE , and UPDATE commands; and a DataSet to store the data from the database. One of these objects, the DataSet , is the same class used within ADO.NET applications. The other three are specific to SQL Server CE but are related to their SQL Server counterparts within the full .NET Framework. In fact, you'll notice that using these objects is exactly the same.

The first thing to do is create an instance of these objects, extract the database records, and bind the data to the DataGrid object. The procedures for this step are the same methods used in ADO.NET. First, you create a SqlCeConnection object, specifying a connection string. The connection string for a SQL Server CE database is much simpler than a regular SQL Server connection string. The only parameter you need, assuming you left the password field blank when you created the database, is the Data Source parameter. Its value is the location of the database, which by default is in the My Documents folder unless you specified otherwise . Next, create an instance of the SqlCeDataAdapter class. You can use a parameterless constructor because you will be associating the adapter with a SqlCeCommand . Therefore, set the SelectCommand property of the SqlCeDataAdapter object by creating a new SqlCeCommand object, passing in the SELECT statement and the connection object.

To automatically generate the INSERT , UPDATE , and DELETE commands, create an instance of the SqlCeCommandBuilder object, passing the data adapter as the parameter. Any time the DataSet object is updated, the data adapter uses the automatically generated commands to update the database when the Update method is called.

The last step is to create the DataSet object, fill it using the DataAdapter , and set the DataSource property of the DataGrid to the Catalog table of the DataSet . Listing 26.11 shows the form constructor for all of these steps.

Listing 26.11 Populating a DataGrid from a SQL Server CE Database

 public Form1() {     InitializeComponent();     sqlConnection = new SqlCeConnection(         @"Data Source=/My Documents/MusicCatalog.sdf;" );     string selectCmd   = "SELECT * From Catalog";     // create the adapter     sqlDataAdapter = new SqlCeDataAdapter();     // assign select command     sqlDataAdapter.SelectCommand = new SqlCeCommand(selectCmd, sqlConnection);     cmdBuilder = new SqlCeCommandBuilder( sqlDataAdapter );     // create dataset     catalogDataSet = new DataSet("Catalog");     // fill dataset     sqlDataAdapter.Fill(catalogDataSet, "Catalog" );     // databind datagrid     dataGrid1.DataSource = catalogDataSet.Tables["Catalog"];     sqlConnection.Close(); } 

At this point, you can run your application and you see the DataGrid display the column headers for each column in the database table. However, unlike the DataGrid in the .NET Framework, you cannot directly edit cells in a Smart Device Application. You have to devise a way for users to edit the cells. For this application, create a menu item that when clicked automatically inserts a new record with some default values. Additionally, to enable editing of a cell , create a TextBox control at the bottom of the DataGrid with a corresponding button. To update a cell, therefore, the user selects a cell from the DataGrid , enters a new value in the TextBox , and clicks on the Button to make the change. The code to add a new row to the DataGrid as well as to update the underlying DataSource is as follows :

 private void mnuAdd_Click(object sender, System.EventArgs e) {     DataRow newRow = catalogDataSet.Tables["Catalog"].NewRow();     newRow["Title"] = "Song Title";     newRow["Artist"] = "Artist";     catalogDataSet.Tables["Catalog"].Rows.Add( newRow );     sqlDataAdapter.Update( catalogDataSet, "Catalog" ); } 

The Button event handler is where the database update code resides for individual cell editing. The first step is to figure out which cell the user has clicked within the DataGrid . Access the CurrentCell property of the DataGrid . This DataGridCell object contains information such as the value, row, and column that you need to update the underlying data source. Before that happens, the DataGrid cell data changes to reflect the new value. Next, you find the corresponding row within the DataSet by using the CurrentRowIndex value of the DataGrid as an indexer into the Catalog table rows. Once you find the row, you can use traditional dictionary-based access to set the individual columns within the row and finally update the data source using the data adapter, as shown in Listing 26.12.

Listing 26.12 Updating a SQL Server CE Database

 private void btnChange_Click(object sender, System.EventArgs e) {     DataGridCell currentCell;     string currentCellData;     // Get the text to put into the current cell.     currentCellData = tbNewValue.Text;     // Get the current cell.     currentCell = dataGrid1.CurrentCell;     // Set the current cell's data.     dataGrid1[currentCell.RowNumber,currentCell.ColumnNumber] = currentCellData;     // update the data source     DataRow updateRow =         catalogDataSet.Tables["Catalog"].Rows[dataGrid1.CurrentRowIndex];     updateRow["Title"] = dataGrid1[dataGrid1.CurrentRowIndex,1];     updateRow["Artist"] = dataGrid1[dataGrid1.CurrentRowIndex,2];     sqlDataAdapter.Update( catalogDataSet, "Catalog" ); } 

The last thing to enable for the SQL Server CE database application is to delete a row. The procedure is much simpler than that for performing a database update. All you need is to find the corresponding row within the DataSet by once again using the CurrentRowIndex of the DataGrid . Once you find the row, call the Delete method from the DataRow object and update the data source using the data adapter:

 private void mnuDelete_Click(object sender, System.EventArgs e) {     DataRow updateRow =         catalogDataSet.Tables["Catalog"].Rows[dataGrid1.CurrentRowIndex];     updateRow.Delete();     sqlDataAdapter.Update( catalogDataSet, "Catalog" ); } 

Comments

This recipe really demonstrates some of the cool things you can do with mobile devices and SQL Server CE. Both of the technologies involved, the .NET Compact Framework and the SQL Server CE Edition, might be small, but the functionality contained within them allows for unique and interesting application ideas. Although it might take a little more work because the designer and control support isn't as rich as it is in the full .NET Framework, you can still do a majority of the methods shown with the added benefit of being able to take it on the road with you.