c#批量插入数据库Demo
时间：2021-07-01 10:21:17
帮助过：2人阅读
System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
namespace SqlServerBulkInsert
{
class Program
{
public static void BulkToDB(DataTable dt)
{
SqlConnection sqlConn =
new SqlConnection(
ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConn);
bulkCopy.DestinationTableName =
"BulkTestTable";
bulkCopy.BatchSize =
dt.Rows.Count;
try
{
sqlConn.Open();
if (dt !=
null && dt.Rows.Count !=
0)
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
if (bulkCopy !=
null)
bulkCopy.Close();
}
}
public static DataTable GetTableSchema()
{
DataTable dt =
new DataTable();
dt.Columns.AddRange(new DataColumn[]{
new DataColumn(
"Id",
typeof(
int)),
new DataColumn(
"UserName",
typeof(
string)),
new DataColumn(
"Pwd",
typeof(
string))});
return dt;
}
static void Main(
string[] args)
{
Stopwatch sw =
new Stopwatch();
for (
int multiply =
0; multiply <
10; multiply++
)
{
DataTable dt =
GetTableSchema();
for (
int count = multiply *
100000; count < (multiply +
1) *
100000; count++
)
{
DataRow r =
dt.NewRow();
r[0] =
count;
r[1] =
string.Format(
"User-{0}", count *
multiply);
r[2] =
string.Format(
"Pwd-{0}", count *
multiply);
dt.Rows.Add(r);
}
sw.Start();
BulkToDB(dt);
sw.Stop();
Console.WriteLine(string.Format(
"Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
}
Console.ReadLine();
}
}
}
//------------------以下是测试数据库及表的建立sql语句————————————————————————————
USE [BulkTestDB]
GO
/****** Object: Table [dbo].[BulkTestTable] Script Date: 09/22/2016 09:38:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BulkTestTable](
[Id] [int] NOT NULL,
[UserName] [nvarchar](32) NULL,
[Pwd] [varchar](16) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
FROM:http://www.cnblogs.com/rengke2002/p/5895004.html
c#批量插入数据库Demo
标签：
