我想使用 DataGridView 来显示和更新来自两个联接的 MSSQL 表的数据，但我无法弄清楚如何显示我想要的数据并使其可更新。
在本例中，我想显示特定机器类型的物料清单(在下面的代码中为"HX"(。"零件"是独特的机器组件(因此包含库存水平，零件图纸等(，而"项目"是零件在特定上下文或机器内组件中的"实例"，因此包含该用途的数量，描述等。
从DB/模型的角度来看，这些表具有多(项目(到一个(部件(的关系。
下面的代码显示了我能想到的所有 Linq 查询结构，其中没有一个能满足我的需求：
Public Class Form1
' Entity Framework members
Private context As New EJEFData.CorporateEntities
Public Sub New()
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
' This displays ALL 'Item' fields and can be updated via the DataGridView.
' However, it doesn't show any of the related 'Part' fields which I need
Dim data1 = From i In context.Items
Where i.Type = "HX"
Order By i.Item1
Select i
' This displays all the columns I want (and no more) and demonstrates the the magic of entity framework as the table join
' is all part of the model! However, the output is read only so can't change the data.
Dim data2 = From i In context.Items
Where i.Type = "HX"
Order By i.Item1
Select i.Part.Stock, i.Model, i.Item1, i.Part.DrawingType, i.Description, i.Part.Supplier, i.Part.SuppliersDescription, i.Qty, i.Status
' This displays just two columns on the DataGridView: text representations of the the particular 'Part' and 'Item' classes...
Dim data3 = From i In context.Items
Join p In context.Parts On i.PartID Equals p.ID
Where i.Type = "HX"
Order By i.Item1
Select i, p
' ...However, the underlying data is there and can be edited by uncommenting:
'Dim itemList = data3.ToList
'itemList(0).i.Description = InputBox("New Value:",, itemList(0).i.Description)
'm_context.SaveChanges()
' This seems to be another way of achieving data2 but with the join defined in the query rather than using the Entity Framework model structure
' Again, it is read only.
Dim data4 = From i In context.Items
Join p In context.Parts On i.PartID Equals p.ID
Where i.Type = "HX"
Order By i.Item1
Select p.Stock, i.Model, i.Item1, p.DrawingType, i.Description, p.Supplier, p.SuppliersDescription, i.Qty, i.Status
' This dispays an editable DataGridView (perfect!) BUT doesn't save the changes back to the database
Dim data5 = From i In context.Items
Where i.Type = "HX"
Order By i.Item1
Select New With {.Stock = i.Part.Stock, .Model = i.Model, .Item = i.Item1, .DrawingType = i.Part.DrawingType,
.Description = i.Description, .Supplier = i.Part.Supplier, .SuppliersDescription = i.Part.SuppliersDescription, .Qty = i.Qty, .Status = i.Status}
' Bind the data to the DataGridView via a BindingSource (data1 can be changed to data2, data3 etc.)
PartBindingSource.DataSource = data1.ToList
DataGridView1.DataSource = PartBindingSource
End Sub
' Update the database when leaving a modified DataGridView row
Private Sub DataGridView1_RowValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
context.SaveChanges()
End Sub
End Class
我已经尝试了一种使用自定义SQL查询填充和更新的数据集的低级方法，但是在整个项目中使用它将非常耗时且容易出错。无论如何，根据MSDN：
实体框架是 Microsoft 推荐的数据访问技术 新应用
但是，如果实体框架确实是此应用程序的错误方法，那么我很高兴听到任何其他建议！
这就是
我尝试过的
public class DataBindingProjection
{
public string name { get; set; }
public int apmntid { get; set; }
public string servicedesc { get; set; }
public string cusid { get; set; }
public string empid { get; set;
public bool isdelivered { get; set; }
}
context = new HHCSEntities();
var query = from d in context.DeliveryOrders
join a in context.Appointments on d.Id equals a.Id
join s in context.ServiceCategories on d.ServiceId equals s.ServiceId
join e in context.Employees on d.EmpId equals e.EmpId
select new DataBindingProjection
{
dono = d.Name,
apmntid = a.Id,
servicedesc = s.ServiceDesc,
cusid = a.CusId,
empid = d.EmpId,
shortname = e.ShrtName,
isdelivered = d.IsDelivered
};
dataGridView1.DataSource = query.ToList();
dataGridView1.Columns[1].DataPropertyName = "Name";
dataGridView1.Columns[2].DataPropertyName = "Id";
dataGridView1.Columns[3].DataPropertyName = "servicedesc";
dataGridView1.Columns[4].DataPropertyName = "apmntid";
dataGridView1.Columns[5].DataPropertyName = "empid";
dataGridView1.Columns[9].DataPropertyName = "isdelivered";
您可以使用这种方式更新日期重度视图表
BindingSource bi = new BindingSource();
bi.DataSource = query.ToList();
dataGridView1.DataSource = bi;
dataGridView1.Refresh();
最后，我使用了问题中的"Data1"方法，但创建了一个继承的 DataGridView 类，该类可以接受列绑定中的嵌套属性(例如，获取 Item.Part.Stock 的 Part.Stock (。但是，这意味着无法自动生成这些列，因为没有自动检测需要哪些嵌套属性的机制。 有关 DataGridView 代码，请参见下文(我已经删除了一些可疑的错误检查，因此如果它看起来不完整，这可能就是原因(。 这基本上是通过读取列绑定路径以及如果它包含"."来使用反射获取或设置属性来工作的。
Imports System.Reflection
Imports System.Linq.Dynamic
Public Class NestedSourceGrid
' Inherits DataGridView in designer generated code
Private _commitAttempt As Boolean
''' <summary>
''' Stores list of properties for nested property bound columns
''' Key = column name
''' </summary>
Dim BindPropertyLists As New Dictionary(Of String, List(Of String))
Public Sub New()
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
DoubleBuffered = True
End Sub
Private Function GetBindProperty(ByVal [property] As Object, ByVal propertyName As String) As Object
Dim retValue As Object = Nothing
If propertyName.Contains(".") Then
Dim thisPropertyName As String
thisPropertyName = propertyName.Substring(0, propertyName.IndexOf("."))
propertyName = propertyName.Substring(propertyName.IndexOf(".") + 1)
retValue = GetBindProperty([property].GetType().GetProperty(thisPropertyName).GetValue([property], Nothing), propertyName)
Else
' Return this (bottom) property value
Dim tempValue = [property].GetType().GetProperty(propertyName).GetValue([property], Nothing)
If tempValue Is Nothing Then Return ""
retValue = tempValue
End If
Return retValue
End Function
Private Sub SetBindProperty(ByVal [property] As Object, ByVal propertyName As String, ByVal value As Object)
' If there are further property nest levels
If propertyName.Contains(".") Then
Dim thisPropertyName As String
thisPropertyName = propertyName.Substring(0, propertyName.IndexOf("."))
propertyName = propertyName.Substring(propertyName.IndexOf(".") + 1)
SetBindProperty([property].GetType().GetProperty(thisPropertyName).GetValue([property], Nothing), propertyName, value)
Else ' Bottom property level
' Set the value to this (bottom) property
[property].GetType().GetProperty(propertyName).SetValue([property], value)
End If
End Sub
Private Function GetBindPropertyType(ByVal propertyType As Type, ByVal propertyName As String, ByVal columnName As String) As Type
Dim retValue As Type = Nothing
' If there are further property nest levels
If propertyName.Contains(".") Then
Dim thisPropertyName As String
thisPropertyName = propertyName.Substring(0, propertyName.IndexOf("."))
propertyName = propertyName.Substring(propertyName.IndexOf(".") + 1)
retValue = GetBindPropertyType(propertyType.GetProperty(thisPropertyName).PropertyType, propertyName, columnName)
Else ' Bottom property level
Dim propertyInfo As PropertyInfo
propertyInfo = propertyType.GetProperty(propertyName)
retValue = propertyInfo.PropertyType
End If
Return retValue
End Function
Private Sub MultiSourceGrid_RowsAdded(sender As Object, e As DataGridViewRowsAddedEventArgs) Handles Me.RowsAdded
If Rows(e.RowIndex).DataBoundItem Is Nothing Then Exit Sub
' Clear the BindPropertyLists so Add can't throw exception
BindPropertyLists.Clear()
For Each col As DataGridViewColumn In Columns
If col.DataPropertyName.Contains(".") Then
' Create new (empty) list for this column in BindPropertyLists
BindPropertyLists.Add(col.Name, New List(Of String))
col.ValueType = GetBindPropertyType(Rows(0).DataBoundItem.GetType, col.DataPropertyName, col.Name)
' Insert the values from 'complex bound' source objects
For i As Integer = e.RowIndex To e.RowIndex + e.RowCount - 1
Rows.Item(i).Cells.Item(col.Index).Value = GetBindProperty(Rows(i).DataBoundItem, col.DataPropertyName)
Next
End If
Next
End Sub
Private Sub MultiSourceGrid_CellValidating(sender As Object, e As DataGridViewCellValidatingEventArgs) Handles Me.CellValidating
' If this is an 'complex bound' column, attempt to set the value to the source object
If (Columns(e.ColumnIndex).DataPropertyName.Contains(".")) And e.RowIndex >= 0 And e.ColumnIndex >= 0 Then
Try
_commitAttempt = True
CommitEdit(DataGridViewDataErrorContexts.Formatting) 'DataGridViewDataErrorContexts.Commit Or DataGridViewDataErrorContexts.Parsing)
Catch ex As Exception
MsgBox("[during cell validating]" + vbNewLine + ex.Message)
e.Cancel = True
End Try
_commitAttempt = False
End If
End Sub
Private Sub MultiSourceGrid_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles Me.CellValueChanged
' If this is an 'complex bound' column, attempt to set the value to the source object
If _commitAttempt Then
Dim cell As DataGridViewCell = Rows.Item(e.RowIndex).Cells.Item(e.ColumnIndex)
SetBindProperty(Rows.Item(e.RowIndex).DataBoundItem, Columns.Item(e.ColumnIndex).DataPropertyName, cell.Value)
End If
End Sub
End Class