Inserting Records into SQL Server Tables

[Guest]

I am currently writting an application in VB .Net and SQL Server. I am trying to insert new records into two SQL Server tables and would like to know whether it is best to insert the records using a stored procedure or through a dataset. Currently I have a form that consists of two parts. One part of the form collects basic customer information (name, address, etc.) the second part of the form allows the user to to select customer categories that define a customer. So the user of the app can define the customer as an accountant and a real estate boker, or several other categories. I am using sqlClient.SqlParameters to pass the values of the text boxes on the form to a stored procedure to insert new records into a customer table for data on the first part of the form (the basic information). I would like to write the code to update a categories table in SQL Server, (there is a one-to-many relationship between the customer and category tables), by storing the customer categories (second part of form) in an array and using a second stored procedure to update the categories table. I am not sure if this is the best approach. I am thinking that perhaps I should be building empty datatables, with the same structure as my SQL Server tables, in a dataset and then use the form to populate the datatables, create relationship and constraints for the datatables and then use the dataadapter methods to insert the records into SQL Server. Any suggestions would be most appreciated.

 

[William Ryan eMVP]

<<I should be building empty datatables, with the same structure as my SQL
Server tables, in a dataset and then use the form to populate the
datatables, create relationship and constraints for the datatables and then
use the dataadapter methods to insert the records into SQL Server.>> In my
experience, this is a very straightforward approach that's easy to
implement, maintain and use. The client side validation is great and you
can still implement your own at the UI level DataRelations are really
powerful, so are primary keys. Moreoever you can create autoincrement
columns, expression(calculated columns) and much more. I think this is the
way to go.

Cheers,

Bill

www.devbuzz.com
www.knowdotnet.com

I am currently writting an application in VB .Net and SQL Server. I am

trying to insert new records into two SQL Server tables and would like to
know whether it is best to insert the records using a stored procedure or
through a dataset. Currently I have a form that consists of two parts. One
part of the form collects basic customer information (name, address, etc.)
the second part of the form allows the user to to select customer categories
that define a customer. So the user of the app can define the customer as an
accountant and a real estate boker, or several other categories. I am using
sqlClient.SqlParameters to pass the values of the text boxes on the form to
a stored procedure to insert new records into a customer table for data on
the first part of the form (the basic information). I would like to write
the code to update a categories table in SQL Server, (there is a one-to-many
relationship between the customer and category tables), by storing the
customer categories (second part of form) in an array and using a second
stored procedure to update the categories table. I am not sure if this is
the best approach. I am thinking that perhaps I should be building empty
datatables, with the same structure as my SQL Server tables, in a dataset
and then use the form to populate the datatables, create relationship and
constraints for the datatables and then use the dataadapter methods to
insert the records into SQL Server. Any suggestions would be most
appreciated.