Using datatables to insert records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to use a stored procedure to insert records into a table in SQL Server using a datatable as the source, but I am not sure of the commands needed to do this. I have manually created two datatables with the same structure as two SQL Server tables, using VB .Net code, and have added them to a new dataset. I have set the constraints and relationships between the tables. One table is a customer table the other a category table. There is a one-to-many relationship between the two tables. I have populated the tables, so that the customer table has one record and the category table has 2 records. When I setup my parameters, I reference each row of the datatable individually, using tCustomerTable.Rows(0).Item, but using this approach I would have to loop through all rows of the datatable. Is there a way to pass the datatable as an object and have the dataadapter loop through the rows? Is there a better approach to this

Here is part of my code
CustomerDataAdapter.InsertCommand = New SqlComman
CustomerDataAdapter.InsertCommand.CommandText = "spInsertDetail
CustomerDataAdapter.InsertCommand.Connection = sqlCon
CustomerDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedur

'Instruct InsertCommand to use outparameter to update the datase
CustomerDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameter


Dim pCustId As SqlClient.SqlParamete
pCustId = New SqlClient.SqlParameter("@customer_id", SqlDbType.Int, 4
pCustId.Direction = ParameterDirection.Outpu
CustomerDataAdapter.InsertCommand.Parameters.Add(pCustId

Dim pCust_lname As New SqlParameter("@customer_lname", SqlDbType.VarChar, 25, ParameterDirection.Input, False, 0, 0, "customer_lname", DataRowVersion.Current, tCustomerTable.Rows(0).Item("customer_lname")
CustomerDataAdapter.InsertCommand.Parameters.Add(pCust_lname

Dim pCust_fname As New SqlParameter("@customer_fname", SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0, "customer_fname", DataRowVersion.Current, tCustomerTable.Rows(0).Item("customer_fname")
CustomerDataAdapter.InsertCommand.Parameters.Add(pCust_fname
 
Gary:

you are basically on the right track and essentially, if I understand you
correctly, you just want to have to call update instead of looping through
everything. I could walk you through it, but there's a lot easier way we
can try first. Use the visual tools on the dataadapter and let it build
the update commands for one table for you. The main difference you'll see
is that it'll pull on DataRowVersion.Original which it'll use for the
"Original_" value and then it will ignore rowstate for the first part. The
one common thread is the column mapping, which in this case is the crux of
the battle. once you have that set up correctly, update should work
provided your command matches. After you look through it once, you'll get
the idea and you can decide what kind of concurrency you want to employ.
Bill Vaughn also has a great article on his sight www.betav.com ->
Articles -> MSDN - Weaning Developers from the CommandBuilder.

HTH

Bill
Gary said:
I would like to use a stored procedure to insert records into a table in
Click to expand...
SQL Server using a datatable as the source, but I am not sure of the
commands needed to do this. I have manually created two datatables with the
same structure as two SQL Server tables, using VB .Net code, and have added
them to a new dataset. I have set the constraints and relationships between
the tables. One table is a customer table the other a category table. There
is a one-to-many relationship between the two tables. I have populated the
tables, so that the customer table has one record and the category table has
2 records. When I setup my parameters, I reference each row of the datatable
individually, using tCustomerTable.Rows(0).Item, but using this approach I
would have to loop through all rows of the datatable. Is there a way to pass
the datatable as an object and have the dataadapter loop through the rows?
Is there a better approach to this?
Here is part of my code:
CustomerDataAdapter.InsertCommand = New SqlCommand
CustomerDataAdapter.InsertCommand.CommandText = "spInsertDetail"
CustomerDataAdapter.InsertCommand.Connection = sqlConn
CustomerDataAdapter.InsertCommand.CommandType = CommandType.StoredProcedure

'Instruct InsertCommand to use outparameter to update the dataset
CustomerDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters


Dim pCustId As SqlClient.SqlParameter
pCustId = New SqlClient.SqlParameter("@customer_id", SqlDbType.Int, 4)
pCustId.Direction = ParameterDirection.Output
CustomerDataAdapter.InsertCommand.Parameters.Add(pCustId)

Dim pCust_lname As New SqlParameter("@customer_lname",
Click to expand...
SqlDbType.VarChar, 25, ParameterDirection.Input, False, 0, 0,
"customer_lname", DataRowVersion.Current,
tCustomerTable.Rows(0).Item("customer_lname"))
CustomerDataAdapter.InsertCommand.Parameters.Add(pCust_lname)

Dim pCust_fname As New SqlParameter("@customer_fname",
Click to expand...
SqlDbType.VarChar, 20, ParameterDirection.Input, False, 0, 0,
"customer_fname", DataRowVersion.Current,
tCustomerTable.Rows(0).Item("customer_fname"))
 
William

I found the code I was looking for it is
CustomerDataAdapter.Update(tCustomerTable.Select(Nothing, Nothing, DataViewRowState.Added)
 
Back
Top