DataAdapter:是否可以填充集合类型而不是数据表/数据集?

发布于 2024-09-12 15:01:15 字数 2803 浏览 4 评论 0原文

这更多的是我问自己的一个理论问题。 我记得有序列表的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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

尹雨沫 2024-09-19 15:01:15

既然这很简单,为什么不使用 DataReader 呢?在 C# 中,您需要执行以下操作:

List<int> primaryKeys = new List<int>();

using (SqlConnection conn = new SqlConnection("your connection string"))
{
    SqlCommand command = new SqlCommand("SELECT Id FROM Table", conn);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        // Loop through each record.
        while (reader.Read())
        {
            primaryKeys.Add(reader.GetInt32(0));
        }
    }
}   

Why don't you use a DataReader instead since this is pretty trivial? In C# you'd do the following:

List<int> primaryKeys = new List<int>();

using (SqlConnection conn = new SqlConnection("your connection string"))
{
    SqlCommand command = new SqlCommand("SELECT Id FROM Table", conn);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        // Loop through each record.
        while (reader.Read())
        {
            primaryKeys.Add(reader.GetInt32(0));
        }
    }
}   
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文