.net - VB 从 SQL Server 读取数据到数组，写入 .CSV
问题描述
我正在尝试将数据从 SQL Server 读取到数组中。之后，我想将每一行写入单独的 .csv 文件。
到目前为止，这是我的代码：
Imports System.Data.SqlClient
Public Class Form1
Public SQLcn As New SqlConnection
Public Function Connect() As Boolean
SQLcn = New SqlConnection("Server = Server01;Database=PROD;User ID=user; Password = 123")
Try
SQLcn.Open()
Return True
Catch ex As Exception
MessageBox.Show(ex.Message)
Return False
End Try
End Function
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim SQLQuery As String
Dim SQLcmd As SqlCommand
Dim SQLrdr As SqlDataReader
Dim NAVArray As New ArrayList()
Call Connect()
SQLQuery = "SELECT No_ FROM " & "dbo.Database" & " Where No_ LIKE '10007*'"
SQLcmd = New SqlCommand(SQLQuery, SQLcn)
SQLrdr = SQLcmd.ExecuteReader()
While SQLrdr.Read()
Dim dict As New Dictionary(Of String, Object)
For count As Integer = 0 To (SQLrdr.FieldCount - 1)
dict.Add(SQLrdr.GetName(count), SQLrdr(count))
Next
NAVArray.Add(dict)
End While
ExportCSV(NAVArray, "\\path\path\path")
SQLcn.Close()
End Sub
Function ExportCSV(ByVal Daten As ArrayList, ByVal Pfad As String) As Boolean
Dim Nummer As Integer
For Each Nummer In Daten
Dim csv As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(Pfad & Nummer, False)
csv.WriteLine("formatcount;formatname;printername;Beschreibung;")
csv.WriteLine("1;\\path\path\path\Format1.fmt;")
csv.Close()
Next
Return 0
End Function
End Class
NAVArray 甚至没有填充数据。
另外，我不知道如何将数据写入CSV.Writeline.
编辑：
新代码（到目前为止工作）如下所示：
Option Infer On
Option Strict On
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
Public SQLcn As New SqlConnection
Public Function GetData(databaseColumnNames As String()) As DataTable
Dim dt As New DataTable()
Dim csb As New SqlConnectionStringBuilder With {.DataSource = "NAVDB01",
.InitialCatalog = "NAV110_PROD",
.UserID = "paz",
.Password = "****"
}
Dim columnNames = " " & String.Join(", ", databaseColumnNames.Select(Function(c) "[" & c & "]")) & " "
Dim sql = "SELECT " & columnNames & " FROM [dbo.Part1 Part2$Item] WHERE No_ LIKE '10007%'"
Using conn = New SqlConnection(csb.ConnectionString),
cmd = New SqlCommand(sql, conn)
Dim DAdap As New SqlDataAdapter(cmd)
DAdap.Fill(dt)
End Using
Return dt
End Function
Function CsvLine(a As Object(), separator As Char) As String
Dim b = a.Select(Function(x) x.ToString()).ToArray()
For i = 0 To b.Count - 1
If b(i).IndexOfAny({separator, Chr(42), Chr(10), Chr(13)}) >= 0 Then
b(i) = b(i).Replace("""", """""")
b(i) = """" & b(i) & """"
End If
Next
Return String.Join(separator, b)
End Function
Sub WriteCsvFiles(destPath As String, headings As String(), dt As DataTable)
Dim separator As Char = ";"c
Dim header = String.Join(separator, headings)
For Each r As DataRow In dt.Rows
Dim destFile = Path.Combine(destPath, r(0).ToString().Trim() & ".csv")
Using sw As New StreamWriter(destFile)
sw.WriteLine(header)
sw.WriteLine(CsvLine(r.ItemArray, separator))
End Using
Next
End Sub
Private Sub bnDatenVerarbeiten_Click() Handles bnDatenVerarbeiten.Click
Dim destinationFolder = "\\fileserver02\Folder1"
Dim columnsToUseSQL = {"Description"}
Dim columnsToUseCSV = {"formatcount", "formatname", "printername", "Beschreibung"}
Dim daten = GetData(columnsToUseSQL)
WriteCsvFiles(destinationFolder, columnsToUseCSV, daten)
End Sub
End Class
关于附加输入。将实现 5 个字段： Formatcount As InputBox Formatname As Dropdown printername As Dropown（默认值由 Formatname 定义） itemnumber As Inputbox（用作 SQL 的过滤器） creditor As Inputbox（用于第二个 SQL 语句）
直接进入 csv 的字段。: formatcount 格式名 打印机名
结果进入 csv 的字段。: itemnumber (result eg "Description") creditor (result = "specialcode";"countrycode")
编辑2：
当前状态：
Option Infer On
Option Strict On
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
Public SQLcn As New SqlConnection
Public Function GetData(databaseColumnNames As String()) As DataTable
Dim dt As New DataTable()
Dim csb As New SqlConnectionStringBuilder With {.DataSource = "Server01",
.InitialCatalog = "NAV110_PROD",
.UserID = "paz",
.Password = "***"
}
Dim columnNames = " " & String.Join(", ", databaseColumnNames.Select(Function(c) "[" & c & "]")) & " "
Dim sql = "SELECT " & columnNames & " FROM [dbo.Part1 Part2$Item] WHERE No_ LIKE '10007%'"
Using conn = New SqlConnection(csb.ConnectionString),
cmd = New SqlCommand(sql, conn)
Dim DAdap As New SqlDataAdapter(cmd)
DAdap.Fill(dt)
End Using
Return dt
End Function
Function CsvLine(a As Object(), separator As Char) As String
Dim b = a.Select(Function(x) x.ToString()).ToArray()
For i = 0 To b.Count - 1
If b(i).IndexOfAny({separator, Chr(42), Chr(10), Chr(13)}) >= 0 Then
b(i) = b(i).Replace("""", """""")
b(i) = """" & b(i) & """"
End If
Next
Return String.Join(separator, b)
End Function
Sub WriteCsvFiles(destPath As String, headings As String(), dt As DataTable)
Dim separator As Char = ";"c
Dim header = String.Join(separator, headings)
For Each r As DataRow In dt.Rows
Dim destFile = Path.Combine(destPath, r(0).ToString().Trim() & ".csv")
Using sw As New StreamWriter(destFile)
sw.WriteLine(header)
sw.WriteLine(CsvLine(r.ItemArray, separator))
End Using
Next
End Sub
Private Sub bnDatenVerarbeiten_Click() Handles bnDatenVerarbeiten.Click
Dim destinationFolder = "\\fileserver02\folder"
Dim Anzahl = 1
Dim Format = "\\fileserver02\folder2"
Dim Drucker = "\\PRNSRV\Druckdruck"
Dim columnsToUseSQL = {"Description", "Description 2"}
Dim columnsToUseCSV = {"Beschreibung", "Beschreibung 2", "formatcount", "formatname", "printername"}
Dim daten = GetData(columnsToUseSQL)
daten.Columns.Add("formatcount", GetType(Integer))
daten.Columns.Add("formatname", GetType(String))
daten.Columns.Add("printername", GetType(String))
daten.Rows.Add(daten.Rows(0).Item("Description"), daten.Rows(0).Item("Description 2"), Anzahl, Format, Drucker)
WriteCsvFiles(destinationFolder, columnsToUseCSV, daten)
End Sub
End Class
解决方案
看起来您想从数据库中选择 4 列，所以为了回答这个问题，我用这些数据制作了一个名为“Huber”的表：
No_ formatcount formatname printername Beschreibung
10007 1 Hello World Starting "entry".
100071 2 Yellow Flower NULL
100072 3 Grey Rock Let's have a ; here.
您可能需要灵活选择哪些列，因此我将列名设为数组。
CSV 文件有一个标准：RFC 4180，所以我们不妨尝试遵循它。
在我看来，在这种情况下，将数据检索到 DataTable 中是最简单的。由于每个数据都将转换为字符串以写入 CSV 文件，因此这种方式从数据库中获取 NULL 数据不会有问题。
使用数据库时，最好打开连接，做操作，处理掉连接。VB.NETUsing语句会自动处理 dispose，即使出现问题。DataAdapter.Fill 方法为您打开和关闭（实际上它使连接保持与调用之前相同的状态）。不要在问题中使用“Function Connect()”之类的东西：它会导致问题。
鉴于这一切，我使用了这段代码：
Option Infer On
Option Strict On
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
Function GetData(databaseColumnNames As String()) As DataTable
Dim dt As New DataTable()
Dim csb As New SqlConnectionStringBuilder With {.DataSource = "Server01",
.InitialCatalog = "PROD",
.UserID = "user",
.Password = "123"}
' Put the column names in square brackets in case a reserved word is used as a column name.
' Does not take into account using square brackets in a column name (don't do that).
Dim columnNames = " " & String.Join(", ", databaseColumnNames.Select(Function(c) "[" & c & "]")) & " "
Dim sql = "SELECT " & columnNames & " FROM dbo.[Huber] WHERE [No_] LIKE '10007%'"
Using conn = New SqlConnection(csb.ConnectionString),
cmd = New SqlCommand(sql, conn)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
End Using
Return dt
End Function
Function CsvLine(a As Object(), separator As Char) As String
' Ref: RFC 4180 "Common Format and MIME Type for Comma-Separated Values (CSV) Files"
' https://www.rfc-editor.org/rfc/rfc4180
Dim b = a.Select(Function(x) x.ToString()).ToArray()
For i = 0 To b.Count - 1
' If the field contains the separator, a double-quote, LF, or CR, then make adjustments:
If b(i).IndexOfAny({separator, Chr(42), Chr(10), Chr(13)}) >= 0 Then
b(i) = b(i).Replace("""", """""")
b(i) = """" & b(i) & """"
End If
Next
Return String.Join(separator, b)
End Function
Sub WriteCsvFiles(destPath As String, headings As String(), dt As DataTable)
Dim separator As Char = ";"c
Dim header = String.Join(separator, headings)
For Each r As DataRow In dt.Rows
' Use the first column for the filename:
Dim destFile = Path.Combine(destPath, r(0).ToString().Trim() & ".csv")
Using sw As New StreamWriter(destFile)
sw.WriteLine(header)
sw.WriteLine(CsvLine(r.ItemArray, separator))
End Using
Next
End Sub
Private Sub bnDatenVerarbeiten_Click(sender As Object, e As EventArgs) Handles bnDatenVerarbeiten.Click
Dim destinationFolder = "C:\Temp\Huber"
Directory.CreateDirectory(destinationFolder)
' The names of the columns in the database...
Dim columnsToUse = {"formatcount", "formatname", "printername", "Beschreibung"}
Dim daten = GetData(columnsToUse)
' You could use different text for the headers if required.
WriteCsvFiles(destinationFolder, columnsToUse, daten)
End Sub
End Class
获取 3 个文件：
1.csv
formatcount;formatname;printername;Beschreibung
1 ;Hello;World;"Starting ""entry""."
2.csv
formatcount;formatname;printername;Beschreibung
2 ;Yellow;Flower;
3.csv
formatcount;formatname;printername;Beschreibung
3 ;Grey;Rock;"Let's have a ; here."
希望我留下了足够的可调节部件，供您根据需要进行更改。
注意：如果查询被修改为使用参数，例如您可能希望将其10007%变成一个变量，那么您必须使用 SQL 参数来保护它免受 SQL 注入的影响。
关于向数据表添加列。
添加列是个好主意，但不要像这样添加带有额外数据的行：
daten.Rows.Add(daten.Rows(0).Item("Description"), daten.Rows(0).Item("Description 2"), Anzahl, Format, Drucker)
您需要将额外的数据添加到每一行，如下所示：
For i = 0 To daten.Rows.Count - 1
daten.Rows(i)("formatcount") = Anzahl
daten.Rows(i)("formatname") = Format
daten.Rows(i)("printername") = Drucker
Next
推荐阅读
- python - 使用 PySerial 与 Arduino 通信，如何告诉 Python 在接收到某些输入后只执行一次函数？
- c++ - 循环未正确处理“\n”比较
- javascript - 无法在 React Web App 中导入 css 文件
- kubernetes - 如何在 Kubernetes 清单中进行变量替换？
- javascript - 如何使用 CSS（少）
- python-3.x - OrderedDict 的抽象子类？蟒蛇3
- azure - 如何从 Azure DevOps 管道向 azure key Vault 写入机密？
- r - R中createDataPartition的y值
- javascript - 如何在js中修剪html内容？
- r - 从列中提取数据以在 R 中创建另一列