What is the equivalent passing array int to stored procedure .net 4.6 code for .net core?

Question

So this link, shows how to pass an int array to stored procedure.

CREATE TYPE dbo.EmployeeList
AS TABLE
(
  EmployeeID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.EmployeeList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT EmployeeID FROM @List; 
END
GO

and c# Code:

DataTable tvp = new DataTable();
// define / populate DataTable from your List here

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    // execute query, consume results, etc. here
}

But since .net core does not support datatable, what would be the equivalent code for .net core? Especially adding tvp variable as a parameter in AddWithValue method and also the last line of code which is tvparam.SqlDbType = SqlDbType.Structured?

Edit

So I have this stored procedure that takes three parameters. @StudentIds is an int array.

ALTER PROCEDURE stp_Student

@Name nvarchar(450), 
@StudentIds tvp_ArrayInt READONLY,  
@StartDate date

AS

blah blah 

//returns student summary
SELECT  stu.StudentId, 
        stu.StudentName, 
        CASE WHEN (COUNT(DISTINCT course.Id) IS NOT NULL) THEN COUNT(DISTINCT course.Id) ELSE 0 END AS CourseCount, 
        CASE WHEN (SUM(course.TotalCourses) IS NOT NULL) THEN SUM(course.TotalCourses) ELSE 0 END AS TotalCourses, 
        CASE WHEN (SUM(course.Hours) IS NOT NULL) THEN SUM(course.Hours) ELSE 0 END AS Hours
FROM    #TempStudent AS #Temp

and based on your recommendation, I tried to do it in controller:

public ActionResult Submit(string name, int[] studentIds, DateTime startDate)
{
    context.Database.ExecuteSqlCommand(@"CREATE TYPE dbo_ArrayInt AS TABLE (intValue Int);");

    var tvp = new TableValuedParameterBuilder("dbo_ArrayInt", new SqlMetaData("intValue", SqlDbType.Int))
            .AddRow(1)
            .AddRow(2)
            .CreateParameter("StudentIds");

    //But here, I'm not sure how to use tvp variable appropriately
     var query = _context.Set<StudentModel>().FromSql("dbo.stp_Student @Name = {0}, @StudentIds = {1}, @StartDate = {2}"
            , name, studentIds, startDate).ToList();

Entity for mapping the result:

public class StudentModel
{  
    [Key]
    public long RowId { get; set; }   
    public int StudentId { get; set; }    
    public string StudentName { get; set; }
    public int CourseCount { get; set; }
    [Column(TypeName = "Money")]
    public decimal TotalCourses { get; set; }
    public double Hours { get; set; }
}

score 2 · Accepted Answer · answered Feb 20 '17 at 03:38

Here is a complete, self contained example on .net core:

SCHEMA

CREATE TABLE dbo.Students (
    StudentId int NOT NULL,
    StudentName varchar(50) NOT NULL
) ON [PRIMARY]
GO

CREATE TYPE dbo.ArrayInt
AS TABLE
(
  intValue INT
);
GO

CREATE PROCEDURE dbo.stp_Student
    @Name nvarchar(450), 
    @StudentIds dbo.ArrayInt READONLY,  
    @StartDate date
AS
    SELECT  
        StudentId, 
        StudentName, 
        3 AS CourseCount, 
        6 AS TotalCourses, 
        9 AS [Hours]
    FROM
        dbo.Students A
        JOIN @StudentIds B ON A.StudentId = B.intValue

CODE

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;

namespace StackOverflow.TpvConsole
{
    public class Program
    {
        private const String SqlConnetionString = @"Server={Server};Database={Database};User={User};Password={Password};";

        public static void Main(String[] args)
        {
            var model = new List<StudentModel>();

            var parameter = new SqlParameter("StudentIds", SqlDbType.Structured)
            {
                Direction = ParameterDirection.Input,
                TypeName = "dbo.ArrayInt"
            };

            var items = new List<SqlDataRecord>();

            //just a quick loop to add three student ids
            for (var i = 1; i < 4; i++)
            {
                var rec = new SqlDataRecord(new SqlMetaData("intValue", SqlDbType.Int));
                rec.SetInt32(0, i);
                items.Add(rec);
            }

            parameter.Value = items;

            using (var db = new SqlConnection(SqlConnetionString))
            {
                var sqlCommand = new SqlCommand("dbo.stp_Student", db) {CommandType = CommandType.StoredProcedure};

                sqlCommand.Parameters.Add(new SqlParameter("Name", "Student Name"));
                sqlCommand.Parameters.Add(parameter);
                sqlCommand.Parameters.Add(new SqlParameter("StartDate", new DateTime(2016, 8, 1)));

                db.Open();

                var reader = sqlCommand.ExecuteReader();

                while (reader.Read())
                {
                    model.Add(new StudentModel
                    {
                        StudentId = reader.GetInt32(0),
                        StudentName = reader.GetString(1),
                        CourseCount = reader.GetInt32(2),
                        TotalCourses = reader.GetInt32(3),
                        Hours = reader.GetInt32(4)
                    });
                }
            }

            foreach (var item in model)
            {
                Console.WriteLine($"{item.StudentId}: {item.StudentName}");
            }

            Console.ReadKey();
        }

        public class StudentModel
        {
            [Key]
            public Int64 RowId { get; set; }
            public Int32 StudentId { get; set; }
            public String StudentName { get; set; }
            public Int32 CourseCount { get; set; }
            [Column(TypeName = "Money")]
            public Decimal TotalCourses { get; set; }
            public Double Hours { get; set; }
        }
    }
}

score 0 · Answer 2 · answered Feb 19 '17 at 04:43

0

These are called Table Value Parameters.

According to a member of the Entity Framework team, you can use a IEnumerable<SqlDataRecord> in place of a DataTable. A fully functional gist has been posted.

answered Feb 19 '17 at 04:43

vcsjones

138,677
31
291
286

What is the equivalent passing array int to stored procedure .net 4.6 code for .net core?

2 Answers2