使用SqlBulkCopy导入数据，并且生成insert数据

最新推荐文章于 2018-12-19 09:15:48 发布

原创最新推荐文章于 2018-12-19 09:15:48 发布 · 816 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #string #sql server #button #properties #object

数据库技术专栏收录该内容

45 篇文章

订阅专栏

本文介绍了一种从Excel文件中读取数据并批量导入到SQL Server数据库的方法。通过使用OleDbConnection连接Excel文件，并借助SqlBulkCopy进行高效的数据迁移。此外，还展示了如何进一步将数据插入到指定的数据库表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

 protected void Button1_Click(object sender, EventArgs e)

    {

        string execelConnectionStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/Xmas Fusion - 10000 Coupons.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

        using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))

        {

            OleDbCommand cmd = new OleDbCommand("select * FROM [rental Promocodes1$]", conn);

            conn.Open();

            using (IDataReader dr = cmd.ExecuteReader())

            {

                string sqlConnectionString = @"Data Source=192.168.1.6;Initial Catalog=VP_Prod_080627;uid=VP;pwd=clochase";

                // Bulk Copy to SQL Server 

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

                {

                    bulkCopy.DestinationTableName = "TempTest";

                    bulkCopy.WriteToServer(dr);

                }

            }

        }

        Response.Write("OK");

    }

    protected void Button2_Click(object sender, EventArgs e)

    {

        string sqlConnectionString = @"Data Source=192.168.1.6;Initial Catalog=VP_Prod_080627;uid=VP;pwd=clochase";

        FileStream file = new FileStream(@"D:/Insert.txt",FileMode.Create);

        StreamWriter w = new StreamWriter(file);

        using (SqlConnection con = new SqlConnection(sqlConnectionString))

        {

            SqlCommand cmd = new SqlCommand("select * from TempTest", con);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();

            da.Fill(dt);

            long count = 170001;

            for (int i = 0; i < dt.Rows.Count; i++)

            { 

                string insertString = string.Format("insert tblPromo values({0},'{1}','{2}')",count, dt.Rows[i]["PromoCode"].ToString(),dt.Rows[i]["PromoVersion"].ToString());

                w.WriteLine(insertString);

                count++;

            }

            w.Close();

            file.Close();

        }

        Response.Write("sheng cheng ok ");





    }