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

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 

      

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 };//值为上面转换的datatable

ExecuteNonQuery("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();
            }
        }