用C#中的Scripting.Dictionary等效构建新数据表

我对C#还是比较陌生的,以前有一些VBA经验。当前正在尝试制作一个小的数据库工具来比较记录。目前,我已经编写了一个能够连接到数据库并将数据写入C#数据表的代码。但是,我想添加与VBA代码相同的功能-使用字段比较来构建新的数据表。

Do I need to convert datatables to Dictionary and perform same actions? Can I perform "building new datatable" directly to datatable? Do I need to add Scripting.Dictionary as reference and perform similar code to what I have in VBA? Any suggestions or maybe even examples to get it working?

这是我在C#中拥有的东西:

string queryString1 = "select YRNRO,SEARCHNAME,NAMEA,NAMEB from FIRMA";
string queryString2 = "select YRNRO,SEARCHNAME,NAMEA,NAMEB from FIRMA";

using (OdbcConnection dbConnection1 = new OdbcConnection(connectionString1))
{
    dbConnection1.Open();
    OdbcDataAdapter dadapter1 = new OdbcDataAdapter();
    dadapter1.SelectCommand = new OdbcCommand(queryString1, dbConnection1);

    dadapter1.Fill(t1);

}
using (OdbcConnection dbConnection2 = new OdbcConnection(connectionString2))
{
    dbConnection2.Open();
    OdbcDataAdapter dadapter2 = new OdbcDataAdapter();
    dadapter2.SelectCommand = new OdbcCommand(queryString2, dbConnection2);

    var t2 = new DataTable("FIRMA");
    dadapter2.Fill(t2);

    var tResult = new DataTable();
    var tTemp = new DataTable();

    dadapter2.Fill(t2);

}

这是目前正在使用的VBA:

    Sub BuildListWithClass()

        'Declare all the variables
        Dim Sheet1: Set Sheet1 = Sheets("Country1")
        Dim Sheet2: Set Sheet2 = Sheets("Country2")
        Dim Sheet3: Set Sheet3 = Sheets("Main")
        Dim key

        Dim x As Long, arr1 As Variant, arr2 As Variant, lst As clssList
        Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

        'Fill 1st array from sheet1
        With Sheet1
            x = .Cells(.Rows.Count, 4).End(xlUp).Row
            arr1 = .Range("A2:D" & x).Value
        End With

        'Fill 2nd array from sheet2
        With Sheet2
            x = .Cells(.Rows.Count, 4).End(xlUp).Row
            arr2 = .Range("A2:D" & x).Value
        End With

        'Load 1st array into dictionary with use of class
        For x = LBound(arr1) To UBound(arr1)
            Set lst = New clssList
            lst.Number1 = arr1(x, 1)
            lst.Searchname = arr1(x, 2)
            lst.Namea = arr1(x, 3)
            lst.Nameb = arr1(x, 4)
            dict.Add arr1(x, 2), lst
        Next x

        'Load 2nd array into dictionary with test 
        'Test is if there are duplicates in SEARCHNAME
        For x = LBound(arr2) To UBound(arr2)
            If dict.Exists(arr2(x, 2)) Then
                If Trim(arr2(x, 4)) = Trim(dict(arr2(x, 2)).Nameb) Then
                    dict(arr2(x, 2)).Number2 = arr2(x, 1)
                Else
                    Set lst = New clssList
                    lst.Number2 = arr2(x, 1)
                    lst.Searchname = arr2(x, 2)
                    lst.Namea = arr2(x, 3)
                    lst.Nameb = arr2(x, 4)
                    dict.Add arr1(x, 2) & "x", lst
                End If
            Else
                Set lst = New clssList
                lst.Number2 = arr2(x, 1)
                lst.Searchname = arr2(x, 2)
                lst.Namea = arr2(x, 3)
                lst.Nameb = arr2(x, 4)
                dict.Add arr2(x, 2), lst
            End If
        Next x

        'Transpose dictionary into sheet3
        With Sheet3
            x = 2
            For Each key In dict.keys
                .Cells(x, 1).Value = dict(key).Number1
                .Cells(x, 2).Value = dict(key).Number2
                .Cells(x, 3).Value = dict(key).Searchname
                .Cells(x, 4).Value = dict(key).Namea
                .Cells(x, 5).Value = dict(key).Nameb
                x = x + 1
            Next key
        End With

    End Sub
评论