4 Simple steps to learn SqlCeDataAdapater, SqlCeDataSet to modify data in SQL Server CE tables

You already know, from my previous blog  Using ADO.NET SqlCeCommand and SqlCeDataReader to select data from SQL Server Compact how to SELECT data for ReadOnly using SqlCeDataReader Object. You can’t update the data using SqlCeDataReader object.  Instead you will need to use SqlCeDataAdapter and SqlCeDataSet objects if you have to make any changes and save the data back to the Database. In this post, I will show you how to use SqlCeDataAdapter and SqlCeDataSet objects  in System.Data.SqlServerCe Namespace to edit/delete/update a SQL Server CE database tables in ASP.NET 4.0 web forms application.

SqlCeDataAdapter and SqlCeDataSet Objects and their relationship

The SqlCeDataAdapter serves as a bridge between a DataSet and data source. It is used for retrieving data from, and storing data to, the data source. The SqlCeDataAdapter provides this bridge by using Fill() method to load data from the data source into the DataSet, and using Update() Method to update the Database Table with changes made in the DataSet.

When the SqlCeDataAdapter fills a DataSet, it creates the necessary tables and columns for the returned data if they do not already exist. The primary key information will not be included in the implicitly created schema unless the MissingSchemaAction property is set to AddWithKey. You can also have the SqlCeDataAdapter create the schema of the DataSet, including primary key information, before filling it with data by using FillSchema() method.

Inserting Data into SQLServer CE table – Step by Step Procedure

1. Create SqlCeDataAdapter using a SQL SELECT Statement and a connection string.
2. Create an empty dataset object and pass it to Fill() method of the data adapter. The second parameter “AllStudents” identifies one table in the set of tables represented by the Dataset. A DataSet can have multiple tables each with its own unique name.
3. Create SqlCeCommandBuilder object from the Data Adapter. This will automatically create the UPDATE and DELETE sql commands internally, based on the SELECT command already provided in step 1.
4. Call NewRow() method on DataSet.Tables["AllStudents"] table which retuen a DataRow object newRow.
5. Assign the filed values for all the required fields. Here I am assigning values for Name and Class fields. id field is a primary key with autoincrement set to true so no need to assign.
6. Now I call Add(newRow) method on the dataSet.Tables["AllStudents"] .Rows object . This will add the newly created row to the rows collection of the AllStudents table in the dataset.
7. Call DataAdapter.Update (DataSet, TableName) to save the data back to the database.

Here is the code in C#

protected void Button2_Click(object sender, EventArgs e)        {          
    string commandString = "Select * from Students";          
    using (SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(commandString, connstr))           {              
        try               {                    
            DataSet dataSet = new DataSet();                    
            dataAdapter.Fill(dataSet, "AllStudents");                    
            SqlCeCommandBuilder sqlCb = new SqlCeCommandBuilder(dataAdapter);                    
            DataRow newRow = dataSet.Tables["AllStudents"].NewRow();                    
            newRow["Name"] = TextBox1.Text;                    
            newRow["Class"] = int.Parse(TextBox2.Text);
            dataSet.Tables["AllStudents"].Rows.Add(newRow);
            dataAdapter.Update(dataSet, "AllStudents");                
            Label1.Text = "Inserted " + TextBox1.Text + "   "+TextBox2.Text;              
        }              
        catch (Exception ex)               {                  
            Label1.Text = ex.ToString();              
        }          
    }        
}

Deleting Data from a SQLServer CE table – Step by Step Procedure

1. Create SqlCeDataAdapter using a SQL SELECT Statement and a connection string.
2. Create an empty dataset object and pass it to Fill() method of the data adapter. The second parameter “AllStudents” identifies one table in the set of tables represented by the Dataset. A DataSet can have multiple tables each with its own unique name.
3. Create SqlCeCommandBuilder object from the Data Adapter. This will automatically create the UPDATE and DELETE sql commands internally, based on the SELECT command already provided in step 1.
4. Call Select(FilterString) method on DataSet.Tables["AllStudents"] table which returns a DataRow[] object called foundRows. In my case the filter string is “Where Name=’Name_From_Form_Input’”
5. Now I call Delete() method on the foundRows[0] object . This will delete the first row in the foundRows object
6. Call DataAdapter.Update (DataSet, TableName) to save the data back to the database.

