Insert to db from new strongly typed datarow

[DannyT]

I have a strongly typed dataset, called "Customers" for example.

My user chooses to create new customer, i have no existing dataset, so i create a new instance of my Customers dataset.

dim dsCustomers as new Customers

and instantiate a new CustomersDataRow.

dim drCustomer as new dsCustomers.Tables(0).NewRow()

At this point i've not Filled my ds as I know i'm creating a new record and don't want to make an unneccessary call to the db.

I populate my NewRow with the required data.

 

How do I now insert this row into the actual database? I'm trying to pass it to my attempt at a data access layer (which currently has no reference to the original dataset). I seem to be stuck with a detached datarow and can't figure out how to commit it to the database?

 

I'm sure this shouldn't be as difficult as i'm making it but i seem to be having a slow day so any help much appreciated.

 

Regards,

 

Dan

[DannyT]

Okay this got the better of me so I resorted to filling the dataset, I then needed to maintain a reference to that dataset which I have done, now I'm sending the dataset to a commitToDb function I realise I need a reference to the original dataadapter I filled it from?! Is this correct?

 

How do I update a dataset to the database using a new dataadapter? I'm sorely missing a trick somewhere as using strongly datasets seems to be very tightly coupling my users' interaction with the data itself.

[tfowler]

Note: I use a SQL database most of the time, so I will use the SQL objects in my description.

 

You need to fill in the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter and then call the SqlDataAdapter.Update() method to update the database with the changes to your DataSet. If you don't want to manually create the SqlDataAdapter command properties, you can always have the SqlCommandBuilder create them for you.

 

Todd

[DannyT]

ah thank you tfowler! I had explored that before but dismissed it, after your post I went back and realised where i was going wrong.

 

For the benefit of others, here is how to update a dataset with a new dataadapter:

 

Public Function ExecuteUpdateDS(ByVal ds as DataSet, ByVal sql as String) as Integer
   Dim da As New OleDbDataAdapter 'create new dbAdapter
   da.SelectCommand = New OleDbCommand(sql, myConnection) ' same select command that was used in the initial dataset population
   Dim autogen As New OleDbCommandBuilder(da) ' CommandBuilder, auto generates Update, Insert and Delete commands
   Dim rowsUpdated as Integer= da.Update(ds, "Customers") ' commit update
   ds.AcceptChanges() ' accept modifications to dataset
   ExecuteUpdateDS = rowsUpdated
End Function

[PlausiblyDamp]

If you have a strongly typed dataset you can access this functionality in a more typesafe manor - something like....

ds.Customers.Customer.AddCustomerRow(...)

[DannyT]

Thanks plausibly, I am doing that, the above is just a generic function that I can throw any dataset at once updates have been made. I'm currently evaluating strongly typed datasets vs custom classes.