asp.net 如何将excel百万条数据导入到sql里面

xiaohutu626 2011-12-30 02:22:44

我刚做了一个将excel数据导入到sql里面，现在遇到了一个问题就是将1万条数据导入是可以的，如何数据量在几十万条的话就不可以了，请问大家没有没好的解决方案，利用多线程并发还是什么方法，希望大家可以给一个具体的实现步骤，谢谢！
我写的代码如下



<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>无标题页</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:GridView ID="GridView1" runat="server">

        </asp:GridView>

    <input id="flashfile" runat="server" type="file" style="width: 350px" />

        <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />

    </div>

    </form>

</body>

</html>

CS文件



        protected System.Web.UI.HtmlControls.HtmlInputFile File1;

        protected System.Web.UI.WebControls.Label Label1;

        protected System.Web.UI.WebControls.Button Button1;

        protected System.Web.UI.WebControls.DataGrid DataGrid1;

        public Random rd;

        Random ObjRandom = new Random();





        protected void Page_Load(object sender, EventArgs e)

        {



        }



        protected void Button1_Click(object sender, EventArgs e)

        {

            //获取文件名

            string flash = flashfile.PostedFile.FileName;

            //获取扩展名

            int j = flash.LastIndexOf(".");

            string flashexname = flash.Substring(j);

            //新文件名

            string newflashname = DateTime.Now.ToString("ddHHmmss") + Convert.ToString(ObjRandom.Next(9999)) + flashexname;

            //图片存储路径，网站的相对路径

            //adsinfo.DefaultPic = "/upload/flash/" + newflashname;

            string FlashUrl = "/upload/flash/" + newflashname;



            //保存图片

            flashfile.PostedFile.SaveAs(Server.MapPath(FlashUrl));



            string conn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + Server.MapPath("/upload/flash/") + newflashname + ";Extended Properties=Excel 8.0 ";

            OleDbConnection thisconnection = new OleDbConnection(conn);

            thisconnection.Open();

            string Sql = "select * from [Sheet1$] ";

            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);

            DataSet ds = new DataSet();

            mycommand.Fill(ds, "[Sheet1$] ");

            thisconnection.Close();

            GridView1.DataSource = ds;

            GridView1.DataBind();



           // string strConnString = "server=127.0.0.1;database=gbdb;uid=sa;password=123 ";



           // SqlConnection sqlCon = new SqlConnection(strConnString);   //创建SqlConnection对象 



          //  sqlCon.Open();//打开对象 





            string conn1 = "Data Source=gs;Initial Catalog=gsDB;Persist Security Info=True;User ID=sa;Password=sa";

            SqlConnection thisconnection1 = new SqlConnection(conn1);

            thisconnection1.Open();

            int count = ds.Tables["[Sheet1$] "].Rows.Count;



            for (int i = 0; i < count; i++)

            {

                string id_1, id_2;

                //id = ds.Tables["[Sheet1$] "].Rows[i]["id"].ToString();

                id_1 = ds.Tables["[Sheet1$] "].Rows[i]["NumberID"].ToString();

                id_2 = ds.Tables["[Sheet1$] "].Rows[i]["CardPaw"].ToString();

                //id_3 = ds.Tables["[Sheet1$] "].Rows[i]["id_3"].ToString();

                string excelsql = "insert into Tab_Card (NumberID,CardPaw) values ( '" + id_1 + "', '" + id_2 + "')";

                SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);

                mycommand1.ExecuteNonQuery();

            }

            Response.Write("更新成功");

            thisconnection1.Close();



        }