<back> 2.1. My typical VB.NET desktop application.
(EF DB First and ADO NET Typed Dataset, Tree ways to Binding GridView and ComboBox, Set Timeout, Asynchronous fill table by data)
2.1.1. EF DB First and ADO NET Typed Dataset
I do not stop in this description on EF, because I have big description about EF - Entity Framework missing FAQ (Part 1)., but in this application database is huge.
But EF not give opportunity to us to conveniently manipulate result from View and Stored procedures, therefore I use both ADO.NET way to accessing data
2.1.2. Tree ways to Binding GridView and ComboBox
EF not supported autogenerate fields in DataGridView by result of stored procedures and even not supported combobox without literal string. In fact, EF has support only raw SQL table. Therefore to manipulate name instead literal I usually firstly create Typed Dataset.
This is very old way to accessing data - please look at my description of evolution data access method, but so far nothing a best way.
-
One my SQL procedure (GetMail1) use by input a result of another SQL stored procedure (GetMailBox1). In this case if you use only EF, you will have to:
1: Dim MailList = db1.p_GetMail1(CType(IDMailBox.Items(IDMailBox.SelectedIndex), p_GetMailBox1_Result).idmailbox).ToList
But what next? How to have access to result of procedures without text literals and how to autogenerate columns in tables?
-
There are second way with raw typed Dataset and bind GridView directly to Dataset. In this case you can write:
1: Dim DA1 = New p_GetMail1TableAdapter
2: Dim DT1 = New GetMail1DataSet.p_GetMail1DataTable
3: DA1.Fill(DT1, CType(IDMailBox.Items(IDMailBox.SelectedIndex), p_GetMailBox1_Result).idmailbox)
4: EmailCenterSubformDataGridView.DataSource = DT1 -
In my opinion the best way is to use Design-Time component BindingSource, GridView have to binding to it in design time and you can fill Dataset by DataAdapter, related to this BindingSource. This way give us many benefits, BindingSource has intermediate layer to sort and filtered data and raise events if data set changed.
In this case code template will be look as:
1: PGetMail1BindingSource.DataSource = p_GetMail1TableAdapter.GetData(CInt(e.Argument.MailBox))
In this case I also have convenient way to apply a filter to dataset:
And also I have opportunity to automatically fill columns in GridView.
The similar way to binding I usually use in Combobox (by BindingSource).
As a main goal instead style of binding
1: IDMailBox.DataSource = db1.p_GetMailBox1(IDUser1.SelectedValue).ToList
2: IDMailBox.DisplayMember = "login"
3: IDMailBox.ValueMember = "idmailbox"
I can use name instead string:
2.1.3. Tree ways to Binding GridView and Combobox
If you use EF, Timeout is set simple:
11: Dim db1 As SamanthaEntities
...
19: db1 = New SamanthaEntities
20: db1.Database.CommandTimeout = 60
For Typed Dataset I have some special extension, including timeout setting
27: <Extension()>
28: Public Sub TableAdapterCommandTimeout(Of T As System.ComponentModel.Component)(ByVal TableAdapter As T, ByVal CommandTimeout As Integer)
29: For Each c In TryCast(GetType(T).GetProperty("CommandCollection", System.Reflection.BindingFlags.NonPublic Or System.Reflection.BindingFlags.GetProperty Or System.Reflection.BindingFlags.Instance).GetValue(TableAdapter, Nothing), System.Data.SqlClient.SqlCommand())
30: c.CommandTimeout = CommandTimeout
31: Next
32: End Sub
33:
34: <Extension()>
35: Public Function ListToDataTable(Of T)(ByVal Items As IList(Of T)) As DataTable
36: Dim DataTable As DataTable = New DataTable(GetType(T).Name)
37: Dim Props As System.Reflection.PropertyInfo() = GetType(T).GetProperties(System.Reflection.BindingFlags.[Public] Or System.Reflection.BindingFlags.Instance)
38: For Each Prop As System.Reflection.PropertyInfo In Props
39: DataTable.Columns.Add(Prop.Name)
40: Next
41: For Each Item As T In Items
42: Dim values = New Object(Props.Length - 1) {}
43: For i As Integer = 0 To Props.Length - 1
44: values(i) = Props(i).GetValue(Item, Nothing)
45: Next
46: DataTable.Rows.Add(values)
47: Next
48: Return DataTable
49: End Function
And I use this extension by this way.
21: P_GetMail1TableAdapter.TableAdapterCommandTimeout(60)
22: VGetUsers1BindingSource.DataSource = db1.v_GetUsers1.ToList
2.1.4. Asynchronous fill table by data with BackgroundWorker
Usually I fill data from big SQL base to GridView in async mode by similar way:
8: Dim WithEvents BGW1 As BackgroundWorker
9: Dim BGW1_Prm As New Object
...
78: #Region "GridView"
79:
80: Private Sub GoButton_Click(sender As Object, e As EventArgs) Handles GoButton.Click
81: If IDMailBox.SelectedIndex >= 0 Then
82: 'FillGrid()
83: RowCountLabel.Text = "Please wait"
84: FillGridAsync(CType(IDMailBox.Items(IDMailBox.SelectedIndex), p_GetMailBox1_Result).idmailbox)
85: End If
86: End Sub
87:
88: Sub FillGridAsync(MailBox As String)
89: BGW1 = New BackgroundWorker
90: BGW1_Prm = New With {.MailBox = MailBox}
91: BGW1.RunWorkerAsync(BGW1_Prm)
92: End Sub
93:
94: Private Sub BGW1_DoWork(sender As Object, e As DoWorkEventArgs) Handles BGW1.DoWork
95: Dim DT As GetMail1DataSet.p_GetMail1DataTable
96: Dim Msg As String
97: Try
98: DT = P_GetMail1TableAdapter.GetData(CInt(e.Argument.MailBox))
99: Catch ex As Exception
100: Msg = ex.Message 'There is already an open DataReader associated with this Command which must be closed first.
101: End Try
102: e.Result = New With {.DataTable = DT, .Error = Msg}
103: End Sub
104:
105: Private Sub BGW1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BGW1.RunWorkerCompleted
106: PGetMail1BindingSource.DataSource = e.Result.DataTable
107: If e.Result.Error IsNot Nothing Then
108: RowCountLabel.Text = e.Result.Error
109: Else
110: RowCountLabel.Text = PGetMail1BindingSource.Count & " records"
111: End If
112: End Sub
