Insert a record in Main table & several into sub table
SQL 2008: I have 2 tables TableMain & TableSub. The 2 tables are related via the MainID When I insert 1 records in TableMain, it creates a MainID automatically. I have to use the MainID & then insert several records into the TableSub.
TableMain has 16 parameters. TableSub has 4 parameters for each record not including the MainID.
I am using ASP.NET with SQLDatasource.
If I had a few records in TableSub, I could have used a stored procedure inserted all the records at the same time. Since there will be at least 10+ records, no. of parameters will become unmanageable. Also the no. of records in TableSub wil be variable.
What will be the best approach to accomplish this?
EDIT: ASP.NEt 3.5
If I do go with ObjectDatasource (NO DAL - .XSD file) how do I design my Business Logic Layer/DataAccess Class?
Should I have 2 Data Access Classes - One for Main & the other for Sub?
The Main - Insert() should return the ID & using that I should call the Sub-Insert() - Should this be a part of the Main -Insert() code or should it be explicitly be called from the file that class the Main-Insert()?
Tu开发者_高级运维torial with Object Data Source using the scenario with Main & Sub Table will be much appreciated.
I'd like to suggest you to use EntityFramework in order to solve your problems.
PS: Never use SQLDataSource
. (and I'm sure that you will never get desired result with SqlDatasource)
I used ObjectDatasource with DAL(.cs class) . Did not use the XSD file. No BLL as I felt it was a overkill. But I did code with SQLDatasource & got my queries working & just dropped them into the DAL. Setting up SQLDatasorce was easy with the wizard. I do have 2 DAL classes one for main & one for Sub.
精彩评论