DataAdapter:是否可以填充集合类型而不是数据表/数据集?
这更多的是我问自己的一个理论问题。 我记得有序列表的BinarySearch(一般集合) )比使用 Datatable.Rows.Find 查找行更快或带有主键值的 DataTable.FindByPK。
因此,我在共享构造函数中填充数据库中的数据表,然后立即填充包含该表中所有主键的列表(Int32)。稍后我将使用 BinarySearch 检查列表是否包含主键值。但是因为数据表无论如何只包含 PK 列,所以我问自己是否有一种方法可以避免填充数据表然后将所有行添加到列表中的巨大开销。 是否可以直接从数据适配器填充通用列表(或其他集合类型)而不是数据表/数据集? 也许我偏离了轨道,还有另一种方法可以避免我错过的额外循环。
将DataTable填充到强类型Dataset和List中的代码:
Private Shared w205CorrectSWUpgrades As New List(Of Int32)
Shared Sub New()
Dim da As New dsDatabaseTableAdapters.W205SWUpgradesTableAdapter
For Each row As dsDatabase.W205SWUpgradesRow In da.Get_W205CorrectSWUpgrades.Rows
w205CorrectSWUpgrades.Add(row.idData)
Next
End Sub
UPDATE: 为了完整起见,我的解决方案(感谢 TheCloudlessSky): 因为 DataAdapter 本身使用 DataReader 来填充 Datatable 或 Dataset,所以最好的方法是使用一个新函数来扩展(从 VS)生成的 DataAdapter 的部分类,该函数返回直接从数据库填充的 List(Int32)。请记住,该部分类必须位于生成的类之外的其他文件中,否则您的源代码将在数据集中的更改时被覆盖。还要记住,它必须位于相同的命名空间中(以 TableAdapters 结尾),并且当然具有相同的名称。
Namespace dsDatabaseTableAdapters
Partial Public Class W205SWUpgradesTableAdapter
Public Function GetListOfW205CorrectSWUpgrades() As System.Collections.Generic.List(Of System.Int32)
Dim list As New System.Collections.Generic.List(Of System.Int32)
Dim command As System.Data.SqlClient.SqlCommand = Me.CommandCollection(0)
Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
Try
If ((command.Connection.State And Global.System.Data.ConnectionState.Open) _
<> Global.System.Data.ConnectionState.Open) Then
command.Connection.Open()
End If
Using reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader
While reader.Read
list.Add(reader.GetInt32(0))
End While
End Using
Finally
If (previousConnectionState = System.Data.ConnectionState.Closed) Then
command.Connection.Close()
End If
End Try
Return list
End Function
End Class
End Namespace
现在业务逻辑和数据访问层仍然严格分离(在单独的项目中):
Private Shared w205CorrectSWUpgrades As List(Of Int32)
Shared Sub New()
Dim da As New dsDatabaseTableAdapters.W205SWUpgradesTableAdapter
w205CorrectSWUpgrades = da.GetListOfW205CorrectSWUpgrades
End Sub
this is more a theoretical question i asked myself.
I remembered that BinarySearch of an ordered List(Collection in general) is faster than finding Rows with Datatable.Rows.Find or DataTable.FindByPK with a primary key value.
Hence i fill a Datatable from Database in a shared constructor and immediately after that a List(of Int32) that contains all primary keys from that table. Later i will check with BinarySearch if the List contains primary-key values. But because the datatable contains only the PK-Column anyway, i asked myself if there is a way to avoid the huge overhead of filling a Datatable and after that adding all Rows to a List.
Is it possible to fill a generic List(or other collection-type) instead of a Datatable/Dataset directly from a Dataadapter?
Maybe i'm off the track and there is another way to avoid the Extra-Loop that i'm missing.
The code of filling the DataTable in a strong typed Dataset and the List:
Private Shared w205CorrectSWUpgrades As New List(Of Int32)
Shared Sub New()
Dim da As New dsDatabaseTableAdapters.W205SWUpgradesTableAdapter
For Each row As dsDatabase.W205SWUpgradesRow In da.Get_W205CorrectSWUpgrades.Rows
w205CorrectSWUpgrades.Add(row.idData)
Next
End Sub
UPDATE:
For the sake of completeness my solution (thanks TheCloudlessSky):
Because DataAdapter itself uses a DataReader to fill a Datatable or a Dataset, the best way was to extend the (from VS) generated partial Class of the DataAdapter with a new function that returns the List(of Int32) filled directly from the database. Remember that this partial class must be in an other file than the generated Class, otherwise your sourcecode will be overwritten on changes in the Dataset. Also remember that it must be in the same Namespace(ends with TableAdapters) and of course has the same name.
Namespace dsDatabaseTableAdapters
Partial Public Class W205SWUpgradesTableAdapter
Public Function GetListOfW205CorrectSWUpgrades() As System.Collections.Generic.List(Of System.Int32)
Dim list As New System.Collections.Generic.List(Of System.Int32)
Dim command As System.Data.SqlClient.SqlCommand = Me.CommandCollection(0)
Dim previousConnectionState As System.Data.ConnectionState = command.Connection.State
Try
If ((command.Connection.State And Global.System.Data.ConnectionState.Open) _
<> Global.System.Data.ConnectionState.Open) Then
command.Connection.Open()
End If
Using reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader
While reader.Read
list.Add(reader.GetInt32(0))
End While
End Using
Finally
If (previousConnectionState = System.Data.ConnectionState.Closed) Then
command.Connection.Close()
End If
End Try
Return list
End Function
End Class
End Namespace
Now the business logic and the data access layer are still strictly separated (in separate projects):
Private Shared w205CorrectSWUpgrades As List(Of Int32)
Shared Sub New()
Dim da As New dsDatabaseTableAdapters.W205SWUpgradesTableAdapter
w205CorrectSWUpgrades = da.GetListOfW205CorrectSWUpgrades
End Sub
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
既然这很简单,为什么不使用 DataReader 呢?在 C# 中,您需要执行以下操作:
Why don't you use a
DataReader
instead since this is pretty trivial? In C# you'd do the following: