Inserting Master-Detail Data into a SQL-Server Compact Edition Database

Inserting Master-Detail Data into a SQL-Server Compact Edition Database

  • Comments 25

Yesterday John posted on the Visual Studio Data blog how to Refresh the Primary Key Identity Column during an Insert Operation using SQL Server. In that post he shows how the DataSet designer sets up a batch Insert statement to retrieve identity keys automatically. A while back I wrote about how to insert data into an Access database using TableAdapters and identity keys. Because Access doesn’t support batch statements, the Visual Studio Dataset designer doesn’t generate the code to retrieve the identity on new rows, so you end up having to write a bit of code yourself to do this as I showed in that post.

Since then I’ve had similar questions about how to do the same thing but using a SQL CE (Compact Edition) database so I thought I’d present the updated code to work with this type of database. Let’s take a look at how to do that, but first some background. (If you don’t care, just skip to the end for the code and sample download ;-))

What is SQL-Server Compact Edition (SQL CE)?

SQL CE is a file-based, single-user database that is really handy to use as local storage for your client applications because of its small footprint. And it’s included with Visual Studio 2008. Check out what’s new in SQL CE 3.5 here. One very typical use of SQL CE is as a local data cache to your SQL Server backend data using sync services in order to create an occasionally connected application. You can learn about the sync framework here and how to create a local data cache in Visual Studio 2008 here.

Setting up Parent-Child Tables and Relationships in SQL CE 3.5 Using Visual Studio 2008

To add a local database file to your Visual Studio 2008 project just select Add –> New Item, choose the Data category and then select Local Database. This will add a SQL CE database file with an .sdf extension to your project.

image

This will trigger the Data Sources wizard to start but first we need to create some tables in our database so cancel the wizard. Next open up the Server Explorer (or Database Explorer if you are using VS Express) and you should see your SQL CE database listed under Data Connections. Expand the database node and then right-click on Tables and select New Table. A dialog will open that allows you to design your table schema.

For this example I’ll create a parent Categories table and a child Products table with just a few fields to illustrate the concepts here. It’s important when you create a primary key that you choose the int data type, set Unique to Yes and then below in the column properties you set Identity to True. This will create an auto-incrementing, unique primary key.

image

Click OK when you’re done and then repeat the same process to add a new Products table. This time though we need to specify a field for the CategoryID foreign key and I’m going to make this a required field by setting Allow Nulls to No.

image

Now we need to add a relationship between these tables so that our little database will maintain referential integrity for us. We’re saying that a Product cannot exist without specifying a Category. We want the database to enforce this so that if we try to delete a Category it will prevent us from doing so if there are any Products. SQL CE 3.5 can maintain this kind of referential integrity for us, just go back to the Server Explorer and right-click on the Products table again but this time select Table Properties. Select the Add Relations page.

image

Type in the name of the relation you want to create and then select the Foreign Key Table Column, in my case I select CategoryID. Notice that you can also set up cascading or set null/default update and delete rules as well, but for this example we want to leave the rules as NO ACTION. Click Add Columns button then Add Relations button then click OK to save and close.

Setting up the Parent-Child DataSet

Now that we have the database set up we can design our DataSet. This is going to be almost exactly the same as how we set up our Access DataSet here so take a look at that post for the screen-shots, they’ll be the same here. To recap, you need to make sure you set up the relationship on the DataSet properly so that the primary key on the parent will cascade to the foreign key on the child. Right click on the relation in the DataSet designer and select "Both Relation and Foreign Key Constraint" and then set the Update and Delete rules to Cascade.

imageThe other important thing you need to do is set the Primary Key fields on both DataTables to ReadOnly False. We need to do this so we can set them in code and have the DataRelation cascade rules work correctly.

The DataSet designer will set all the rest of the properties and statements up correctly so you don’t have to modify anything else, though I do encourage you to take a look through them. One important thing to notice is the AutoIncrement, AutoIncrementSeed and AutoIncrementStep properties here. These are set to True, –1 and –1 respectively. This means that on the DataSet (client side) the referential integrity on new rows is maintained between the products and categories DataTables using temporary primary keys that are negative integers. These do NOT correspond to the keys in the database for new rows. It’s not until we send the updates to the database that we get the real identity keys so keep that in mind. (And these properties are the same regardless if you are using SQL CE, Access, or SQL Server identity keys.)

Okay so now we are ready to design our master-detail form. This should be a familiar process at this point but just in case here’s a recap. Open the Data Sources window (Main Menu –> Data –> Show Data Sources) and you should see the Categories and Products DataTables that are in the DataSet we just created. Design your Master-Detail form like normal. For this example I drag the Categories as details and then select the related Products by expanding the Categories node and dragging the Products table under there. This will set up a relationship on the form as well so that when we select a Category, it will only show those related products. This is also important to get our inserts to work correctly because the temporary identity key (-1, –2, –3, etc) on the CategoryID will automatically cascade to the Product’s CategoryID.

