打开连接后将XML上传到SQL Server数据库中的数据表的正确命令？-解网

问：

我正在测试一些代码，我正在实现这些代码，以便从 XML 文件中提取并放入数据库中。我已经成功写入XML文件，没有问题。现在出现的唯一问题是将信息从 XML 文件成功上载到 SQL Server 数据库表。

将代码作为查询运行成功地将 XML 信息放入数据表中。

但是，当实现到代码中时，我无法让它发挥作用。

我知道要打开连接，但我不熟悉将数据从 XML 文件插入指定 SQL Server 数据库中的 DataTable 的正确命令。

我缺少的正确命令是什么？还是我错过了什么？

如果需要更多信息，我可以列出整个代码的内容。

此时，代码的所有其他方面都按应有的方式运行，甚至生成 XML 文件以导入数据库而不会出现问题。我只有一个问题，即通过代码中设置的连接将 XML 信息正确导入数据表。

感谢您在这件事上的时间和评论。

此处列出了完整代码：

Imports System.Data
Imports System.Data.SqlClient

Module Module1

    Public Sub Main()

        Dim dt As New DataTable("DriveInformation")

        Dim Column_1st As New DataColumn("Date")
        Dim Column_2nd As New DataColumn("Server")
        Dim Column_3rd As New DataColumn("Drive")
        Dim Column_4th As New DataColumn("TotalSpace")
        Dim Column_5th As New DataColumn("UsedSpace")
        Dim Column_6th As New DataColumn("RemainingSpace")
        Dim Column_7th As New DataColumn("GBorTBDrive")
        Dim Column_8th As New DataColumn("DriveActiveStatus")

        dt.Columns.Add(Column_1st)
        dt.Columns.Add(Column_2nd)
        dt.Columns.Add(Column_3rd)
        dt.Columns.Add(Column_4th)
        dt.Columns.Add(Column_5th)
        dt.Columns.Add(Column_6th)
        dt.Columns.Add(Column_7th)
        dt.Columns.Add(Column_8th)

        ' Get information and Write to Console.

        Console.WriteLine("Server Name: {0}", System.Net.Dns.GetHostName)
        Console.WriteLine("Date: {0}", DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt"))
        Console.WriteLine("")

        Dim DriveNumber As Integer = 0
        Dim allDrives() As IO.DriveInfo = IO.DriveInfo.GetDrives()
        Dim d As IO.DriveInfo

        For Each d In allDrives
            Dim DriveName As String = d.Name
            DriveNumber = DriveNumber + 1
            Console.WriteLine(DriveName)
            Console.WriteLine("  Drive type: {0}", d.DriveType)
            If d.IsReady = True And d.TotalSize < 1099511627776 Then
                Console.WriteLine("  Volume label: {0}", d.VolumeLabel)
                Console.WriteLine("  File system: {0}", d.DriveFormat)
                Console.WriteLine(
                "  Total size of drive:   {0, 15} GB ",
                FormatNumber(d.TotalSize / 1024 / 1024 / 1024))

            ElseIf d.IsReady = True And d.TotalSize >= 1099511627776 Then
                Console.WriteLine("  Volume label: {0}", d.VolumeLabel)
                Console.WriteLine("  File system: {0}", d.DriveFormat)

                Console.WriteLine(
                "  Total size of drive:   {0, 15} TB ",
                FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024))
            End If

            If d.IsReady = True And d.TotalSize - d.TotalFreeSpace < 1099511627776 Then
                Console.WriteLine(
                    "  Total used space:      {0, 15} GB",
                FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024))

            ElseIf d.IsReady = True And d.TotalSize - d.TotalFreeSpace >= 1099511627776 Then
                Console.WriteLine(
                    "  Total used space:      {0, 15} TB",
                    FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024))
            End If

            If d.IsReady = True And d.TotalFreeSpace < 1099511627776 Then
                Console.WriteLine(
                    "  Total available space: {0, 15} GB",
                    FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024))
                Console.WriteLine("")

            ElseIf d.IsReady = True And d.TotalFreeSpace >= 1099511627776 Then
                Console.WriteLine(
                "  Total available space: {0, 15} TB",
                FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024))
                Console.WriteLine("")

            End If
        Next

        ' Put Information into DataTable
        For Each d In allDrives
            Dim DriveName As String = d.Name

            If DriveNumber = 1 And d.TotalSize < 1099511627776 Then

                Dim drow1 As DataRow = dt.NewRow

                drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow1("Server") = System.Net.Dns.GetHostName
                drow1("Drive") = DriveName.Remove(1, 2)
                drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
                drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow1("GBorTBDrive") = "GB"
                drow1("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow1)

            ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow1 As DataRow = dt.NewRow

                drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow1("Server") = System.Net.Dns.GetHostName
                drow1("Drive") = DriveName.Remove(1, 2)
                drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow1("GBorTBDrive") = "TB"
                drow1("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow1)

            ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow1 As DataRow = dt.NewRow

                drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow1("Server") = System.Net.Dns.GetHostName
                drow1("Drive") = DriveName.Remove(1, 2)
                drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow1("GBorTBDrive") = "TB"
                drow1("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow1)

            ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow1 As DataRow = dt.NewRow

                drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow1("Server") = System.Net.Dns.GetHostName
                drow1("Drive") = DriveName.Remove(1, 2)
                drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow1("GBorTBDrive") = "TB"
                drow1("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow1)

            ElseIf DriveNumber = 1 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow1 As DataRow = dt.NewRow

                drow1("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow1("Server") = System.Net.Dns.GetHostName
                drow1("Drive") = DriveName.Remove(1, 2)
                drow1("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow1("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow1("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow1("GBorTBDrive") = "TB"
                drow1("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow1)

            End If

            If DriveNumber = 2 And d.TotalSize < 1099511627776 Then

                Dim drow2 As DataRow = dt.NewRow

                drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow2("Server") = System.Net.Dns.GetHostName
                drow2("Drive") = DriveName.Remove(1, 2)
                drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
                drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow2("GBorTBDrive") = "GB"
                drow2("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow2)

            ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow2 As DataRow = dt.NewRow

                drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow2("Server") = System.Net.Dns.GetHostName
                drow2("Drive") = DriveName.Remove(1, 2)
                drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow2("GBorTBDrive") = "TB"
                drow2("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow2)

            ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow2 As DataRow = dt.NewRow

                drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow2("Server") = System.Net.Dns.GetHostName
                drow2("Drive") = DriveName.Remove(1, 2)
                drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow2("GBorTBDrive") = "TB"
                drow2("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow2)

            ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow2 As DataRow = dt.NewRow

                drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow2("Server") = System.Net.Dns.GetHostName
                drow2("Drive") = DriveName.Remove(1, 2)
                drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow2("GBorTBDrive") = "TB"
                drow2("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow2)

            ElseIf DriveNumber = 2 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow2 As DataRow = dt.NewRow

                drow2("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow2("Server") = System.Net.Dns.GetHostName
                drow2("Drive") = DriveName.Remove(1, 2)
                drow2("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow2("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow2("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow2("GBorTBDrive") = "TB"
                drow2("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow2)

            End If


            If DriveNumber = 3 And d.TotalSize < 1099511627776 Then

                Dim drow3 As DataRow = dt.NewRow

                drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow3("Server") = System.Net.Dns.GetHostName
                drow3("Drive") = DriveName.Remove(1, 2)
                drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
                drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024,)
                drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024,)
                drow3("GBorTBDrive") = "GB"
                drow3("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow3)

            ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow3 As DataRow = dt.NewRow

                drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow3("Server") = System.Net.Dns.GetHostName
                drow3("Drive") = DriveName.Remove(1, 2)
                drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow3("GBorTBDrive") = "TB"
                drow3("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow3)

            ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow3 As DataRow = dt.NewRow

                drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow3("Server") = System.Net.Dns.GetHostName
                drow3("Drive") = DriveName.Remove(1, 2)
                drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow3("GBorTBDrive") = "TB"
                drow3("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow3)

            ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow3 As DataRow = dt.NewRow

                drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow3("Server") = System.Net.Dns.GetHostName
                drow3("Drive") = DriveName.Remove(1, 2)
                drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow3("GBorTBDrive") = "TB"
                drow3("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow3)

            ElseIf DriveNumber = 3 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow3 As DataRow = dt.NewRow

                drow3("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow3("Server") = System.Net.Dns.GetHostName
                drow3("Drive") = DriveName.Remove(1, 2)
                drow3("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow3("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow3("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow3("GBorTBDrive") = "TB"
                drow3("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow3)

            End If


            If DriveNumber = 4 And d.TotalSize < 1099511627776 Then

                Dim drow4 As DataRow = dt.NewRow

                drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow4("Server") = System.Net.Dns.GetHostName
                drow4("Drive") = DriveName.Remove(1, 2)
                drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
                drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow4("GBorTBDrive") = "GB"
                drow4("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow4)

            ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow4 As DataRow = dt.NewRow

                drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow4("Server") = System.Net.Dns.GetHostName
                drow4("Drive") = DriveName.Remove(1, 2)
                drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow4("GBorTBDrive") = "TB"
                drow4("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow4)

            ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow4 As DataRow = dt.NewRow

                drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow4("Server") = System.Net.Dns.GetHostName
                drow4("Drive") = DriveName.Remove(1, 2)
                drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow4("GBorTBDrive") = "TB"
                drow4("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow4)

            ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow4 As DataRow = dt.NewRow

                drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow4("Server") = System.Net.Dns.GetHostName
                drow4("Drive") = DriveName.Remove(1, 2)
                drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow4("GBorTBDrive") = "TB"
                drow4("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow4)

            ElseIf DriveNumber = 4 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow4 As DataRow = dt.NewRow

                drow4("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow4("Server") = System.Net.Dns.GetHostName
                drow4("Drive") = DriveName.Remove(1, 2)
                drow4("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow4("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow4("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow4("GBorTBDrive") = "TB"
                drow4("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow4)

            End If

            If DriveNumber = 5 And d.TotalSize < 1099511627776 Then

                Dim drow5 As DataRow = dt.NewRow

                drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow5("Server") = System.Net.Dns.GetHostName
                drow5("Drive") = DriveName.Remove(1, 2)
                drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024)
                drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow5("GBorTBDrive") = "GB"
                drow5("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow5)


            ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow5 As DataRow = dt.NewRow

                drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow5("Server") = System.Net.Dns.GetHostName
                drow5("Drive") = DriveName.Remove(1, 2)
                drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow5("GBorTBDrive") = "TB"
                drow5("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow5)

            ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace >= 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow5 As DataRow = dt.NewRow

                drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow5("Server") = System.Net.Dns.GetHostName
                drow5("Drive") = DriveName.Remove(1, 2)
                drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024)
                drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow5("GBorTBDrive") = "TB"
                drow5("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow5)

            ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace >= 1099511627776 Then

                Dim drow5 As DataRow = dt.NewRow

                drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow5("Server") = System.Net.Dns.GetHostName
                drow5("Drive") = DriveName.Remove(1, 2)
                drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024 / 1024)
                drow5("GBorTBDrive") = "TB"
                drow5("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow5)

            ElseIf DriveNumber = 5 And d.TotalSize >= 1099511627776 And d.TotalSize - d.TotalFreeSpace < 1099511627776 And d.TotalFreeSpace < 1099511627776 Then

                Dim drow5 As DataRow = dt.NewRow

                drow5("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
                drow5("Server") = System.Net.Dns.GetHostName
                drow5("Drive") = DriveName.Remove(1, 2)
                drow5("TotalSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 / 1024,)
                drow5("UsedSpace") = FormatNumber(d.TotalSize / 1024 / 1024 / 1024 - d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow5("RemainingSpace") = FormatNumber(d.TotalFreeSpace / 1024 / 1024 / 1024)
                drow5("GBorTBDrive") = "TB"
                drow5("DriveActiveStatus") = d.IsReady
                dt.Rows.Add(drow5)

            End If
        Next

        ' Write Information to XML file

        Dim day As String = DateTime.Now.ToString("yyyy" & Space(1) & "MM" & Space(1) & "dd" & Space(1) & "h" & Space(1) & "mm" & Space(1) & "ss" & Space(1) & "tt")

        Dim path As String = "C:\temp"
        path = IO.Path.Combine(path, "ServerStorageC7L6M72" & Space(1) & day & Space(1) & ".xml")
        dt.WriteXml(path)

    End Sub
    Public Sub UploadXML(sender As Object, e As EventArgs)

        Dim day As String = DateTime.Now.ToString("yyyy MM dd h mm ss tt")
        Dim path As String = IO.Path.Combine("C:\temp", "ServerStorageC7L6M72 " & day & " .xml")
        ' Please tell me you're not really using the sa account for this. That's REALLY BAD!
        Dim ServerCheckConnectString = "Persist Security Info=False;User ID=sa;Password=*****;Initial Catalog=ServerDriveStorageChecks;Data Source=localhost;"
        Dim SQL As String = "
INSERT INTO DriveInformation
    (Date, Server, Drive, TotalSpace, UsedSpace, RemainingSpace, GBorTBDrive, DriveActiveStatus)
SELECT 
    MY_XML.DriveInformation.query('Date').value('.', 'DATETIME'),
    MY_XML.DriveInformation.query('Server').value('.', 'VARCHAR(15)'), 
    MY_XML.DriveInformation.query('Drive').value('.', 'VARCHAR(1)'),  
    MY_XML.DriveInformation.query('TotalSpace').value('.', 'NUMERIC(10,2)'), 
    MY_XML.DriveInformation.query('UsedSpace').value('.', 'NUMERIC(10,2)'), 
    MY_XML.DriveInformation.query('RemainingSpace').value('.', 'NUMERIC(10,2)'), 
    MY_XML.DriveInformation.query('GBorTBDrive').value('.', 'VARCHAR(2)'), 
    MY_XML.DriveInformation.query('DriveActiveStatus').value('.', 'VARCHAR(6)')
FROM (
    SELECT CAST(MY_XML AS xml) 
    FROM OPENROWSET (BULK " & path & ", SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY MY_XML.nodes('DocumentElement/DriveInformation') AS MY_XML (DriveInformation);"

        Using cn As New SqlConnection(ServerCheckConnectString),
          cmd As New SqlCommand(SQL, cn)

            cn.Open()
            cmd.ExecuteNonQuery()
        End Using ' Connection is closed/disposed here, *even if an exception is thrown*
    End Sub
End Module

sql-server xml vb.net

0赞 Sean Lange 6/26/2023

清理命令文本后，您需要实际执行命令。正如发布的那样，您的代码不会执行任何操作。最有可能的是，您将希望使用 ExecuteNonQuery（）。

1赞 Charlieface 6/26/2023

将整个文件作为参数传递可能更明智。但为此，客户端需要访问它，此时您将其存储在服务器上。varbinary

答：

1赞 Joel Coehoorn 6/26/2023 #1

VB.Net SQL 字符串在第一行末尾的初始和命令之间包含一个额外的逗号，该逗号不是原始 SQL 的一部分，不应存在，从而导致 SQL Server 看到以下无效表达式：SELECTINSERT

SELECT * FROM DriveInformation, INSERT INTO ...

从该部分中删除“，”。但你似乎根本没有使用这个语句，并且可以完全删除它。如果你正在使用它，而我们只是没有看到代码的那部分，至少把它放在命令之后，这样新的行就会包含在结果中。SELECTINSERT

我们还可以进行其他改进：

Public Sub UploadXML(sender As Object, e As EventArgs)

    Dim day As String = DateTime.Now.ToString("yyyy MM dd h mm ss tt")
    Dim path As String = IO.Path.Combine("C:\temp", "ServerStorageC7L6M72 " & day & " .xml")
    ' Please tell me you're not really using the sa account for this. That's REALLY BAD!
    Dim ServerCheckConnectString As String = "Persist Security Info=False;User ID=sa;Password=********;Initial Catalog=ServerDriveStorageChecks;Data Source=localhost;"
    Dim SQL As String = "
INSERT INTO DriveInformation
    (Date, Server, Drive, TotalSpace, UsedSpace, RemainingSpace, GBorTBDrive, DriveActiveStatus)
SELECT 
    MY_XML.DriveInformation.query('Date').value('.', 'DATETIME'), 
    MY_XML.DriveInformation.query('Server').value('.', 'VARCHAR(15)'), 
    MY_XML.DriveInformation.query('Drive').value('.', 'VARCHAR(1)'),  
    MY_XML.DriveInformation.query('TotalSpace').value('.', 'NUMERIC(10,2)'), 
    MY_XML.DriveInformation.query('UsedSpace').value('.', 'NUMERIC(10,2)'), 
    MY_XML.DriveInformation.query('RemainingSpace').value('.', 'NUMERIC(10,2)'), 
    MY_XML.DriveInformation.query('GBorTBDrive').value('.', 'VARCHAR(2)'), 
    MY_XML.DriveInformation.query('DriveActiveStatus').value('.', 'VARCHAR(6)')
FROM (
    SELECT CAST(MY_XML AS xml) 
    FROM OPENROWSET (BULK " & path & ", SINGLE_BLOB) AS T(MY_XML)
) AS T(MY_XML)
CROSS APPLY MY_XML.nodes('DocumentElement/DriveInformation') AS MY_XML (DriveInformation);"

    Using cn  As New SqlConnection(ServerCheckConnectString), _
          cmd As New SqlCommand(SQL, cn)

        cn.Open()
        cmd.ExecuteNonQuery()
    End Using ' Connection is closed/disposed here, *even if an exception is thrown*
End Sub

我的一个特别讨厌的是：

Dim cn As New SqlConnection
' ...
cn = New SqlConnection(ServerCheckConnectString)

...其中，您使用声明一个变量，因此它也会分配对象，然后对该变量的第一个引用再次为其分配一个新的引用。这会浪费地从内存中分配一个额外的对象，然后立即丢弃它。虽然编译器可能会检测到这一点并优化分配，但这通常表明作者对代码正在做什么缺乏基本的了解。New

更新：

通读了代码后，我相信这个问题是创建 xml 文件和将其加载到服务器中的当前时间变化，因此文件名（精确到秒）不再存在。若要解决此问题，可以在将文件名写入磁盘时将文件名保存在内存中的某个位置，或者要求系统提供目标路径中 xml 文件的名称。

当我在那里时，我花了几分钟时间将最后一个循环中的所有 IF/Else 检查合并到一个 SINGLE SET 中，从而大大减少了代码：

Public GBTBBreak As Integer = 1099511627776 
Public GBDivisor As Double = 1024 * 1024 * 1024 * 1.0
Public TBDivisor As Double = GBDivisor * 1024

Public Function FormatForGBTB(input As Integer) As String
    If input < GBTBBreak Then
        Return String.Format("{0,15} {1}", input / GBDivisor, "GB")
    Else
        Return String.Format("{0, 15} {1}", input / TBDivisor, "TB")
    End If
End Function

Public Sub Main()

    Dim dt As New DataTable("DriveInformation")

    dt.Columns.Add(New DataColumn("Date"))
    dt.Columns.Add(New DataColumn("Server"))
    dt.Columns.Add(New DataColumn("Drive"))
    dt.Columns.Add(New DataColumn("TotalSpace"))
    dt.Columns.Add(New DataColumn("UsedSpace"))
    dt.Columns.Add(New DataColumn("RemainingSpace"))
    dt.Columns.Add(New DataColumn("GBorTBDrive"))
    dt.Columns.Add(New DataColumn("DriveActiveStatus"))

    ' Get information and Write to Console.
    Dim Host As String = System.Net.Dns.GetHostName
    Console.WriteLine("Server Name: {0}", Host)
    Console.WriteLine("Date: {0}{1}", DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt"), vbCrLf)


    Dim allDrives() As IO.DriveInfo = IO.DriveInfo.GetDrives()   

    For Each d As IO.DriveInfo In allDrives.Where(Function(dr) dr.IsReady)
        Console.WriteLine(d.Name)
        Console.WriteLine("  Drive type: {0}", d.DriveType)
        Console.WriteLine("  Volume label: {0}", d.VolumeLabel)
        Console.WriteLine("  File system: {0}", d.DriveFormat)
        Console.WriteLine("  Total size of drive:   {0}", FormatForGBTB(d.TotalSize))
        Console.WriteLine("  Total used space:      {0}", FormatForGBTB(d.TotalSize - d.TotalFreeSpace))
        Console.WriteLine("  Total available space: {0}", FormatForGBTB(d.TotalFreeSpace))
    Next

    ' Put Information into DataTable
    For Each d In allDrives
        ' The DriveNumber check was NOT NEEDED, added a bunch of extra code

        Dim divisor As Double = If(d.TotalSize < GBTBBreak, GBDivisor, TBDivisor)

        Dim row As DataRow = dt.NewRow

        row("Date") = DateTime.Now.ToString("yyyy/MM/dd h:mm:ss tt")
        row("Server") = Host
        row("Drive") = d.Name.Remove(1, 2)

        ' There was an issue with the original where the units were
        ' only preserved in the GBorTBDrive column, which was only
        ' based on the TotalSpace column, but the UsedSpace and 
        ' Remaining space columns would still scale without showing
        ' which units they used. Since we're storing the unit
        ' separately, I simplified to always scale with the 
        ' GBorTBColumn. Otherwise, information was lost about 
        ' the actual amount of used vs free space. As an alternative, 
        ' you could include the units with each column again,
        ' as was done in the previous loop.
        row("TotalSpace") = (d.TotalSize / divisor).ToString()
        row("UsedSpace") = ((d.TotalSize-d.TotalFreeSpace) / divisor).ToString()
        row("RemainingSpace") = (d.TotalFreeSpace / divisor).ToString()
        row("GBorTBDrive") = If(d.TotalSize < GBTBBreak, "GB", "TB")
        row("DriveActiveStatus") = d.IsReady
        dt.Rows.Add(row)

    Next

    ' Write Information to XML file

    Dim day As String = DateTime.Now.ToString("yyyy MM dd h mm ss tt")
    Dim path As String = IO.Path.Combine("C:\temp", "ServerStorageC7L6M72 " & day & " .xml")
    dt.WriteXml(path)

End Sub

0赞 dpresnell90 6/26/2023

感谢大家对这个问题的见解和有用的回答！@Joel Coehoorn 我确实知道 SA 帐户使用起来并不安全。我暂时只是将其用作临时测试环境。此外，我对编码仍然相当陌生，对 SQL 函数和查询甚至更新。随着我获得更多的编码经验，我正在努力更好地优化我的代码和工作流程。我将在下面的下一条评论中发布完整的代码。请记住，此代码尚未优化，并且会显得混乱，因为现在我正在测试功能。

0赞 Charlieface 6/26/2023

对不起，我的意思是，等等是更简单的代码，可能更快。如果出现注射问题，这样做也是明智的。MY_XML.DriveInformation.value('(Date/text())[1]', 'DATETIME')& path.Replace("'", "''")

0赞 Joel Coehoorn 6/26/2023

@dpresnell90不要在评论中发布代码。将其编辑到原始问题中。

0赞 dpresnell90 6/26/2023

嗨，@JoelCoehoorn。谢谢你的信息。我把完整的代码放在问题中，不得不删除以前的一些代码，因为我的代码片段太长了。我知道这段代码需要大量的优化，我计划在代码中的所有功能都正常运行后再进行优化。我知道这也有很多 If Then Else 语句。

0赞 dpresnell90 6/26/2023

@JoelCoehoorn 添加您提供的信息后，它仍然无法正常工作，因此我知道它代表我的编码错误。调试时间充裕！

上一个：Vb.Net 从 XML 中读取和提取数据

下一个：为什么SOAP请求中缺少参数？

打开连接后将XML上传到SQL Server数据库中的数据表的正确命令？

Proper command to upload XML to datatable in SQL Server database after opening connection?

评论

评论