0

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?

  1. Should I have 2 Data Access Classes - One for Main & the other for Sub?

  2. 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()?

  3. Tutorial with Object Data Source using the scenario with Main & Sub Table will be much appreciated.

user450143
  • 435
  • 1
  • 6
  • 15

2 Answers2

0

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)

KV Prajapati
  • 93,659
  • 19
  • 148
  • 186
  • 1
    @abatishchev SQLDataSource is good and valid if it's just a demo, a prototype, or a quick hack. 1. http://stackoverflow.com/questions/1207295/sqldatasource-vs-objectdatasource – KV Prajapati Sep 22 '11 at 07:39
  • 1
    I don't agree with Marc. SqlDataSource brings a lot of built-in features like paging, sorting, etc - everything you need to implement manually with ObjectDataSource, so yes, there is a border when you need to switch to more powerful control but it's not just for demos – abatishchev Sep 22 '11 at 07:46
  • Currently my database is small, But there are chances it may become Medium size. Considering this I may change the design to Objectsource. How stable is EntityFramework? – user450143 Oct 01 '11 at 08:59
0

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.

user450143
  • 435
  • 1
  • 6
  • 15