将dataTable一次性插入数据库的几种方法(SqlBulkCopy或存储过程)

/// <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]