C#中批量导入大容量的txt文件到sql2005

来源：互联网发布：酒店有线网络怎么连接编辑：程序博客网时间：2024/05/12 21:03

运行工具 vs2008+sql2005 ，导入150万条数据第一次运行需要3-4分钟，第二次运行需要44秒哦

表结构》文件

前台————————————————————————————————————————————

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

后台————————————————————————————————————————————

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.IO;
using System.Collections;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

    //读出文本的内容
    private string TxtFileToStr()
    {
       string path = FileUpload1.PostedFile.FileName.ToString().Trim(); //得到文本路径
       string kk = Server.MapPath("2_201005141.TXT").ToString();

        FileInfo file = new FileInfo(path);
        FileStream fs = file.OpenRead();
        StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
        sr.BaseStream.Seek(0, SeekOrigin.Begin);
        string str = sr.ReadToEnd();
        fs.Close();
        sr.Close();
        return str;
    }

    //事务处理插入数据库中
    private void Exesql()
    {
        //调用方法TxtFileToStr()
        string str = TxtFileToStr();
        string[] sql = str.Split('|');   //得到string 数组
        int num = sql.Length - 1;

        /*构建DataTable 开始 */
        DataTable dt = new DataTable();
        dt.Columns.Add("name",typeof(string));
        dt.Columns.Add("num", typeof(string));
        dt.Columns.Add("telephone", typeof(string));
        dt.Columns.Add("num2", typeof(string));
        dt.Columns.Add("telephone2", typeof(string));
        DataRow dr = null;
        SqlConnection conn = null;
        int number = 0; //标识哪行有问题
        int col = 0;//标识那列有问题
        int count=num/5;
        ArrayList list = new ArrayList();
                try
                {
                    for (int i = 0; i < num; i++)
                    {

                        list.Add(sql[i].ToString().Trim());
                        if ((i + 1) % 5 == 0)// 表中有几列就除余几（本表为5列）
                        {
                            number++;
                            //实例化行
                            dr = dt.NewRow();
                            col = 1;
                            dr["name"] = list[0].ToString().Trim();
                            col = 2;
                            dr["num"] = list[1].ToString().Trim();
                            col = 3;
                            dr["telephone"] = list[2].ToString().Trim();
                            col = 4;
                            dr["num2"] = list[3].ToString().Trim();
                            col = 5;
                            dr["telephone2"] = list[4].ToString().Trim();
                            dt.Rows.Add(dr);
                            list.Clear(); //清空 ArrayList对象

                        }
                    }

                     string conString = ConfigurationManager.AppSettings["ConnectionString"].ToString();
                    //声明数据库连接
                     conn = new SqlConnection(conString);
                     conn.Open();
                     //声明SqlBulkCopy ,using释放非托管资源
                     using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
                     {
                         //一次批量的插入的数据量
                         sqlBC.BatchSize = 2000;

//超时之前操作完成所允许的秒数，如果超时则事务不会提交，数据将回滚，所有已复制的行都会从目标表中移除
sqlBC.BulkCopyTimeout = 60;

//设置要批量写入的表
sqlBC.DestinationTableName = "test";

                         //自定义的datatable和数据库的字段进行对应
                         sqlBC.ColumnMappings.Add("name", "a");
                         sqlBC.ColumnMappings.Add("num", "b");
                         sqlBC.ColumnMappings.Add("telephone", "c");
                         sqlBC.ColumnMappings.Add("num2", "d");
                         sqlBC.ColumnMappings.Add("telephone2", "e");

                         //批量写入
                         sqlBC.WriteToServer(dt);
                     }

                    ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入成功！')</script>");
                }
                catch
                {
                    string s = "第" + number + "行,第" + col + "列有问题；请检查一下txt文件！";
                    ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('" + s + "')</script>");
                }
                finally
                {
                    dt.Rows.Clear();
                    conn.Close();
                    GC.Collect();
                }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {

//获取当前时间的刻度数

TimeSpan ts1 = new TimeSpan(DateTime.Now.Ticks);

//你的代码或者其他操作

Exesql();

TimeSpan ts2 = new TimeSpan(DateTime.Now.Ticks);

TimeSpan ts = ts2.Subtract(ts1).Duration();

    //时间差
       string spanTime =ts.Hours.ToString()+"时"+ts.Minutes.ToString() + "分" + ts.Seconds.ToString() + "秒"+ts.Milliseconds+"毫秒";
       Response.Write(spanTime);

}