Dapper use Table Value Parameter in C# (Sql Server 数组参数)

dapper 也可以使用 数组参数

参考:blog on github

dapper 调用存储过程 ：单个参数

  static void main(string[] args)
        {
            var connection = new sqlconnection("data source=.;initial catalog=datamip;integrated security=true;multipleactiveresultsets=true");

            var info = connection.query<users>("sp_getusers", new { id = 5 },
                                   commandtype: commandtype.storedprocedure);
        }

dapper 调用存储过程 ：数组参数

需要使用 sql server 的自定义类型 ： dbo.idlist

create type dbo.idlist
as table
(
  id int
);
go

c# code

  public static list<worklog> querywithtvp()
        {
            int[] idlist = new int[] { 1, 2 };
            var results = new list<worklog>();
            try
            {
                var typeidsparameter = new list<sqldatarecord>();
                // typeid  数组参数对应的字段
                var mymetadata = new sqlmetadata[] { new sqlmetadata("typeid", sqldbtype.int) };
                foreach (var num in idlist)
                {
                    // create a new record, i.e. row.
                    var record = new sqldatarecord(mymetadata);
                    // set the 1st colunm, i.e., position 0 with the correcponding value:
                    record.setint32(0, num);
                    // add the new row to the table rows array:
                    typeidsparameter.add(record);
                }
                using (idbconnection conn = new sqlconnection(dbconfig.connectionstring))
                {
                    conn.open();
                   //调用存储过程，idlist： 自定义类型
                    results =  conn.query<worklog>("dbo.getworklog_bytypeids",
                                        new tablevalueparameter("@typeids", "idlist", typeidsparameter)
                                        , commandtype: commandtype.storedprocedure).tolist();
                }
            }
            catch (exception)
            {

                throw;
            }

            return results;
        }