cmd.AddTableParam for Ids - lobodava/artisan-orm GitHub Wiki

Sometimes it is necessary to pass to a stored procedure a list of identifiers.

Artisan.Orm provides overloads for cmd.AddTableParam method than can create such Id-tables for Byte, Int16, Int32 and Int64 types:

public static void AddTableParam(this SqlCommand cmd, string parameterName, IEnumerable<byte> ids)

public static void AddTableParam(this SqlCommand cmd, string parameterName, IEnumerable<short> ids)

public static void AddTableParam(this SqlCommand cmd, string parameterName, IEnumerable<int> ids)

public static void AddTableParam(this SqlCommand cmd, string parameterName, IEnumerable<long> ids)

If you intend to use these overloads, make sure that the following user-defined table types are created in your database:

create type dbo.TinyIntIdTableType as table
(
	Id tinyint not null primary key clustered
);

GO

create type dbo.SmallIntIdTableType as table
(
	Id smallint not null primary key clustered
);

GO

create type dbo.IntIdTableType as table
(
	Id int not null primary key clustered
);

GO

create type dbo.BigIntIdTableType as table
(
	Id bigint not null primary key clustered
);

Then you can add table valued parameters in your stored procedures like this:

create procedure dbo.SaveUser
    @User    dbo.UserTableType	     readonly,
    @RoleIds dbo.TinyIntIdTableType  readonly
as

-- or

create procedure dbo.DeleteUsers
    @UserIds dbo.IntIdTableType	     readonly
as

See the example of full stored procedure with Id-table use.

⚠️ **GitHub.com Fallback** ⚠️