image

Loading and Editing the Parent-Child DataSet

Now that the DataSet is set to enforce the foreign key relationships, this means that you must have a parent for every child so you have to load the data in parent then child order. Remember, you have to make sure that every row in the child DataTable will have a corresponding parent row in the parent DataTable. This also means that you have to make sure to call EndEdit on any new parent BindingSource before any children can be added. I’m doing this by adding a handler to the grid’s Enter event. So the code-behind for this form is the same as the Access sample I showed before:

Public Class Form1

    Private Sub CategoriesBindingNavigatorSaveItem_Click() _
Handles CategoriesBindingNavigatorSaveItem.Click Me.Validate() Me.CategoriesBindingSource.EndEdit() 'Make sure to call EndEdit on all BindingSources before an update Me.ProductsBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.MyDatabaseDataSet) End Sub Private Sub Form1_Load() Handles MyBase.Load 'Load parent before child because contraints are enabled on the DataSet Me.CategoriesTableAdapter.Fill(Me.MyDatabaseDataSet.Categories) Me.ProductsTableAdapter.Fill(Me.MyDatabaseDataSet.Products) End Sub Private Sub ProductsDataGridView_Enter() Handles ProductsDataGridView.Enter 'You must commit the parent row to the DataTable before adding child rows Me.CategoriesBindingSource.EndEdit() End Sub End Class

Adding Code to Handle Inserts to SQL CE Databases

We need a way to set the primary key on the parent right after the row is inserted into the database and before any children are inserted. Now that we have keys cascading we just need to write code to handle the RowUpdated event on the DataAdapter inside the TableAdapter partial class. TableAdapters are generated classes that Visual Studio creates for us from the DataSet designer. These classes are declared as Partial Classes so that means we can add code to the same class even if it’s in a separate file. Right-click on the TableAdapter class in the DataSet Designer and select View Code and the partial class file that you can edit will be created for you.

Now we can write code to automatically query the database for the identity key because SQL CE supports the @@IDENTITY command just like Access. We need to execute this query after each new row has been inserted into the database, but before any children. If you’re using Visual Studio 2008 then the TableAdapterManager will handle sending parents first then children for insert operations so all we need to do is handle the DataAdapter’s RowUpdated event. Here’s the complete code listing for the DataSet and TableAdapter partial classes which includes code to set default values on the new rows. Notice it’s very similar to the Access code.  We’re just working with a different data access client library by importing the System.Data.SqlServerCe instead of System.Data.OleDb.

Imports System.Data.SqlServerCe

Public Class SQLCEIDHelper
    ''' <summary>
    ''' Retrieves the primary key auto-number identity values from SQL CE
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub SetPrimaryKey(ByVal trans As SqlCeTransaction, _
                                    ByVal e As SqlCeRowUpdatedEventArgs)

        ' If this is an INSERT operation...
        If e.Status = UpdateStatus.Continue AndAlso _
           e.StatementType = StatementType.Insert Then
            Dim pk = e.Row.Table.PrimaryKey
            ' and a primary key PK column exists...
            If pk IsNot Nothing AndAlso pk.Count = 1 Then
                'Set up the post-update query to fetch new @@Identity
                Dim cmdGetIdentity As New SqlCeCommand("SELECT @@IDENTITY", _
                                                       CType(trans.Connection, SqlCeConnection), _
                                                       trans)
                
                'Execute the command and set the result identity value to the PK
                e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
                e.Row.AcceptChanges()

            End If
        End If
    End Sub
End Class

Namespace MyDatabaseDataSetTableAdapters
    Partial Public Class CategoriesTableAdapter
        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As SqlCeRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            SQLCEIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class

    Partial Public Class ProductsTableAdapter
        Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                        ByVal e As SqlCeRowUpdatedEventArgs) _
                                        Handles _adapter.RowUpdated

            SQLCEIDHelper.SetPrimaryKey(Me.Transaction, e)
        End Sub
    End Class
End Namespace

Partial Class MyDatabaseDataSet
    Partial Class CategoriesDataTable
        Private Sub CategoriesDataTable_TableNewRow(ByVal sender As Object, _
                                            ByVal e As System.Data.DataTableNewRowEventArgs) _
                                            Handles Me.TableNewRow
            'Set defaults so that constraints don't fail when EndEdit is called
            Dim cat = CType(e.Row, CategoriesRow)
            cat.CategoryName = "[new]"
        End Sub
    End Class

    Partial Class ProductsDataTable
        Private Sub ProductsDataTable_TableNewRow(ByVal sender As Object, _
                                             ByVal e As System.Data.DataTableNewRowEventArgs) _
                                             Handles Me.TableNewRow
            'Set defaults so that constraints don't fail when EndEdit is called
            Dim product = CType(e.Row, ProductsRow)
            product.ProductName = "[new]"
        End Sub
    End Class
