SQL Server数据库 857万条数据导入用时4分钟。【数据库访问类】
using System;
using System.Data;
using System.Data.SqlClient;
public class SqlDbHelper
{
/// <summary>
/// 连接字符串
/// </summary>
public static readonly string connectionString = "data source=。;initial catalog=FFFFe;user id=LLDB_Pre;password=XXX;";
#region ExecuteNonQuery命令
/// <summary>
/// 对数据库执行增、删、改命令
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <returns>受影响的记录数</returns>
public static int ExecuteNonQuery(string safeSql)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
Connection.Open();
SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.Transaction = trans;
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
int result = cmd.ExecuteNonQuery();
trans.Commit();
return result;
}
catch
{
trans.Rollback();
return 0;
}
}
}
/// <summary>
/// 对数据库执行增、删、改命令
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <returns>受影响的记录数</returns>
public static int ExecuteNonQuery(string sql, SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
Connection.Open();
SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Transaction = trans;
cmd.Parameters.AddRange(values);
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
int result = cmd.ExecuteNonQuery();
trans.Commit();
return result;
}
catch (Exception ex)
{
trans.Rollback();
return 0;
}
}
}
#endregion
#region ExecuteScalar命令
/// <summary>
/// 查询结果集中第一行第一列的值
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <returns>第一行第一列的值</returns>
public static int ExecuteScalar(string safeSql)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
/// <summary>
/// 查询结果集中第一行第一列的值
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <returns>第一行第一列的值</returns>
public static int ExecuteScalar(string sql, SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
#endregion
#region ExecuteReader命令
/// <summary>
/// 创建数据读取器
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <param name="Connection">数据库连接</param>
/// <returns>数据读取器对象</returns>
public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection)
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
/// <summary>
/// 创建数据读取器
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <param name="Connection">数据库连接</param>
/// <returns>数据读取器</returns>
public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection)
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
#endregion
#region ExecuteDataTable命令
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
/// </summary>
/// <param name="type">命令类型(T-Sql语句或者存储过程)</param>
/// <param name="safeSql">T-Sql语句或者存储过程的名称</param>
/// <param name="values">参数数组</param>
/// <returns>结果集DataTable</returns>
public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = type;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <returns>结果集DataTable</returns>
public static DataTable ExecuteDataTable(string safeSql)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(ds);
}
catch (Exception ex)
{
}
return ds.Tables[0];
}
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <returns>结果集DataTable</returns>
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandTimeout = 0;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
#endregion
#region GetDataSet命令
/// <summary>
/// 取出数据
/// </summary>
/// <param name="safeSql">sql语句</param>
/// <param name="tabName">DataTable别名</param>
/// <param name="values"></param>
/// <returns></returns>
public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
if (values != null)
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(ds, tabName);
}
catch (Exception ex)
{
}
return ds;
}
}
#endregion
#region ExecureData 命令
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="ds">修改过的DataSet</param>
/// <param name="strTblName">表名</param>
/// <returns></returns>
public static int ExecureData(DataSet ds, string strTblName)
{
try
{
//创建一个数据库连接
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
//创建一个用于填充DataSet的对象
SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection);
SqlDataAdapter myAdapter = new SqlDataAdapter();
//获取SQL语句，用于在数据库中选择记录
myAdapter.SelectCommand = myCommand;
//自动生成单表命令，用于将对DataSet所做的更改与数据库更改相对应
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
return myAdapter.Update(ds, strTblName); //更新ds数据
}
}
catch (Exception err)
{
throw err;
}
}
#endregion
public static void TableValuedToDB(DataTable dt)
{
SqlConnection sqlConn = new SqlConnection(connectionString);
const string TSqlStatement =
"insert into [BarCodeRecordes9] (is_add,qr_code,integral,created_time,id,terminal_code)" +
" SELECT is_add,qr_code,integral,created_time,id,terminal_code" +
" FROM @NewBulkTestTvp AS nc";
SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);
catParam.SqlDbType = SqlDbType.Structured;
//表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。
catParam.TypeName = "dbo.BulkUdt";
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
}
使用
TableValuedToDB方法导入 每次导入一百万条，依次分页导入了9次。
大数据（857万条数据）导入使用时间为： 362708 毫秒， 大概4分钟多一点。
TableValuedToDB(DataTable dt) 方法中 dt注意一定要和 数据库 类型 BulkUdt 中定义的列保持一致。
保证你的数据库存在该类型。自定义表类型。
CREATE TYPE dbo.BulkUdt AS TABLE
(
[is_add] [bit] NULL,
[qr_code] [nvarchar](255) NULL,
[integral] [decimal](18, 2) NULL,
[created_time] [datetime] NULL,
[id] [bigint] NOT NULL PRIMARY KEY ,
[terminal_code] [nvarchar](255) NULL
)
