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

Artikel
09/15/2009

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.

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.

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.

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.

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.

The 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.

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.

So to recap:

Create your SQL CE tables, set the PKs to an Identity Integer column and then set up the relation to enforce referential integrity
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
Make sure the PKs on the DataTables in the designer are set ReadOnly False
Make sure to design your form so that the parent and related children BindingSources are set up properly
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
Use the TableAdapterManager to handle updating parents and children in proper order (this happens by default if you use the designer)
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!

Comments

Anonymous
November 22, 2009
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?
Anonymous
November 25, 2009
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
Anonymous
January 09, 2010
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
Anonymous
February 02, 2010
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.
Anonymous
March 29, 2010
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
Anonymous
April 19, 2010
Hi Elainw, You just need to call the parent bindingsource's EndEdit method before you enter the child data. HTH, -B
Anonymous
April 27, 2010
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
Anonymous
April 28, 2010
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
Anonymous
April 28, 2010
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
Anonymous
April 29, 2010
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
Anonymous
April 29, 2010
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
Anonymous
May 11, 2010
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?
Anonymous
May 25, 2010
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!
Anonymous
August 09, 2010
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?
Anonymous
November 15, 2010
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
Anonymous
November 30, 2010
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.
Anonymous
December 27, 2010
Dude, you really have some nice guide there. Thanks a bunch! :) Trying on a Master-Detail-SubDetail using your guide. Hopefully it works!
Anonymous
March 09, 2011
The comment has been removed
Anonymous
September 04, 2011
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.....
Anonymous
September 28, 2011
how can i insert data in vb 2005 using sql server explorer
Anonymous
October 12, 2011
Hello, anyone has any idea how to solve this same problem with the Entity Framework ? Thanks ! Koen.
Anonymous
February 20, 2012
can we have multipul foreinkey in one table
Anonymous
March 16, 2012
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
Anonymous
September 22, 2013
Thanx for sharing...
Anonymous
July 06, 2014
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.
Anonymous
September 25, 2014
Thanks .. Is there any new link for download.
Anonymous
November 21, 2014
thanks Beth, I work fine.. I want to mention that if the child table is not updating "while the master is updating OK" you have to add the child table adapter to the table manager properties. with best regard

Freigeben über

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

Comments

Zusätzliche Ressourcen