C#使用表类型插入数据

1.创建表

CREATE TABLE [dbo].[T2](
    [id] [varchar](50) NULL,
    [name] [varchar](50) NULL
)

2.创建表类型

create type MyTableType as table(id varchar(50),name varchar(50))

3.创建C#使用表类型的帮助类

public class SqlHelper
    {
        public static int ExeSqlByTableType(string sql,SqlParameter sqlParameter)
        {
            using (SqlConnection con=new SqlConnection(@"Data Source=.;Initial Catalog=test;User ID=sa;Password=123456"))
            {
                con.Open();
                using (SqlCommand cmd=new SqlCommand(sql,con))
                {
                    cmd.Parameters.Add(sqlParameter);
                    return cmd.ExecuteNonQuery();
                }
            }

        } 
    }

4.调用帮助类

 DataTable table = new DataTable();
            table.Columns.Add("id", typeof(int));
            table.Columns.Add("name", typeof(string));
            table.Rows.Add(1, "John");
            table.Rows.Add(2, "Mary");
            SqlParameter parameter = new SqlParameter("@MyTable", SqlDbType.Structured);
            parameter.TypeName = "MyTableType";
            parameter.Value = table;
            int AddSum= SqlHelper.ExeSqlByTableType("INSERT INTO T2 (id, name) SELECT ID, Name FROM @MyTable", parameter);