首先创建一个数据库连接类：SQLHelper
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; namespace insetData { public class SQLHelper { public static readonly string Strconn = "Data Source=LocalHost;database=CardID;uid=sa;pwd=123"; /// <summary> /// 创建SqlConnection对象 /// </summary> /// <returns></returns> public static SqlConnection sqlConn() { return new SqlConnection(Strconn); } /// <summary> /// SQLServer批量插入数据功能 /// 这是SQLServer特有的功能，故不再上层抽象类中编写 /// </summary> /// <param name="dt">数据表</param> /// <param name="tableName">表名称</param> public static void BulkInsert(SqlConnection conn, DataTable dt, string tableName) { if (string.IsNullOrEmpty(tableName)) { throw new ArgumentNullException("请指定需要插入的数据表"); } var count = dt.Rows.Count; if (count == 0) { return; } if (conn.State == ConnectionState.Closed) { conn.Open(); } //SqlBulkCopy用其他源的数据有效批量加载 SQL Server 表 using (SqlBulkCopy copy = new SqlBulkCopy(conn)) { copy.BatchSize = dt.Rows.Count;//每一批次中的行数。在每一批次结束时，将该批次中的行发送到服务器。 copy.DestinationTableName = tableName;//指定目标表 copy.WriteToServer(dt);//将dt中的所有行复制到SqlBulkCopy对象的DestinationTableName指定的目标表中 conn.Close(); conn.Dispose(); } } /// <summary> /// 创建数据表 /// </summary> /// <param name="columns"></param> public static DataTable CreateTable(IList<string> columns) { var dt = new DataTable(); foreach (var c in columns) { dt.Columns.Add(c); } return dt; } /// <summary> /// 批量插入数据 /// </summary> /// <param name="tableName">数据表</param> /// <param name="columns">字段</param> /// <param name="dataList">数据列表</param> /// <param name="action">具体操作</param> public static void CreateInner<T>(SqlConnection conn, string tableName, IList<string> columns, IList<T> dataList, Action<DataRowWaper, T, int> action) { if (string.IsNullOrEmpty(tableName)) { throw new ArgumentNullException("需要指定操作的数据表"); } if (columns == null || columns.Count == 0) { throw new ArgumentNullException("数据表列不能为空"); } var dt = CreateTable(columns); if (action != null) { for (var i = 0; i < dataList.Count; i++) { var wapper = new DataRowWaper(dt.NewRow()); action(wapper, dataList[i], i); dt.Rows.Add(wapper.Row); } } BulkInsert(conn, dt, tableName); } } }
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace insetData
{
public class SQLHelper
{
public static readonly string Strconn = "Data Source=LocalHost;database=CardID;uid=sa;pwd=123";
/// <summary>
/// 创建SqlConnection对象
/// </summary>
/// <returns></returns>
public static SqlConnection sqlConn()
{
return new SqlConnection(Strconn);
}
/// <summary>
/// SQLServer批量插入数据功能
/// 这是SQLServer特有的功能，故不再上层抽象类中编写
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="tableName">表名称</param>
public static void BulkInsert(SqlConnection conn, DataTable dt, string tableName)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("请指定需要插入的数据表");
}
var count = dt.Rows.Count;
if (count == 0)
{
return;
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//SqlBulkCopy用其他源的数据有效批量加载 SQL Server 表
using (SqlBulkCopy copy = new SqlBulkCopy(conn))
{
copy.BatchSize = dt.Rows.Count;//每一批次中的行数。在每一批次结束时，将该批次中的行发送到服务器。
copy.DestinationTableName = tableName;//指定目标表
copy.WriteToServer(dt);//将dt中的所有行复制到SqlBulkCopy对象的DestinationTableName指定的目标表中
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 创建数据表
/// </summary>
/// <param name="columns"></param>
public static DataTable CreateTable(IList<string> columns)
{
var dt = new DataTable();
foreach (var c in columns)
{
dt.Columns.Add(c);
}
return dt;
}
/// <summary>
/// 批量插入数据
/// </summary>
/// <param name="tableName">数据表</param>
/// <param name="columns">字段</param>
/// <param name="dataList">数据列表</param>
/// <param name="action">具体操作</param>
public static void CreateInner<T>(SqlConnection conn, string tableName, IList<string> columns, IList<T> dataList, Action<DataRowWaper, T, int> action)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("需要指定操作的数据表");
}
if (columns == null || columns.Count == 0)
{
throw new ArgumentNullException("数据表列不能为空");
}
var dt = CreateTable(columns);
if (action != null)
{
for (var i = 0; i < dataList.Count; i++)
{
var wapper = new DataRowWaper(dt.NewRow());
action(wapper, dataList[i], i);
dt.Rows.Add(wapper.Row);
}
}
BulkInsert(conn, dt, tableName);
}
}
}
其次创建DataRowWaper类
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace insetData { public class DataRowWaper { private DataRow row = null; public DataRowWaper(DataRow row) { this.row = row; } public DataRow Row { get { return this.row; } } public object this[DataColumn column] { get { return this.row[column]; } set { this.row[column] = value; } } public object this[int columnIndex] { get { return this.row[columnIndex]; } set { this.row[columnIndex] = value; } } public object this[string columnName] { get { return this.row[columnName]; } set { this.row[columnName] = value; } } public void SetValue(string key, object value) { this.row[key] = value; } } }
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace insetData
{
public class DataRowWaper
{
private DataRow row = null;
public DataRowWaper(DataRow row)
{
this.row = row;
}
public DataRow Row
{
get
{
return this.row;
}
}
public object this[DataColumn column]
{
get
{
return this.row[column];
}
set
{
this.row[column] = value;
}
}
public object this[int columnIndex]
{
get
{
return this.row[columnIndex];
}
set
{
this.row[columnIndex] = value;
}
}
public object this[string columnName]
{
get
{
return this.row[columnName];
}
set
{
this.row[columnName] = value;
}
}
public void SetValue(string key, object value)
{
this.row[key] = value;
}
}
}
映射到数据库对象表信息People
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace insetData
{
public class People
{
public int id { get; set;}
public string uname { get; set; }
public string era { get; set; }
public string amount { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace insetData
{
public class People
{
public int id { get; set;}
public string uname { get; set; }
public string era { get; set; }
public string amount { get; set; }
}
}
批量操作数据
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Diagnostics; using System.Data.SqlClient; namespace insetData { class Program { static void Main(string[] args) { //构造的数据源 List<People> list = new List<People>() { new People(){ uname="张亮",era="中年",amount="100000"}, new People(){ uname="天天",era="年轻",amount="233233"}, new People(){ uname="kimi",era="年轻",amount="455631"} }; //列名称 var lists = new[] { "id", "uname", "era", "amount" }; ////构建DataTable //DataTable dt = new DataTable(); //foreach (var item in lists) //{ // dt.Columns.Add(item, item.GetType()); //} //for (int i = 0; i < list.Count; i++) //{ // DataRow newRow = dt.NewRow(); // newRow["uname"] = list[i].uname; // newRow["era"] = list[i].era; // newRow["amount"] = list[i].amount; // dt.Rows.Add(newRow); //} ////批量插入 //SQLHelper.BulkInsert(SQLHelper.sqlConn(), dt, "Tb_People"); //上面这段代码是直接构造的DataTable SQLHelper.CreateInner<People>(SQLHelper.sqlConn(), "Tb_People", lists, list, (curow, People, i) => { curow["id"] = People.id; curow["uname"] = People.uname; curow["era"] = People.era; curow["amount"] = People.amount; } ); } } }
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
namespace insetData
{
class Program
{
static void Main(string[] args)
{
//构造的数据源
List<People> list = new List<People>()
{
new People(){ uname="张亮",era="中年",amount="100000"},
new People(){ uname="天天",era="年轻",amount="233233"},
new People(){ uname="kimi",era="年轻",amount="455631"}
};
//列名称
var lists = new[] { "id", "uname", "era", "amount" };
////构建DataTable
//DataTable dt = new DataTable();
//foreach (var item in lists)
//{
// dt.Columns.Add(item, item.GetType());
//}
//for (int i = 0; i < list.Count; i++)
//{
// DataRow newRow = dt.NewRow();
// newRow["uname"] = list[i].uname;
// newRow["era"] = list[i].era;
// newRow["amount"] = list[i].amount;
// dt.Rows.Add(newRow);
//}
////批量插入
//SQLHelper.BulkInsert(SQLHelper.sqlConn(), dt, "Tb_People");
//上面这段代码是直接构造的DataTable
SQLHelper.CreateInner<People>(SQLHelper.sqlConn(), "Tb_People", lists, list,
(curow, People, i) =>
{
curow["id"] = People.id;
curow["uname"] = People.uname;
curow["era"] = People.era;
curow["amount"] = People.amount;
}
);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
namespace insetData
{
class Program
{
static void Main(string[] args)
{
//构造的数据源
List<People> list = new List<People>()
{
new People(){ uname="张亮",era="中年",amount="100000"},
new People(){ uname="天天",era="年轻",amount="233233"},
new People(){ uname="kimi",era="年轻",amount="455631"}
};
//列名称
var lists = new[] { "id", "uname", "era", "amount" };
////构建DataTable
//DataTable dt = new DataTable();
//foreach (var item in lists)
//{
// dt.Columns.Add(item, item.GetType());
//}
//for (int i = 0; i < list.Count; i++)
//{
// DataRow newRow = dt.NewRow();
// newRow["uname"] = list[i].uname;
// newRow["era"] = list[i].era;
// newRow["amount"] = list[i].amount;
// dt.Rows.Add(newRow);
//}
////批量插入
//SQLHelper.BulkInsert(SQLHelper.sqlConn(), dt, "Tb_People");
//上面这段代码是直接构造的DataTable
SQLHelper.CreateInner<People>(SQLHelper.sqlConn(), "Tb_People", lists, list,
(curow, People, i) =>
{
curow["id"] = People.id;
curow["uname"] = People.uname;
curow["era"] = People.era;
curow["amount"] = People.amount;
}
);
}
}
}