c# - 将许多文本文件读入 SQL Server Express

Question

我对 C# 中的 SQL Server Express 还很陌生。

我有一个包含 2000 个文本文件的目录，我想将它逐行读入数据库并保存文件路径、行号和该行的文本。 Linq-to-SQL 或连接字符串的最佳方式是什么？

我在下面使用了这个，只是一直循环，我想这可能是因为读取数据库需要很长时间

private void button1_Click(object sender, EventArgs e)
{
        string[] files = Directory.GetFiles(@"C:\Users\mchotu\Desktop\Sample data", "*.txt", SearchOption.AllDirectories);

        foreach(string file in files)
        {
            using (StreamReader sr = new StreamReader(file))
            {
                using (var db = new DataClasses1DataContext())
                {
                    string s;
                    int line = 0;

                    while((s=sr.ReadLine()) !=null)
                    {
                        db.textFilesCompletes.InsertOnSubmit(new textFilesComplete() { fileNameone = file });
                        db.textFilesCompletes.InsertOnSubmit(new textFilesComplete() { text = s });
                        db.textFilesCompletes.InsertOnSubmit(new textFilesComplete() { Linenumber = line });
                    }

                    db.SubmitChanges();
                }
            }                
        }
        this.textFilesCompleteTableAdapter.Fill(this.database1DataSet.textFilesComplete);// this displays the updated table
}   //ends function

这是可以快速阅读的东西吗？

Answer 1

您没有创建 textFilesComplete正确。你的正确解决方案应该是这样的

private void button1_Click(object sender, EventArgs e)
{
        string[] ls = Directory.GetFiles(@"C:\Users\mchotu\Desktop\Sample data", "*.txt", SearchOption.AllDirectories);

        foreach(string file in ls)
        {
            using (StreamReader sr = new StreamReader(file))
            {
                using (var db = new DataClasses1DataContext())
                {
                    string s;
                    int line = 0;

                    while((s=sr.ReadLine()) !=null)
                    {
                        db.textFilesCompletes.InsertOnSubmit(new textFilesComplete() { fileNameone = file ,text = s ,Linenumber = line });

                    }

                    db.SubmitChanges();
                }
            }                
        }
        // dont know what your doing here?
        this.textFilesCompleteTableAdapter.Fill(this.database1DataSet.textFilesComplete);
} 

顺便说一句，加载文本文件的更好解决方案是使用 SqlBulkCopy MSDN Documentation并且可以通过使用类似 ObjectDataReader 的东西来简化它的使用Documentation & download

这暴露了 IEnumerable<T>作为IDataReader

这是一个工作示例。

 string[] files = Directory.GetFiles(@"C:\data\", "*.txt", SearchOption.AllDirectories);

    using(SqlBulkCopy sbc = new SqlBulkCopy(@"Server=.\SQLExpress; Database= AAA; Integrated Security=SSPI;"))
    {
        sbc.DestinationTableName = "dbo.textFilesCompletes";
        sbc.EnableStreaming = true;
        foreach(string file in files)
        {
            int lineNumber = 1;
            var lines = from a in File.ReadLines(file,Encoding.Default)
                        select new {FileName = file,Text = a,LineNumber = lineNumber++};
            sbc.WriteToServer(lines.AsDataReader() );               

        }
    }