存储过程传入DataTable参数

最新推荐文章于 2023-07-01 10:18:54 发布

Teln_小凯

最新推荐文章于 2023-07-01 10:18:54 发布

阅读量1k

点赞数

本文介绍了一种使用SQL存储过程批量插入数据的方法，并通过具体示例展示了如何创建表、定义表类型及存储过程，最后提供了数据访问层和业务逻辑层的实现代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原文

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