[ADO.NET + VB 2005] Master Detail insert - one stored procedure or divided ?

Hi,

I have one database SQL 2000 (or 2005 it's not important but I need to be soluzion compatible with 2000) with 2 tables declared as follow:

TAB1 (this one is the master)
IDTAB1 primary key autoincrement integer
NOTE varchar(50)
TS timestamp field

TAB2 (this one is the detail)
IDTAB2 autoincrement primary key integer
FKIDTAB1 this one is the foreign key from master table
VERSION (this field it's particular I will describe later anyway it's integer)
TEXT (string)
TS timestamp field

The structure it's currectly set up as a DATASET with two table called TAB1 and TAB2 and I have a datarelation between TAB1 and TAB2 for rappresent MASTER/DETAIL relation.
The structure it's rappresented in one form by two DATAGRIDVIEW, the MASTER one bound with TAB1 and the DETAIL bound with the relation. I am using 2 dataadapter for the 2 tables.

The field VERSION it's difficult part for me:
if the detail it's the first one for the master and if in datagridview bound at TAB2 no one write other things then null then have to be set to 1, if the detail newly inserted it's not the first one then VERSION have to be set to AVG(VERSION)+1, otherwise have to be set as in the field user write in the VERSION cell of my datagridview.

Using 2 commandbuilder and using the HANDLER event for onrowupdated and onrowupdating of the TAB1 I can assign the currect ID to FKIDTAB1 in TAB2 using the executescalar @@IDENTITY so when I click on button save I launch the UPDATE for TAB1 and UPDATE for TAB2 and all working fine (not the field VERSION because I still never understand how to solve that, if anyone have a suggestion I will be happy about it too).But what if I want to use Stored Procedure ? How using Transaction ?

This is the part I am not able to accomplish,

I hope someone can help on me,

Hi,

Base on my understanding of your requirement. You have master/detail tables, when you insert new record into these two tables, you need to use transaction.

SqlTransaction

SqlTransaction Class (System.Data.SqlClient)

To use SqlTransaction, you should first open a connection then use connection.BeginTransaction. Do all your command in try block, if all commands successfully executed, you can call transaction.Commit(); If exception occurs, call transaction.Rollback();

To use Stored Procedure, I think you'd better contact Transact-SQL team
Transact-SQL Forum
They can help you to learn how to create stored procedure according to your business logical. And the version logical should in the stored procedure.

To call stored procedure in your winform application, you can use SqlCommand class, set its CommandText to the stored procedure name and CommandType to StoredProcedure.

SqlCommand.CommandType Property (System.Data.SqlClient)

__________________
Shaalini.S
Be the Best of Whatever you are...

Hi,

Actually I ask if it's better to use 2 stored procedure, one per tables, separated, and if in this way it's better using transaction inside my stored procedure or using it outside in my VB appication, and what's the difference ?

Hi,

I think you should design it separately.

For example, you need to insert 1 record to the master table and 2 records to the detail table. You should put these three command in one transaction. After you successfully insert the master record. you can retrieve the id of that record, then use that id to insert detail records. When all the insert command successfully executed, call commit method.

If you need to insert 1 record to the master and N records to the detail. It's hard for me to do these N+1 command in one stored procedure. I'm not good at stored procedure, forgive me if some one can do it easily.

__________________
Shaalini.S
Be the Best of Whatever you are...

Hi,

the transact and commit transact I have to use inside the stored procedure or outside in my VB. application in your opinion ? Thanks again for your help.

Hi all,

SqlTransaction is always used in your .NET code, not in the DB stored procedure. You can follow the link I gave you at the first post.

__________________
Shaalini.S
Be the Best of Whatever you are...

First of all thanks for answer my question, actually I ask if it's better to use 2 stored procedure, one per tables, separated, and if in this way it's better using transaction inside my stored procedure or using it outside in my VB appication, and what's the difference ?
Thanks again, best regards