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

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

  • Comments 26

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 3 and type the answer here:
  • Post
  • Thanks for your sharing and the information is valuable. I'm using SQL CE as local database as the same as the scenerio as yours except I'm not using the data binding control. How can I retrieve the ID (Auto-generated) after I call the Insert command from the Strongly-Typed Dataset?

  • Hi Wilton,

    As long as you set up your DataSet relation like above and you manually populate the child with the same temporary key as the parent, then the save process is exactly the same.

    HTH,

    -B

  • Hi Beth,

    you saved my live. I've been searching for your piece of code for 6 days now, and now my application with master-detail forms successfully works on sqlserverce (changing primary keys).

    (don't know, why this isn't part of the standard bevaviour of dataset.updateAll.

    Thanks a lot for your hints!!!!!

    Greets from Dortmund, Germany

    Peter

  • Hai Beth nice to read your article (again).

    I would ask you something about SQL Server 2008 Express.

    First, why it's too difficult to seek for the IDE of SQL Server 2008. In SQL Server 2005 Express I can download the Enterprise Manager and I get the IDE.

    But in SQL Server 2008 I have lots of difficulty.

    I have downloaded both files SQLManagementStudio_x64_ENU.exe and SQLManagementStudio_x86_ENU.exe in http://www.microsoft.com/downloads/details.aspx?familyid=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=en but I have warned about error : " is not a valid win32 application".

    Second, How to connect VB 2008 Express with SQL Server 2005?.

    I install VB 2008 Express and use XP SP2 for the OS. I hope you can answer my difficulty.

    Thanks for the attention.

  • Hi,

    Thank you for this great information.  I am not new to programming, but I am new to Visual Basic.  I am trying re-create what you've done, as part of a learning exercise, but with using details, as opposed to gridview, to populate my form.  In order for this to work, I know that I need to change the ProductsDataGridView_Enter() sub routine, but I'm not sure how.  Would you mind giving me a pointer?

    Thank you,

    Elaine

  • Hi Elainw,

    You just need to call the parent bindingsource's EndEdit method before you enter the child data.

    HTH,

    -B

  • Hi Beth,

    I'm having a small problem on my program and i hope you can help me out.

    I'm using an SQL CE that already has some values in it, and i have one table that it's PK is AutoIncrement but when i try to use the TableAdapterManager to insert a new value on that table the first time i try to do that it gives me an error of duplicate primary key but the second time it works fine..

    What's my problem? Is my dataset not reading the pk right?

    I would apreciate any help,

    Diogo

  • Hi Diogo,

    Have you implemented the code like I shoed above? You need to handle the RowUpdated event on the table adapter in order to refresh the keys properly on the client.

    HTH,

    -B

  • Hi Beth,

    Thanks for the fast answer.

    I was trying to implement the code like above but i'm having a trouble in the _adapter_RowUpdated function.

    It seems that i can't do a this.Transaction <i'm using c# by the way :) > the rest of the code i was able to implement but now i can't do the SQLCEIDHelper.SetPrimaryKey...

    can you help me with what i should insert??

    SQLCEIDHelper.SetPrimaryKey(?? , e);

    Diogo

  • Hi Diego,

    If you take a look at the comments of this post some readers translated this to C#:

    http://blogs.msdn.com/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx

    Although in this case it isn't as easy as VB. Sorry.

    -B

  • Hi Beth,

    Thanks for all the support, i was able to do all the code in c#, compile and run it but the error is still there..

    It's very strange cause it only gives me error the first time i try to insert in the database.. after that it works without any problem..

    I'll continue searching for a solution to the problem and if i find it i'll post it here cause maybe there are more people with similar problems.

    Thanks again for all the help.

    Diogo

  • I can view and retrieve data successfully, but when i try to insert a data, it keeps saying my connection string database path incorrect. where to get the correct connection string?

  • I am uing VB 6 and SQL as well as fp spread.

    I know nothing about VB.net

    Is there a chance to tranlate this code to vb6?!

    Thanks!

  • I'm new to VB.Net, coming from VB6.  Is there some significance to the leading underscore in the "_adapter_RowUpdated" event? Why isn't it named "CategoriesTableAdapter_RowUpdated" or something similar instead?

  • I was haing this problem in C# and having a difficult time solving after reading all of the above.

    Alex in the ADO.NET group helped me in finding a solution.

    social.msdn.microsoft.com/.../b5d39f6c-7025-4023-8f48-8e5810b8fa03

Page 1 of 2 (26 items) 12