C# 调用存储过程传入表变量作为参数

首先在SQLServer定义一个自定义表类型:

USE [ABC]
GO

CREATE TYPE [ABC].[MyCustomType] AS TABLE(
    [EmployeeId] [char](6) NOT NULL,
    [LastName] [varchar](30) NULL,
    [FirstName] [varchar](30) NULL
--省略很多列
)
GO

程序部分:

static void Test()
        {
            //定义与表类型结构相同的DataTable
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add(new DataColumn() { ColumnName = "EmployeeId", DataType = System.Type.GetType("System.String") });
            dataTable.Columns.Add(new DataColumn() { ColumnName = "LastName", DataType = System.Type.GetType("System.String") });
            dataTable.Columns.Add(new DataColumn() { ColumnName = "FirstName", DataType = System.Type.GetType("System.String") });
            //...此处省略很多行

            //插入数据行, 请注意不同类型的赋值, 这里应该根据实际情况添加很多行
            DataRow dataRow = dataTable.NewRow();
            dataRow["EmployeeId"] = "000001";
            dataRow["LastName"] = "Nick";
            dataRow["FirstName"] = "Yang";
            //此处省略插入很多行
            dataTable.Rows.Add(dataRow);

            string connStr = "Data Source=.;Database=ABC;user id=sa;password=***;Persist Security Info=True";
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            try
            {
                using (SqlCommand cmd = new SqlCommand(@"MCU.存储过程名", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    //关键是类型
                    SqlParameter parameter = new SqlParameter("@import", SqlDbType.Structured);
                    //必须指定表类型名
                    parameter.TypeName = "ABC.MyCustomType";
                    //赋值
                    parameter.Value = dataTable;
                    cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw new SystemException("Failed to insert result route.", ex);
            }
            finally
            {
                conn.Close();
            }
        }