DataGrid and DataTable/DataView Binding

[Sam]

We are having some issues in binding multiple DataTables
within a DataSet to a DataGrid within .NET WinForm
application. Relations and constraints among tables at the
screen level as well within DataBase causes the DataGrid
refresh issues. Some times we get index out of error.
Saving the data back to the database is the main issue.

We have multiple tables/grids within the same winform
screen. Alternate approach we tried was using DataView
instead of DataTables directly to bind with DataGrid
without relations. It looks like performance is very slow
as construction and iterating through DataView takes a lot
of time.

Any pointers would be helpful and highly appreciated.

Thanks,
Sam

 

[Ken Tucker [MVP]]

Hi,

Here is a simple example using the northwind database. I assume you
have imports system.data.sqlclient at the top of your file. It needs 3
datagrids dgemployees, dgorders, dgorderdetails to work.

Dim ds As DataSet

Dim daEmployees As SqlDataAdapter

Dim daOrders As SqlDataAdapter

Dim daOrderDetails As SqlDataAdapter

Dim conn As SqlConnection

Dim strConn As String

Dim strSQL As String

Dim strItem As String

Dim ctrl As Control

For Each ctrl In Me.Controls

If TypeOf ctrl Is DataGrid Then

Dim dg As DataGrid = ctrl

dg.AllowNavigation = False

End If

Next

strConn = "Server = " + Environment.MachineName + "\VSdotNet;"

strConn += "Database = NorthWind;"

strConn += "Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

ds = New DataSet

daEmployees = New SqlDataAdapter("Select * from Employees", conn)

daOrders = New SqlDataAdapter("Select * from Orders", conn)

daOrderDetails = New SqlDataAdapter("Select * from [Order Details]", conn)

daEmployees.Fill(ds, "Employee")

daOrders.Fill(ds, "Orders")

daOrderDetails.Fill(ds, "OrderDetails")

ds.Relations.Add("EmployeeOrder",
ds.Tables("Employee").Columns("EmployeeID"), _

ds.Tables("Orders").Columns("EmployeeID"))

ds.Relations.Add("Order2Details", ds.Tables("Orders").Columns("OrderID"), _

ds.Tables("OrderDetails").Columns("OrderID"))

dgEmployees.SetDataBinding(ds, "Employee")

dgOrders.SetDataBinding(ds, "Employee.EmployeeOrder")

dgOrderDetails.SetDataBinding(ds, "Employee.EmployeeOrder.Order2Details")

End Sub

Maybe this link will help with your problem saving data.
http://support.microsoft.com/default.aspx?scid=kb;en-us;310376

Ken