How to call a stored procedure that has several types in C# on .NET Core-CodePudding

I wrote a stored procedure that takes two types I created as parameters, called Type1 and Type2 :

CREATE TYPE Type1 AS TABLE
(
    Id [int] NULL,
    Value [float] NULL
)

CREATE TYPE Type2 AS TABLE
(
    Id [int] NULL,
    Name [varchar](20) NULL,
    Value [float] NULL
)

CREATE PROCEDURE [SP-Name]
    (@Id INT = 1,
     @UserName VARCHAR(500) = 'test',
     @Item1 Type1 READONLY,
     @Item2 Type2 READONLY)
AS
BEGIN
    SELECT * 
    FROM @Item1
END;

When I call this stored procedure in SQL Server itself, it runs without error, but I write the same code in C#, I get an error. What is the problem?

This is how I call this stored procedure in C#:

ENTITIES.SqlQuery<Result>($"EXEC [SP-Name] @Item1",
      new[] {
               new SqlParameter
               {
                   SqlDbType = SqlDbType.Structured,
                   TypeName = "Type1",
                   Value = listItems.ToDataTable(),//Get list Item from database and Convert to DATA-TABLE
                   ParameterName = "Item1"
               }}).ToList();

public class Result 
{
    public int Id { get; set; }
    public string Value { get; set; }
}

public static DataTable ToDataTable<T>(this List<T> items)
{
        DataTable dataTable = new DataTable(typeof(T).Name);

        // Get all the properties
        PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (PropertyInfo prop in Props)
        {
            // Defining type of data column gives proper data table 
            var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);

            // Setting column names as Property names
            dataTable.Columns.Add(prop.Name, type);
        }

        foreach (T item in items)
        {
            var values = new object[Props.Length];

            for (int i = 0; i < Props.Length; i  )
            {
                // inserting property values to datatable rows
                values[i] = Props[i].GetValue(item, null);
            }

            dataTable.Rows.Add(values);
        }

        // put a breakpoint here and check datatable
        return dataTable;
    }

This is the error I get:

Operand type clash: Item1 is incompatible with int

CodePudding user response：

The problem here is you think that because you have a variable called @Item1 that it will be inferred, by SQL Server, to be for the parameter @Item1, as they share the same name; this assumption is wrong.

When you omit the parameter that the variable is being passed to, the variables (or literals) are passed to the parameters based on ordinal position. You pass one value for your parameters, @Item1, which will be passed to your first parameter, @Id. This is why you get an error. @Item1 is defined as a structured data type (SqlDbType.Structured), but @Id is an int; there are not the same data types, and you get an error.

If you want to omit parameters, be explicit on the parameters you are passing:

ENTITIES.SqlQuery<Result>($"EXEC dbo.[SP-Name] @Item1 = @Item1;",

Or, probably better, just remove all the other parameters from the procedure, as you don't use them:

CREATE PROCEDURE dbo.[SP-Name] @Item1 Type1 READONLY
AS
BEGIN
    SELECT * 
    FROM @Item1;
END;

Then you can use use EXEC dbo.[SP-Name] @Item1;.