Note: If you don’t have a primary key in your table, create one. Usually I use a column called id which is integer with auto increment enabled as my primary key. Without this, you will get the following error when trying to update, delete or insert into the table using the DataSet object.

System.InvalidOperationException: Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information. at
System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable
dataTable) at DataAccessProject.WebForm2.Button3_Click(Object sender, EventArgs e) in C:\Documents and Settings\raja\My Documents\Visual Studio
2010\Projects\DataAccess\DataAccessProject\ADOExample.aspx.cs:line 85

Here is the code in C#

protected void Button3_Click(object sender, EventArgs e)
{
            string commandString = "Select * from Students";
            try
            {
                SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(commandString, new SqlCeConnection(connstr));
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet, "AllStudents");
                SqlCeCommandBuilder sqlCb = new SqlCeCommandBuilder(dataAdapter);
                string delcmd = sqlCb.GetDeleteCommand().CommandText;
                DataTable t = dataSet.Tables["AllStudents"];
                DataRow[] foundRows = t.Select("Name = '" + TextBox3.Text.Trim() + "'");
                foundRows[0].Delete();
                dataAdapter.Update(t);
                Label1.Text = "Deleted Student: " + TextBox3.Text + " " + foundRows[0].RowState;
            }
            catch (Exception ex)
            {
                Label1.Text = ex.ToString();
            }
}

Update Data in a SQLServer CE table – Step by Step Procedure

1. Create SqlCeDataAdapter using a SQL SELECT Statement and a connection string.
2. Create an empty dataset object and pass it to Fill() method of the data adapter. The second parameter “AllStudents” identifies one table in the set of tables represented by the Dataset. A DataSet can have multiple tables each with its own unique name.
3. Create SqlCeCommandBuilder object from the Data Adapter. This will automatically create the UPDATE and DELETE sql commands internally, based on the SELECT command already provided in step 1.
4. Call Select(FilterString) method on DataSet.Tables["AllStudents"] table which returns a DataRow[] object called foundRows. In my case the filter string is “Where Name=’Name_From_Form_Input’”
5. Now I assign a new Name in the place of existing Name in the  foundRows[0]["Name"] . This will UPDATE the first row in the foundRows object inside the memory.
6. Call DataAdapter.Update (DataSet, TableName) to save/commit the data back to the database.

protected void Button4_Click(object sender, EventArgs e)
{
    string commandString = "Select * from Students";
    try
    {
        SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(commandString, new SqlCeConnection(connstr));
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet, "AllStudents");
        SqlCeCommandBuilder sqlCb = new SqlCeCommandBuilder(dataAdapter);
        string delcmd = sqlCb.GetDeleteCommand().CommandText;
        DataTable t = dataSet.Tables["AllStudents"];
        DataRow[] foundRows = t.Select("Name = '" + TextBox4.Text.Trim() + "'");
        foundRows[0]["Name"] = TextBox5.Text.Trim();
        dataAdapter.Update(t);
        Label1.Text = "Updated Student: " + TextBox4.Text + " to " + TextBox5.Text + foundRows[0].RowState;
    }
    catch (Exception ex)
    {
        Label1.Text = ex.ToString();
    }
}

Here is a flowchart of how the Objects are used to Delete, Update and Insert data:

download the DataAdapter/DataSet flow chart in MS Word format.

Conclusion

• Using x=NewRow() and DataSet.Rows.Add(x) you can INSERT a new row.
• Using foundRows[0].Delete() you can DELETE a row from a collection of rows returned by executing a Select(filter) on a DataTable
• Using foundRows[0]["ColumnName"]=newvalue you can UPDATE a row
• In all the above cases, unless you call the DataAdapter.Update(DataTable), the changes will not be saved or committed back to the database persistently.