預存程序傳入DataTable參數

原文

http://www.189works.com/article-97513-1.html

 

資料庫表

CREATE TABLE
[dbo].[Orders]
(   
    [Orders_nbr]
INT IDENTITY(1,1)
PRIMARY KEY,
    [ItemCode]
NVARCHAR(50)
NOT NULL,
    [UM]
NVARCHAR(20)
NOT NULL,
    [Quantity]
DECIMAL(18,6)
NOT NULL,
    [UnitPrice]
DECIMAL(18,6)
NOT NULL
)

 

 

對應的表類型

CREATE TYPE [dbo].[OrdersTableType]
AS TABLE
    (
    ItemCode NVARCHAR(50)
NOT NULL,
    UM NVARCHAR(20)
NOT NULL,
    Quantity DECIMAL(18,6)
NOT NULL,
    UnitPrice DECIMAL(18,6)
NOT NULL
    )

 

預存程序寫法

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

 

 

 

添加。。。這個是自己以前的一個例子 保留下而已，上面步驟都一樣

DAL

 /// <summary>
        /// 添加
        /// </summary>
        /// <returns></returns>
        public bool AddProductReferenceSpecValue(DataTable dt,string md5)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.DistributionConString))
            {
                conn.Open();
                SqlTransaction st = conn.BeginTransaction();
                try
                {
                    string proc = "cProductReferenceSpecValue";
                    SqlCommand cmd = new SqlCommand(proc, conn);
                    cmd.Transaction = st;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@SpecValue",dt);
                    cmd.Parameters.AddWithValue("@MD5", md5);
                    cmd.ExecuteNonQuery();
                    count = 1;
                    st.Commit();
                }
                catch (Exception)
                {
                    count = 0;
                    st.Rollback();
                }
                conn.Close();
                return count == 0 ? false : true;
            }
        }

 

 

Bll

 

public bool AddProductReferenceSpecValue(List<ProductReferenceSpecValue> list, string md5)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID",typeof(string));
            dt.Columns.Add("SpecID", typeof(string));
            dt.Columns.Add("SpecValue", typeof(string));
            dt.Columns.Add("SectionID", typeof(string));
            foreach(ProductReferenceSpecValue value in list){
                DataRow dr = dt.NewRow();
                dr["ID"] = value.ID;
                dr["SpecID"] = value.SpecID;
                dr["SpecValue"] = string.IsNullOrEmpty(value.SpecValue) ? null : value.SpecValue;
                dr["SectionID"] = string.IsNullOrEmpty(value.SectionID) ? null : value.SectionID;
                dt.Rows.Add(dr);
            }
            return this.dal.AddProductReferenceSpecValue(dt, md5);
        }