数据库百万条添加的方法

最新推荐文章于 2022-11-05 16:59:04 发布

weixin_33682790

最新推荐文章于 2022-11-05 16:59:04 发布

阅读量99

点赞数

文章标签：数据库

思路：

.net 创建临时表，然后用SqlBulkCopy把临时表一次性复制的真正的数据表中；

代码如下：

protected void Button1_Click(object sender, EventArgs e)
{

    using (SqlConnection connection = new SqlConnection("Data Source=(local);Integrated Security=true;Initial Catalog=FIO;"))
    {
        connection.Open();               
        DataTable newProducts = MakeTable();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "dbo.test";
            try
            {
                bulkCopy.WriteToServer(newProducts);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

private static DataTable MakeTable()
{
    // 创建临时表
    DataTable newProducts = new DataTable("newtest");
    // 给表创建列
    DataColumn productID = new DataColumn();
    productID.DataType = System.Type.GetType("System.Int32");
    productID.ColumnName = "id";
    productID.AutoIncrement = true;
    newProducts.Columns.Add(productID);

    DataColumn productName = new DataColumn();
    productName.DataType = System.Type.GetType("System.String");
    productName.ColumnName = "name";
    newProducts.Columns.Add(productName);

    // 给列添加行
    for (int i = 0; i < 100000; i++)
    {
        DataRow dataRow = newProducts.NewRow();
        dataRow["name"] =  Guid.NewGuid().ToString();
        newProducts.Rows.Add(dataRow);
    }
    return newProducts;
}