/// <summary> /// 一次性把DataTable中的数据插入数据库 /// <para/>Author : talhon /// <para/>Date : 2015-9-16 /// </summary> /// <param name="source">DataTable数据源</param> /// <returns>true - 成功,false - 失败</returns> public bool AddDataTableToDB(DataTable source,string tableName) { SqlTransaction tran = null;//声明一个事务对象 try { using (SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=sa;database=Test;")) { conn.Open();//打开链接 using (tran = conn.BeginTransaction()) { using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)) { copy.DestinationTableName = tableName; //指定服务器上目标表的名称 copy.WriteToServer(source); //执行把DataTable中的数据写入DB tran.Commit(); //提交事务 return true; //返回True 执行成功! } } } } catch (Exception ex) { if (null != tran) tran.Rollback(); //LogHelper.Add(ex); return false;//返回False 执行失败! } }
创建存储过程添加dtatTable数据
CREATE PROCEDURE [dbo].[usp_Orders_Insert]
(
@OrdersCollection [OrdersTableType] READONLY
)
AS
INSERT INTO [dbo].[Orders] ([ItemCode],[UM],[Quantity],[UnitPrice])
SELECT oc.[ItemCode],oc.[UM],[Quantity],oc.[UnitPrice] FROM @OrdersCollection AS oc;
GO
//这里只是一个具体数据实例,当前为Orders表
Orders表有属性[ItemCode],[UM],[Quantity],[UnitPrice]