design of the classes of data access layer

hi all

for designing the data access logic layer,if i have 3 table that relate to each other ,for example contact,address,sometime with creating the customer address should be created too.

should i design one class for each of them seperately and design 1 class(in bussiness layer or data access layer(i am not sure)) for calling the create stored procedures of 2 tables and put the 2 stored procedures in one transaction or not ,should I design 1 class for both tables and add the transaction in sp part?

because i need also the sp of creating each table seperately.

thanks

I wouldn't use a stored procedure for creating a table, if that is what you are suggesting. If you do not want the data to persist, use a temporay table, then drop it when it is no longer in use. As for the classes, the best and easiest option is to create a class (business layer) for each table, and a dataset class (data access layer)for each table. Then the class interacts with the dataset to validate data, insert update and delete data, and should contain constants for the procedure names, and separate methods which call stored procedures to do the inserting, deleting and updating of data, as well as loading the data. An instance of the data access class can be passed into these methods, or form a member variable of the business layer class. If you are updating several tables at a time, then use one transaction, ensuring that all the changes to all the related table will be rolled back on error.

However, there are many ways to approach this, whatever way works best for your data model and business model is best.

HTH

|||

thank you for your answere ,but maybe i have displayed something wrong ,my purpose of create table means inserting the data in table ,according to that I want to know that how do i design my classes that if update of one table relate to update another table ?

should i put the transaction in the store procedure in that case the seperate class for each table is not sutable?,otherwise i need the seperate clase for each table ,should i have third class for joining 2 class (in bussiness layer or data layer?)

thanks

|||You can interact with multiple classes from the front end inside one single transaction. ALternatively, you can enforce referential integrity across tables using relationships, or create update triggers to update other tables based on updates to one table.