导入CSV文件到数据库

闲来没事写了个将CSV文件导入数所库的方法

先写配置文件

private void WriteConfigFile(string file)
    {
        string colLine = "";
        using (StreamReader reader = new StreamReader(file, Encoding.GetEncoding("GB2312")))
        {
            colLine = reader.ReadLine();
        }
        if (colLine != null && colLine != "")
        {
            string[] cols = colLine.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
            int index = 0;
            string filename = Path.GetFileName(file);
            string dir = Path.GetDirectoryName(file);
            string configFile = string.Format("{0}\\Schema.ini", dir);
            StringBuilder configBuilder = new StringBuilder();
            configBuilder.Append("\r\n");
            configBuilder.Append("[");
            configBuilder.Append(filename);
            configBuilder.Append("]");
            configBuilder.Append("\r\n");
            configBuilder.Append("Format=Delimited(,)");
            configBuilder.Append("\r\n");
            configBuilder.Append("ColNameHeader=true");
            configBuilder.Append("\r\n");
            configBuilder.Append("CharacterSet=ANSI");
            configBuilder.Append("\r\n");
            foreach (string item in cols)
            {
                index++;
                configBuilder.Append("Col");
                configBuilder.Append(index);
                configBuilder.Append("=");
                configBuilder.Append(item);
                configBuilder.Append(" Char Width ");
                configBuilder.Append(4000);
                configBuilder.Append("\r\n");
            }
            using (StreamWriter write = new StreamWriter(configFile, true, Encoding.GetEncoding("GB2312")))
            {
                if (!File.Exists(configFile))
                {
                    File.Create(configFile);
                }
                write.WriteLine(configBuilder.ToString());
            }
        }
    }

再导入数据

private void InputCSVFile(string filename)
    {
        SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLHelper.connectionString, SqlBulkCopyOptions.UseInternalTransaction);
        bulkCopy.BulkCopyTimeout = 30 * 60;
        bulkCopy.BatchSize = 1000;
        bulkCopy.NotifyAfter = 1000;
        bulkCopy.SqlRowsCopied+=new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
        bulkCopy.DestinationTableName = "Market_Client";

        WriteConfigFile(filename);//编辑导入配置文件
        string[] cols=null;//所有源列
        using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
        {
            using (StreamReader reader = new StreamReader(fs, Encoding.Default))
            {
                cols = reader.ReadLine().Split(',');
                reader.Close();
                reader.Dispose();
            }
            fs.Close();
            fs.Dispose();
        }

        string dir = Path.GetDirectoryName(filename);
        string file=Path.GetFileName(filename);

        //构造查询语句
        StringBuilder selectBuilder = new StringBuilder();
        selectBuilder.Append("select ");
        foreach (string col in cols)
        {
            selectBuilder.Append(col);
            selectBuilder.Append(",");
        }
        if (selectBuilder.ToString().EndsWith(","))
        {
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
        }
        selectBuilder.Append(string.Format(" from {0}", file));

        string conStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\;Extended Properties=\"text;HDR=Yes;FMT=Delimited(,)\";", dir);
        using (OleDbConnection oledbCon = new OleDbConnection(conStr))
        {
            OleDbCommand oledbCom = new OleDbCommand();
            oledbCom.Connection = oledbCon;
            oledbCom.CommandText = selectBuilder.ToString();
            OleDbDataAdapter oAdapter = new OleDbDataAdapter(oledbCom);
            DataTable dt = new DataTable();
            try
            {
                oAdapter.Fill(dt);
                foreach (DataColumn col in dt.Columns)
                {
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
                }
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception e)
            {
                
            }
        }
    }

    private void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
    {
        Console.WriteLine(string.Format("已完成{0}条", e.RowsCopied));
    }