.net给有自增列的表值参数传值

可能有时候我们要给存储过程传递一堆的参数，有时候多达几十个参数，或者是N组数据。一个个写参数，麻烦费时还容易出错。如果能把这些参数封装成一个数据集或者列表来传递，那效率肯定是提高不少。

一、自定义数据库表类型

create type Tab_Type as table
(
	CustId int identity(1,1) not null,
	UserName nvarchar(50)
)

创建成功后，可以在可编程性->类型->用户定义表类型里看到

二、创建一个表值参数的存储过程

/*
因为Id是自增列，不能插入值，以下执行会报错
消息 8101，级别 16，状态 1，第 8 行
仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'@tab'中的标识列指定显式值。
*/
declare @tab Tab_Type
insert into @tab
select 1,'刘备' 

go

--创建有表值参数的存储过程
if object_id('TabValueParamTest') is not null
drop proc TabValueParamTest

go

create proc TabValueParamTest
(
	@Tab_Type Tab_Type readonly --表值参数需要加上 readonly
)
as

select stuff((select cast(CustId as nvarchar) ',' UserName '|' from @Tab_Type for xml path('')),1,0,'')

go

declare @tab Tab_Type
insert into @tab
select '刘备' union all
select '关羽'

--测试
exec TabValueParamTest @Tab_Type=@tab

三、用datatable值传参

private static DataTable CreateDatatable()
{
    var dt = new DataTable("Tab_Type");
    dt.Columns.Add(new DataColumn("CustId", typeof(int)));
    dt.Columns.Add(new DataColumn("UserName", typeof(string)));

    var row1 = dt.NewRow();
    row1["CustId"] = 1;
    row1["UserName"] = "刘备";
    dt.Rows.Add(row1);

    var row2 = dt.NewRow();
    row2["CustId"] = 2;
    row2["UserName"] = "关羽";
    dt.Rows.Add(row2);

    return dt;
}

使用此方法会给自增列插入值并引发异常

四、用IEnumerable<SqlDataRecord>传参数

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace TabValueParamTest
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection("server=JSONLEE\\SQLEXPRESS;database=www.singoo.top;uid=sa;pwd=123456"))
            {
                conn.Open();

                var result = "";
                using (SqlCommand cmd = new SqlCommand())
                {
                    var parameter = new SqlParameter("@Tab_Type", CreateSqlDataRecords());
                    parameter.SqlDbType = SqlDbType.Structured; //这句很重要，没有这句，报错说未知映射

                    cmd.Connection = conn;
                    cmd.CommandText = "TabValueParamTest";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(parameter);

                    result = cmd.ExecuteScalar().ToString();
                }

                Console.WriteLine("执行结果："   result);
            }
        }

        private static IEnumerable<SqlDataRecord> CreateSqlDataRecords()
        {
            /*
            public SqlMetaData(string name, SqlDbType dbType, bool useServerDefault, bool isUniqueKey, SortOrder columnSortOrder, int sortOrdinal)
            {
                Construct(name, dbType, useServerDefault, isUniqueKey, columnSortOrder, sortOrdinal);
            }
            主要是useServerDefault，允许使用服务器的默认值，也就允许了表变量的自增列，不会向此列插入值
            */
            var p1 = new SqlMetaData("CustId", SqlDbType.Int, true, false, SortOrder.Unspecified, -1);
            var p2 = new SqlMetaData("UserName", SqlDbType.NVarChar, 50);
            var p = new[] { p1, p2 };

            var c1 = new SqlDataRecord(p);
            c1.SetString(1, "刘备");

            var c2 = new SqlDataRecord(p);
            c2.SetString(1, "关羽");

            return new[] { c1, c2 };
        }
    }
}

c1.SetString(1, "刘备"); 表示c1里面有2个元素，元素1是CustId是默认服务器行为的，并不需要赋值。执行结果也完成正确！

五、SqlMetaData

从微软官网可以知道SqlMetaData是元数据，所谓元数据，基本就是最底层的数据定义了，难怪可以设置服务器的默认行为。

最后把一个或者多个元数据组成一个数据记录，当做表值参数的.net 对象

表值参数可以是.net 的Datatable、DataReader、IEnumerable<SqlDataRecord>三种对象。

参考：

SqlMetaData https://learn.microsoft.com/zh-cn/dotnet/api/microsoft.sqlserver.server.sqlmetadata?redirectedfrom=MSDN&view=dotnet-plat-ext-6.0

表值参数 https://learn.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/bb510489(v=sql.105)

Demo下载

.net给有自增列的表值参数传值

发表评论