Skip to content

Instantly share code, notes, and snippets.

@Antaris
Forked from divega/MyContext.cs
Created October 14, 2016 05:27
Show Gist options
  • Save Antaris/31b89ffb9dccf9ff436eb532f4106720 to your computer and use it in GitHub Desktop.
Save Antaris/31b89ffb9dccf9ff436eb532f4106720 to your computer and use it in GitHub Desktop.
Simple builder class for creating TVPs that work in .NET Core 1.0
using Microsoft.EntityFrameworkCore;
namespace TvpSampleApp
{
public class MyContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server = (localdb)\\mssqllocaldb; Database=TvpSample; Integrated Security=yes");
}
}
}
namespace TvpSampleApp
{
public class Person
{
public int Id { get; set; }
}
}
using Microsoft.EntityFrameworkCore;
using Microsoft.SqlServer.Server;
using System.Data;
namespace TvpSampleApp
{
public class Program
{
public static void Main(string[] args)
{
using (var context = new MyContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.Database.ExecuteSqlCommand(@"CREATE TYPE dbo.IdTable AS TABLE ( Id Int);");
context.People.Add(new Person { });
context.People.Add(new Person { });
context.People.Add(new Person { });
context.SaveChanges();
}
using (var context = new MyContext())
{
var tvp = new TableValuedParameterBuilder("[dbo].[IdTable]", new SqlMetaData("Id", SqlDbType.Int))
.AddRow(1)
.AddRow(2)
.CreateParameter("p0");
var results = context.People.FromSql("SELECT * FROM People WHERE Id IN (SELECT * FROM @p0)", tvp);
foreach (var result in results)
{
System.Console.WriteLine(result.Id);
}
}
}
}
}
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace TvpSampleApp
{
public class TableValuedParameterBuilder
{
string _typeName;
SqlMetaData[] _columns;
List<SqlDataRecord> _rows;
public TableValuedParameterBuilder(string typeName, params SqlMetaData[] columns)
{
_typeName = typeName;
_columns = columns;
_rows = new List<SqlDataRecord>();
}
public TableValuedParameterBuilder AddRow(params object[] fieldValues)
{
var row = new SqlDataRecord(_columns);
row.SetValues(fieldValues);
_rows.Add(row);
return this;
}
public SqlParameter CreateParameter(string name)
{
return new SqlParameter
{
ParameterName = name,
Value = _rows,
TypeName = _typeName,
SqlDbType = SqlDbType.Structured
};
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment