SQL Server Table Valued Parameters with .NET

- 
private static SqlDataRecord CreatePersonRecord(int id, string name) {
    var metaData = new[] {
            new SqlMetaData("Id", SqlDbType.Int),
            new SqlMetaData("Name", SqlDbType.VarChar, 50)
        };
    var record = new SqlDataRecord(metaData);
    record.SetInt32(0, id);
    record.SetString(1, name);
    return record;
}

private SqlDataRecord[] GetPersonRecords() {
    retun new[] {  CreatePersonRecord(1, "John Doe"), CreatePersonRecord(2, "Jane Doe") };
}

using (var conn = new SqlConnection(cs)) {
    conn.Open();
    using (var comm = conn.CreateCommand()) {
        comm.CommandText = "dbo.TestPerson";
        comm.CommandType = CommandType.StoredProcedure;
        
        var peopleParmsValue = GetPersonRecords();
        if (!peopleParmsValue.Any()) {
            peopleParmsValue = null; // Don't set the value to an empty array
        }
        
        comm.Parameters.Add(new SqlParameter {
            ParameterName = "@people",
            Value = peopleParmsValue,
            SqlDbType = SqlDbType.Structured,
            TypeName = "dbo.PersonType"
        });

        using (var rdr = comm.ExecuteReader()) {
            while (rdr.Read()) {
                Console.WriteLine("{0}: {1}", rdr["Id"], rdr["Name"]);
            }
        }
    }
}


create type dbo.PersonType as table
(
    Id int
    ,Name varchar(50)
);
go
create procedure dbo.TestPerson
(
    @people dbo.PersonType readonly
)
as
begin
    select * from @people;
end;
go

View code on GitHub

Comments

Post a Comment

Popular posts from this blog

Left Join in Entity Framework

-
Image
If you need a left join in Entity Framework, you have a couple options. First, if you’re using a real foreign key that just happens to be nullable, then you can use the regular navigation properties. But if you’re doing a left join manually, or with other factors, then you need to do things just a little differently: Suppose we have the following database: create table dbo . Foods ( FoodID int not null identity primary key , FoodName varchar ( 100 ) not null ) ; go insert dbo . Foods ( FoodName ) values ( 'Pizza' ) , ( 'Chicken' ) , ( 'Potatoes' ) , ( 'Broccoli' ) ; go create table dbo . People ( PersonID int not null identity primary key , FirstName varchar ( 100 ) not null , FavoriteFoodID int null , constraint FK_Person_FavoriteFoodID foreign key ( FavoriteFoodID ) references dbo . Foods ( FoodID ) ) ; go insert dbo . People ( FirstName , FavoriteFoodID ) values ( 'John' , 1 ) , ...
Read more

SQL Dates Table

-
create table dbo . Dates ( TheDate date not null , constraint PK_Dates primary key ( TheDate ) ) ; go declare @StartDate date = '1950-01-01' ; declare @EndDate date = '2099-12-31' ; ; with cte as ( select @StartDate [ TheDate ] union all select dateadd ( day , 1 , [ TheDate ] ) from cte where dateadd ( day , 1 , [ TheDate ] ) < = @EndDate ) insert dbo . Dates ( TheDate ) select TheDate from cte option ( maxrecursion 0 ) ; go select * from dbo . Dates order by TheDate ; go
Read more

HTML Line Breaks With CSS

-
Image
If you have text on the server that may have line breaks, you may have gone through a series of steps to get it to display properly. First, if you weren't really thinking about it, you just dump the output to the screen. ASP.NET and others HTML encode it, so you generally don't have to worry about any kind of script injection. But the line breaks aren't handled properly. < div id = output > @message </ div > *@ Next thing to try, replacing line breaks with a BR tag. Of course, since it's encoded, the HTML shows up as text, and that's no good. < div id = output > @( message . Replace("\r\n", " < br > ").Replace("\n", " < br > ").Replace("\r", " < br > ") ) </ div > Ok, now you need to use Html.Raw, but you can't do it on the whole string because that will put the dangerous raw text into the page. So you have to encode the message, after replacin...
Read more