End Class

Now when we run our form, click the Add button on the ToolStrip to add a new Category and then enter new Products in the DataGridView below. Click Save and you will see the identity keys refresh back into the DataTables from our SQL CE database.

image

So to recap:

  1. Create your SQL CE tables, set the PKs to an Identity Integer column and then set up the relation to enforce referential integrity
  2. Create your DataSet and edit the relation and set it to a “Relation and Foreign Key Constraint” then set the Update and Delete rules to Cascade
  3. Make sure the PKs on the DataTables in the designer are set ReadOnly False
  4. Make sure to design your form so that the parent and related children BindingSources are set up properly
  5. Call EndEdit on the ParentBindingSource before you attempt to insert any child rows into the child DataTable so that the parent row is committed to the parent DataTable
  6. Use the TableAdapterManager to handle updating parents and children in proper order (this happens by default if you use the designer)
  7. Add a handler to the DataAdapter’s RowUpdated event to query the database for the new identity (SELECT @@IDENTITY) and set the PK on the DataRow to that value and this will cascade to any related children automatically

Download the sample application from Code Gallery.

I hope that clears up the confusion on how to work with file-based databases like Access and SQL CE that don’t support batch statements. Once you understand how ADO.NET is working with your DataSets then it’s much easier to understand how to configure things like this. SQL CE is a great FREE database for single-user applications and I encourage you to have a look at it if you’re building these types of client applications.

SQL CE also supports the Entity Framework so that would probably be a good follow-up post to this one… next time! ;-)

Enjoy!

Leave a Comment
  • Please add 2 and 8 and type the answer here:
  • Post
  • Hi Beth,

    I've tried using bindingsource.endedit to continue editing the child controls after the parent is done but It does not automatically insert the foreign key like it does when one uses a Datagridview (DGV). All of the above works fine for the DGVs enter event but does not work when the child controls are in details view. I have tried using the enter event of a groupBox control and also tried using the enter event of the individual controls but still does not work. I think my problem is similar to Elainw's qusestion above. Did we have any good solution to that? Thanks in advance for your prompt reply.

    B.

  • Dude, you really have some nice guide there. Thanks a bunch! :)

    Trying on a Master-Detail-SubDetail using your guide. Hopefully it works!

  • Hi Beth!!! You saved my day. This problem was driving me crazy. I really think this is a common scenario and need to work "out of the box". I hope you can take our feedback to MS and it can be included on DataSetDesigner.

    There ir a little problem with your code, it works great when you update thougth TableAdapter.Update method but fail when you use TableAdapterManager.UpdateAll that is really simpler. I have found the solution on Jon's link to a post from Alex in the ADO.NET group. The key on the problem is that TableAdapterManager.UpdateAll use a transaction and you call "SELECT @@IDENTITY" from outside the transaction. The solution is to change SetPrimaryKey definition as follow:

    Public Shared Sub SetPrimaryKey(ByVal Con As SqlCeConnection, ByVal e As SqlCeRowUpdatedEventArgs, ByVal Transaction As SqlCeTransaction)

    Set cmdGetIdentity as follow:

    Dim cmdGetIdentity As SqlCeCommand

    If IsNothing(Transaction) Then

       cmdGetIdentity = New SqlCeCommand("SELECT @@IDENTITY", Con)

    Else

       cmdGetIdentity = New SqlCeCommand("SELECT @@IDENTITY", Transaction.Connection, Transaction)

       End If

    And call SetPrimaryKey as follw:

    SQLCEIDHelper.SetPrimaryKey(_connection, e, _transaction)

    Best Regards, Edu.

  • hi beth,

    i have some problems with inserting,adding and deleting data in sql compact..i tried using the codes that u have listed above to save the data,but when i run the project the data doesn't get added to the sql compact database..

    i am using sql compact3.5 and visual basic 2008for the project..

    please help me.....

  • how can i insert data in vb 2005 using sql server explorer

  • Hello, anyone has any idea how to solve this same problem with the Entity Framework ?

    Thanks !

    Koen.

  • can we have multipul foreinkey in one table

  • Hi Beth,

    I am trying to retrieve the primary key after a record is inserted into the table. In my case, the primary key of the table is not integer but "uniqueidentifier". When I tried that I am just getting a null value as the returned value.

    Pl let me know how this can be handled.

    Thanks

    Siva

  • Thanx for sharing...

  • Hi All,

    i am developing an application on visual studio 2012 , sql server 2012 express edition and i am using Entity framework 5.0. before 2 days i am facing a different type of problem during insertion on data in database, my primary key is unique id , it is auto incremental, when i  am inserting data into database then i look on the database as

    #Key

    5

    1002

    2002

    2003

    2004

    I am not understand ? what type of this problem ? this problem of edmx or SQL , i did not know please provide solution.

Page 2 of 2 (25 items) 12