.net – foreign key constraint in sql

ado.net

I have 2 tables in sql server with primary keys set to identity. They are related and work fine.
I then created a form in vb 2008 and tried inserting some values into my database the respective primary keys work but the primary key in the parent table wont show up in the child table.I did create a relationship in vb using ado.net and all the details of my table are defineed in the data table. For example
cust tables (custid,name,..)
book table(bookid,bookname,..,custid)

in vb my insert statement is something like Insert into cust(name) values(@name)
insert into book(bookname) values(@bookname). I do not include the id columns as they auto generate in the database(tables).
My question is that how do i get to insert the custid in the book table when the data is stored back into the tavles in my database.

Please advice with an example as im not half as good as you guys.

Kind Regards

Best Solution

You have to know which customer you want to associate with the book before INSERTing the book. If you don't know before hand, you can't. So somewhere in your Form there should be a way to select a customer. Then when you create a book, you grab that customer's ID and insert it along with the other book info.

Related Solutions

.net – How to update Dataset Parent & Child tables with Autogenerated Identity Key

I think this should be more obvious and should work without any tweaking. But still, it's pretty easy.

The solution has two parts:

Create DataRelation between child and parent tables and set it to cascade on updates. That way whenever parent Id changes, all children will be updated.
```
Dim rel = ds.Relations.Add(parentTab.Columns("Id"), childTab.Columns("ParentId"))
rel.ChildKeyConstraint.UpdateRule = Rule.Cascade
```
Dataset insert and update commands are two-way: If there are any output parameters bound or any data rows returned, they will be used to update dataset row that caused the update.

This is most useful for this particular problem: getting autogenerated columns back to application. Apart from identity this might be for example a timestamp column. But identity is most useful.

All we need to do is set insert command to return identity. There are several ways to do it, for example:

a) Using stored procedure with output parameter. This is the most portable way among "real" databases.

b) Using multiple SQL statements, with last one returning inserted row. This is AFAIK specific to SQL Server, but the simplest:
```
insert into Parent (Col1, Col2, ...) values (@Col1, @Col2, ...);
select * from Parent where Id = SCOPE_IDENTITY();
```

After setting this up, all you need to do is create parent rows with Ids that are unique (within single dataset) but impossible in the database. Negative numbers are usually a good choice. Then, when you save dataset changes to database, all new parent rows will get real Ids from database.

Note: If you happen to work with database without multiple statements supports and without stored procedures (e.g. Access), you will need to setup event handler on RowUpdated event in parent table adapter. In the hanler you need to get identity with select @@IDENTITY command.

Some links:

Entity-framework – How to view the SQL generated by the Entity Framework

For those using Entity Framework 6 and up, if you want to view the output SQL in Visual Studio (like I did) you have to use the new logging/interception functionality.

Adding the following line will spit out the generated SQL (along with additional execution-related details) in the Visual Studio output panel:

using (MyDatabaseEntities context = new MyDatabaseEntities())
{
    context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
    // query the database using EF here.
}

More information about logging in EF6 in this nifty blog series: http://blog.oneunicorn.com/2013/05/08/ef6-sql-logging-part-1-simple-logging/

Note: Make sure you are running your project in DEBUG mode.

Best Solution

Related Solutions

.net – How to update Dataset Parent & Child tables with Autogenerated Identity Key

Entity-framework – How to view the SQL generated by the Entity Framework

Related Question