学步园

原文

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
    )

存储过程写法

添加。。。这个是自己以前的一个例子 保留下而已，上面步骤都一样

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);
        }