VB 从 SQL Server 读取数据到数组，写入 .CSV [英] VB Reading data from SQL Server to Array, writing into .CSV
问题描述
我正在尝试将数据从 SQL Server 读出到数组中.之后，我想将每一行写入单独的 .csv 文件.
I'm trying to read out data from a SQL Server into an array. After that, I'd like to write each line into separate .csv files.
这是我目前的代码:
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 甚至没有填充数据.
The NAVArray does not even get filled with the data.
另外，我不知道如何将数据写入CSV.Writeline.
Additionally, I don't know how to write the data to the CSV.Writeline.
新代码(目前有效)如下所示:
The New Code (working so far) looks like this:
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个领域:格式计数作为输入框格式名称作为下拉列表printername As Dropown(由 Formatname 定义的默认值)itemnumber 作为输入框(用作 SQL 的过滤器)债权人作为输入框(用于第二个 SQL 语句)
About the additional Input. 5 fields will be implemented: Formatcount As InputBox Formatname As Dropdown printername As Dropown (default value defined by Formatname) itemnumber As Inputbox (Used as Filter for SQL) creditor As Inputbox (Used for a second SQL-Statement)
直接进入 csv 的字段.:格式计数格式名打印机名称
Fields going directly to the csv. : formatcount formatname printername
结果进入 csv 的字段.:项目编号(结果，例如描述")债权人 (result = "specialcode";"countrycode")
Fields with the result going to the csv. : itemnumber (result e.g. "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"的表:
It looks like you want to select 4 columns from the database, so for the purpose of answering the question I made a table named "Huber" with this data:
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.
您可能需要灵活选择哪些列，因此我将列名设为数组.
You may need some flexibility in which columns to select, so I made the column names an array.
CSV 文件有一个标准:RFC 4180，所以我们不妨试试遵循那个.
There is a standard for CSV files: RFC 4180, so we might as well try to follow that.
在我看来，在这种情况下，将数据检索到 DataTable 中是最简单的.由于每个数据都会被转换成字符串写入CSV文件，这样数据库中的NULL数据不会有问题.
It seems to me that in this case it would be simplest to retrieve the data into a DataTable. As each datum will be converted to a String to write to the CSV file, there will be no problem with NULL data from the database this way.
使用数据库时，最好是打开连接，做操作，处理连接.VB.NET 使用 语句会自动处理处置，即使出现问题.DataAdapter.Fill 方法为您执行打开和关闭操作(实际上它使连接保持与调用之前相同的状态).不要使用问题中的Function Connect()"之类的东西:它会导致问题.
When using a database, it is best to open the connection, do the operation, dispose of the connection. The VB.NET Using statement takes care of the dispose automatically, even if something goes wrong. The DataAdapter.Fill method does the open and close for you (actually it leaves the connection in the same state as before it was called). Do not use anything like the "Function Connect()" in the question: it will lead to problems.
鉴于所有这些，我使用了以下代码:
Given all that, I used this code:
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://tools.ietf.org/html/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."
希望我留下了足够多的可调节部件供您根据需要进行更改.
Hopefully I left in enough adjustable parts for you to change it as needed.
注意如果查询被修改为使用参数，例如您可能希望将 10007% 变成一个变量，那么您必须使用 SQL 参数来防止 SQL 注入.
N.B. If the query is modified to use parameters, e.g. you might want to make the 10007% into a variable, then you must use SQL parameters to keep it safe from SQL injection.
关于向数据表添加列.
添加列是个好主意，但不要像这样添加带有额外数据的行:
Adding the columns is a good idea, but instead of adding a row with the extra data like this:
daten.Rows.Add(daten.Rows(0).Item("Description"), daten.Rows(0).Item("Description 2"), Anzahl, Format, Drucker)
您需要向每一行添加额外的数据，如下所示:
You need to add the extra data to each row, like this:
For i = 0 To daten.Rows.Count - 1
daten.Rows(i)("formatcount") = Anzahl
daten.Rows(i)("formatname") = Format
daten.Rows(i)("printername") = Drucker
Next
这篇关于VB 从 SQL Server 读取数据到数组，写入 .CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
