vb.net 从文本文件读取数据并插入到数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20418492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read Data From Text File and Insert To databse
提问by MohammadMMohammad
I want to read data from a text file line by line and insert the data in each line in database.
我想逐行从文本文件中读取数据并将数据插入到数据库的每一行中。
What I'm thinking of now read line by line and at each line insert to database. What I'm asking here is there a better idea to do so? like what can we do to insert data at once instead of looping line by line and insert the record? I have around 500+ lines in the text file that I need to store each day, so performance is my issue here.
我现在想的是逐行读取并在每一行插入到数据库中。我在这里问的是有没有更好的主意这样做？就像我们可以做些什么来一次插入数据而不是逐行循环并插入记录？我每天需要存储的文本文件中有大约 500 多行，因此性能是我的问题。
Note that I Need to insert each line as a row in the DB. The data are delimiter by comma so I need to split them and insert them on specific columns.
请注意，我需要将每一行作为一行插入到数据库中。数据以逗号分隔，因此我需要拆分它们并将它们插入到特定列中。
Any recommendation?
有什么推荐吗？
回答by Jadeja RJ
Imports System
Imports System.IO
Imports System.Collections
Module Module1
Sub Main()
Dim objReader As New StreamReader("c:\test.txt")
Dim sLine As String = ""
Dim arrText As New ArrayList()
Do
sLine = objReader.ReadLine()
If Not sLine Is Nothing Then
arrText.Add(sLine)
End If
Loop Until sLine Is Nothing
objReader.Close()
Using command As New SqlCeCommand("INSERT INTO table(col1) VALUES(@data1)", Con)
command.Parameters.AddWithValue("@data1", "")
For Each sLine In arrText
command.Parameters("@data1").Value = sLine
command.ExecuteNonQuery()
Next
End Using
End Sub
End Module
回答by user3024816
You can bring the text file into a datatable first (bringing into datatable will not be any performance issue as it is memory based and there is no roundrobin trip to the server) and than insert it into database using Bulkcopy feature. I presume that data is to be inserted into SQL Server database. You can use SQLBulkCopy for this here is sample code:
您可以先将文本文件放入数据表中（放入数据表不会有任何性能问题，因为它是基于内存的，并且没有到服务器的循环访问），然后使用 Bulkcopy 功能将其插入到数据库中。我假设数据将被插入到 SQL Server 数据库中。您可以使用 SQLBulkCopy 在这里是示例代码：
private void BulkInsert()
{
SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=ServerName;Database=test;Trusted_Connection=True;",
SqlBulkCopyOptions.TableLock);
bulkCopy.DestinationTableName = "target_table";
bulkCopy.WriteToServer(Text2Table());
}
private DataTable Text2Table()
{
DataTable dt = new DataTable();
StreamReader sr = new StreamReader(@"c:\test\test.txt");
string input;
while ((inrecord = sr.ReadLine()) != null)
{
string[] values = inrecord.Split(new char[] { '|' });
dr = dt.NewRow();
dr["column1"] = values[0];
dr["column2"] = values[1];
dr["column3"] = values[2];
dr["column4"] = values[3];
dt.Rows.Add(dr);
}
sr.Close();
return dt;
}
回答by Ibo
I think it is not difficult it is easy.
我觉得不难，很简单。
import java.io.File;
import java.io.FileNotFoundException;
导入 java.io.File;
导入 java.io.FileNotFoundException;
import java.util.Scanner;
导入 java.util.Scanner;
public class ScannerReadFile {
public static void main(String[] args) {
FileInputStream fstream = new FileInputStream("textfile.txt");
BufferedReader br = new BufferedReader(new InputStreamReader(fstream));
String strLine;
int row =0;
int col;
while ((strLine = br.readLine()) != null) {
Scanner scanner = new Scanner(strLine);
String token="";
while(scanner.hasNext()){
token = scanner.next();
for(int i = 0; i<token.length();i++){
if(token.charAt(i)!=','){
record += token.charAt(i);
}
else{
insert into table values ( record )
record = "";
}
}
row++;
} }
回答by Rex
The performance problem is due to the insertion to DB normally - not because the reading of the file.
性能问题是由于正常插入到数据库 - 不是因为读取文件。
In order to workaround this, i suggest you use strongtyped datatable - As you are doing insertion only, just adding new rows to this .Net datatable, and at the end commit it to DB in one go (use SqlDataAdapter)
为了解决这个问题，我建议您使用强类型数据表 - 因为您只是在执行插入操作，只需向此 .Net 数据表添加新行，最后一次性将其提交到数据库（使用 SqlDataAdapter）
About reading the file, i suggest you use existing vb.net library: Microsoft.VisualBasic.FileIo.TextFiledParser (ref: http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser(v=vs.110).aspx).
关于阅读文件，我建议您使用现有的 vb.net 库：Microsoft.VisualBasic.FileIo.TextFiledParser（参考：http: //msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser( v =vs.110).aspx）。
Of course, you have another option, instead of using DataTable, generate plain text SQL insertion queries would perform a lot better, the output query would be like:
当然，你还有一个选择，不使用DataTable，生成纯文本SQL插入查询会性能好很多，输出查询会是这样的：
INSERT INTO tblTarget(Id, Col1, Col2)
Values (1, 'Row1.Val1', 'Row1.Val2'),
(2, 'Row2.Val1', 'Row2.Val2'),
(3, 'Row3.Val1', 'Row3.Val2'),
(4, 'Row4.Val1', 'Row4.Val2'),
...
Hope it helps...
希望能帮助到你...
