DataGrid - clever way to update DataSet

[st]

Hi,

I populate a DataGrid programmatically from a DataSet that has its
DataAdapter SQL built dynamically, i.e. the DataGrid contents, field
names and field count is not fixed.

I am at the point where I need to write code to update the bound
DataSet contents to reflect DataGrid row edit (singular) and I am
looking for a slick way to do this. Simplistically, something along the
lines of ds(dgindex) = dg(dgindex).

Is this possible? Would I need to ascertain ds field names and
iterate/correspond columns in the DataGrid.

I am working in C#.

Many thanks,

Simon

 

[Scott M.]

This is not as easy as you may think (or want it to be)...

The row index of the row in the DataGrid that you are editing is not
necessarily the same row index in your DataSet. What makes matters a bit
more confusing is that if you allow paging and/or sorting in your grid, the
2 index values may be way off from each other.

You must essentially query the dataset using some unique data from the row
being edited in the DataGrid (primary key) to locate the row of the dataset
that matches the row being edited in the DataGrid.

In addition, if you are using bound controls to show the row data in the
DataGrid, you can only grab those control values by asking for the control
by name. So, this code is an example of how to get the row in the DataSet
that matches the row in the DataGrid being edited:

Dim theRowToUpdate As DataRow = _
ds.Tables(0).Select("PartNumber='" & _
CType(e.Item.FindControl("lblPartNum"), Label).Text & "'")(0)



This example assumes you have a column in the DataSet named "PartNumber" and
a label in the DataGrid called "lblPartNum" that contains Primary Key
information about the record being edited. The "FindControl" method returns
an array, so at the end of the expression, I have "(0)" because I want the
first (and when using Primary Key data) and only row found.

You must also cast the control that is found into the correct type because
"FindControl" will only return objects and you'll want to extract the bound
data from the control via its bound property, in this case "Text".

As you can see, I am finding the row that contains a record with a primary
key value that matches the primary key data displayed in the grid. I am
then making a pointer to that row for easy access to it going forward.

Now, to update the rest of the data in the DataSet row, you'd need something
like this:

With theRowToUpdate
.Item("Name") = DirectCast(e.Item.FindControl("txtName"),
TextBox).Text
.Item("MFG") = DirectCast(e.Item.FindControl("txtMFG"), TextBox).Text
.Item("WholesalePrice") =
CType(DirectCast(e.Item.FindControl("txtWP"), TextBox).Text, Decimal)
.Item("RetailPrice") = CType(DirectCast(e.Item.FindControl("txtRP"),
TextBox).Text, Decimal)
.Item("OnHand") = CType(DirectCast(e.Item.FindControl("txtOnHand"),
TextBox).Text, Integer)
.Item("PicURL") = DirectCast(e.Item.FindControl("txtPicURL"),
TextBox).Text
.Item("Style") = DirectCast(e.Item.FindControl("txtStyle"),
TextBox).Text
.Item("Description") =
DirectCast(e.Item.FindControl("txtDescription"), TextBox).Text
End With

Now, after your DataSet is updated you need to call
DataAdapter.Update(dataSet) to get the original datasource updated and you
need to rebind your grid.

 

[st]

Hi Scott,

Thanks for the information.

As the source table for the DataGrid can change, I need a solution that
doesn't hard-code field names into the update.

Has anybody created a routine that will cope with this situation?

Thanks,

Simon

 

[Scott M.]

The general procedure would still remain the same. You will have to come up
with a way of "dynamically" determining the grid values. Determining the
field names dynamically is a simple matter of looking at the dataset columns
collection and then the column name property of each column object.

 

[Val Mazur \(MVP\)]

Hi,

If you are using SQL Server, then you could use CommandBuilder class that
will allow you automatically build INSERT, UPDATE and DELETE SQL statements
for your DataAdapter based on your SELECT statement. I believe it should
work for you

 

[Cor Ligthert]

Simon,

I don't think that I understand your problem completly.

However when you set the datasource to the datatable.defaultview than you
can forever find the row by using that defaultview (although it is a
datarowview).

Another method and probably for you the most easy one is using the
currencymanager, which gives you forever the current row that is in the
datagrid.

http://msdn.microsoft.com/library/d...stemwindowsformscurrencymanagerclasstopic.asp

When I look at your subject than the answer is just update the datatable
using the commandbuilder as Val already wrote. Don't forget to do an
endcurrentedit before you update.

http://msdn.microsoft.com/library/d...mscurrencymanagerclassendcurrentedittopic.asp

I hope this helps,

Cor

 

[Scott M.]

How does this help the OP? The question wasn't how to dynamically generate
tables, it was how to dynamically update a dataset from changes in a grid
when the dataset has been created dynamically.

In addition, a CommandBuilder is also available for other data sources
besides SQL, however it is generally considered NOT a good idea to use it as
it is limited in situations where it will create correct statements.

 

[Scott M.]

How does the DefaultView of the DataTable return the index in the DataSet
that corresponds to the row being edited in the DataGrid?