将C# dataTable 做为参数传入到存储过程

2016-07-14 11:31 Evan.Pei 阅读(7087) 评论(0) 编辑收藏举报

1.list转换为DataTable(如果有需要)

public static DataTable ListToDataTable<T>(List<T> entitys)
{
    //检查实体集合不能为空
    if (entitys == null || entitys.Count < 1)
    {
        throw new Exception("需转换的集合为空");
    }
    //取出第一个实体的所有Propertie
    Type entityType = entitys[0].GetType();
    PropertyInfo[] entityProperties = entityType.GetProperties();
 
    //生成DataTable的structure
    //生产代码中，应将生成的DataTable结构Cache起来，此处略
    DataTable dt = new DataTable();
    for (int i = 0; i < entityProperties.Length; i++)
    {
        //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
        dt.Columns.Add(entityProperties[i].Name);
    }
    //将所有entity添加到DataTable中
    foreach (object entity in entitys)
    {
        //检查所有的的实体都为同一类型
        if (entity.GetType() != entityType)
        {
            throw new Exception("要转换的集合元素类型不一致");
        }
        object[] entityValues = new object[entityProperties.Length];
        for (int i = 0; i < entityProperties.Length; i++)
        {
            entityValues[i] = entityProperties[i].GetValue(entity, null);
        }
        dt.Rows.Add(entityValues);
    }
    return dt;
}

　　2.得到DATATable

1	`DataTable CutLayHdDt = ListToDataTable(CutLayHds);//CutLayHds is list`

　　3.数据库创建自定义类型

创建的自定义类型与表类型，字段需和C#datatable 的列名一致

CREATE TYPE [dbo].[CUT_LAY_HDCustomType] AS TABLE(
    [LAY_TRANS_ID] [bigint] NULL,
    [LAY_ID] [bigint] NULL,
    [SIZE_CD] [nvarchar](20) NOT NULL,
    [RATIO] [int] NOT NULL,
    [SIZE_CD2] [nvarchar](20) NULL,
    [JOB_ORDER_NO] [nvarchar](20) NULL,
    [LAY_NO] [bigint] NULL
)
GO

　　4.编写存储过程进行使用

create proc USP_CUTTING_TATABLET_PULL_FINISH2
(
 @CutlayhdList CUT_LAY_HDCustomType READONLY--定义参数，接收datatable
)  
as
    SELECT *, IDENTITY(int, 1, 1) AS ID INTO #cutlayhdtemp FROM @CutlayhdList--将参数的值插入到临时表

　　5.C#调用

SqlParameter[] parameters = new SqlParameter[1];
                 
                parameters[0] = new SqlParameter() { ParameterName = "CutlayhdList", Value = CutLayHdDt };//值为上面转换的datatableExecuteNonQuery("USP_CUTTING_TATABLET_PULL_FINISH", parameters);

　　-------------方法

public static void ExecuteNonQuery(string spName, SqlParameter[] parameterValues)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["CuttingDev"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                // Invoke RegionUpdate Procedure
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                foreach (SqlParameter p in parameterValues)
                {
                    //eck for derived output value with no value assigned
                    if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                    {
                        p.Value = DBNull.Value;
                    }
 
                    cmd.Parameters.Add(p);
                }
                cmd.ExecuteNonQuery();
            }
        }