2 questions about Sql Data Adapter
1. When we use Data Adapter Configuration Wizard to make Data Adapter, there is a checkbox option:
"Refresh the dataset: Indicates whether you want the wizard to generate code that re-reads a record from the database after updating it. The Select statement is added to the corresponding command object in the adapter and is executed immediately after the Update or Insert command has finished. This gives you a refreshed view of a record, including values inserted by the database such as default and auto-incremented values."
When I create Data Adapter from code, with CommandBuilder, he doesn't put that Select SQL statement in UPDATE and INSERT commands. How to do this? A can't find Refresh_the_dataset option in CommandBuilder.
2. In case we have many records updated, it's better to use .fill method after .update. That will add new records, update changed records (if table have defined primary key) but not remove deleted rows (?!). How to remove deleted fows from table with .fill method. It's stupid to use first .clear then .fill because it is dificult to hold position in binded controls like datagrids.
[1142 byte] By [
Gosovic] at [2008-2-28]
1. There isn't one. The CommandBuilder is useful but it has limitations. You would need to write at least some of the SQL yourself. The CommandBuilder has methods that return the actual commands which you can then edit.
2. If I'm not mistaken, calling AcceptChanges should remove Deleted rows. I'm actually just guessing because I think this is what should happen. If it doesn't, you'll need to remove those rows yourself individually.
1. With CommandBuilder you have one object:
Private objCB As System.Data.SqlClient.SqlCommandBuilder
objCB = New SqlClient.SqlCommandBuilder(SqlDataAdapter1)
There is no Commands for edit, except
objCB.GetInsertCommand.CommandText
but, it is ReadOnly or something and doesn't accepts changing.
Only way to edit commands is to create Command objects :
Class
Private SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Private SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
Private SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
Sub
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
SqlInsertCommand1.CommandText = objcb.GetInsertCommand.CommandText + ";" + SqlDataAdapter1.SelectCommand.CommandText
SqlUpdateCommand1.CommandText = objcb.GetUpdateCommand.CommandText + ";" + SqlDataAdapter1.SelectCommand.CommandText
SqlDeleteCommand1.CommandText = objcb.GetDeleteCommand.CommandText
objCB.Dispose()
... and there is to much code for stupid operation. U have many methods in this class with accessing to dataset and I'll have to put this code in everyone of them.
2. I have multiuser environment. I can remove my deleted rows but I want with .fill to automaticly remove all deleted. AcceptChanges doesn't help.
I have read, although I can't remember where, that you can use the GetInsertCommand, etc. to get an SqlCommand that you can edit and then assign to the InsertCommand property, etc. of the SqlDataAdapter. The CommandText property of an SqlCommand is not ReadOnly so you should be able to edit it. Coding data access components manually is definitely tedious. Even if you don't use the Data Adapter Configuration Wizard, you can still create your adapters and/or commands in the designer to make things a bit easier.
Perhaps you could write a procedure that takes a DataTable or DataSet and removes all DataRows with a RowState equal to Deleted. You could then simply call that function whenever and wherever it's needed. It's not as neat as a built-in solution but it's not a big deal. The alternative would be to create your own classes that inherit from the existing data access classes and then code in this behaviour yourself.
I have table in DataSet only for DataGrid. That table is built from 5-6 releated tables form server. DA wizard cannot create UPDATE, INSERT or DELETE commands for that table. For that I use DA for each of original tables. They have been called from Class (therefore I can't use designer to create DA's). So, when I delete row from table and call update method, it realy delete that row at server. After that filling a DataSet (with he's DA) is not removing rows than allready missing from server. There is no Rowstate.Deleted rows. I only want to .fill method remove rows that not exists on server. He sinchronise by adding, changing but not delete rows. Only way is to call .clear before .fill.
