How to insert to table with one-to-one relationship via dataset

I use asp.net 4 and DataSets for accessing the database. There are two tables with one-to-one relationship in the database. It means that both tables have the same column as a primary key (say Id), and one of tables has @identity on this column set.

So in general if we want to insert, we insert first into the first table, than insert into the second table with id.table2 = id of the corresponding record in table1.

I can imagine how to achieve this using stored procedure (we would insert into the first table and have id as an out parameter and then insert into the second table using this id, btw all inside one transaction).

But is there a way to do it without using a stored procedure? May be DataSets \ DataAdapters have such functionality built in?

Would apprec开发者_StackOverflow中文版iate any help.

---

Today it is so quiet here... Ok if anybody is also looking for such a solution, I've found a way to do it.

So our main problem is to get the id of the newly created record in the first table. If we're able to do that, after that we simply supply it to the next method which creates a corresponding record in the second table.

I used a DataSet Designer in order to enjoy the code autogeneration feature of the VS. Let's call the first table TripSets. In DataSet Designer right click on the TripSetsTableAdapter, then Properties. Expand InsertCommand properties group. Here we need to do two things.

First we add a new parameter into the collection of parameters using the Parameters Collection Editor. Set ParameterName = @TripId, DbType = Int32 (or whatever you need), Direction = Output.

Second we modify the CommandText (using Query Builder for convenience). Add to the end of the command another one after a semicolon like that:

(...);
SELECT @TripId = SCOPE_IDENTITY()

So you will get something like this statement:

INSERT INTO TripSets 
(Date, UserId)
VALUES     
(@Date,@UserId);
SELECT @TripId = SCOPE_IDENTITY()

Perhaps you will get a parser error warning, but you can just ignore it. Having this configured now we are able to use in our Business logic code as follows:

int tripId;        
int result = tripSetsTableAdapter.Insert(tripDate, userId, out tripId);

// Here comes the insert method into the second table

tripSetTripSearchTableAdapter.Insert(tripId, amountPersons);

Probably you will want to synchronize this operations somehow (e.g. using TransactionScope) but it is completely up to you.