0

I'm using ASP.NET MVC Framework and SQL Server for this one. I have created a table type which will have the list of ids coming from a variable in C#

CREATE TYPE [dbo].[RespondentTypeTable] AS TABLE
(
    Id INT NOT NULL
)

I have created a procedure to pass the table-valued parameter with the table-type created above in readonly mode and 2 integer parameters. The stored procedure will insert the records from the other joined tables mentioned in the statement into a local temporary table and will get the results from the temp table and drop it after fetching the records.

CREATE PROCEDURE GetSensitivityDataForDimension(@respondentIdType [dbo].[RespondentTypeTable] READONLY, @studyId INT, @dimension INT )
AS
BEGIN

CREATE TABLE #AllUtilityDataDTO  
(
    AttributeLevelId INT NOT NULL,
    AttributeLevelName NVARCHAR(MAX),
    AttributeId INT NOT NULL,
    AttributeName NVARCHAR(MAX),
    RespondentId INT NOT NULL,
    UtilityValue INT
);

BEGIN
INSERT INTO #AllUtilityDataDTO 
(
    AttributeLevelId,
    AttributeLevelName,
    AttributeId,
    AttributeName,
    RespondentId,
    UtilityValue
)
SELECT al.Id AS AttributeLevelId,
       al.DisplayText AS AttributeLevelName,
       a.Id AS AttributeId,
       a.DisplayText AS AttributeName,
       usd.RespondentId,
       usd.Value AS UtilityValue
FROM dbo.Attribute a
    INNER JOIN dbo.AttributeLevel al
        ON al.AttributeId = a.Id
    INNER JOIN dbo.UtilityScoreData usd
        ON usd.AttributeLevelId = al.Id
WHERE a.StudyId = @studyId
      AND usd.RespondentId IN
          (
        (SELECT Id FROM @respondentIdType)
          )
      AND
      (
          (usd.Dimension IS NOT NULL
          AND usd.Dimension = @dimension)
      )
      OR
      (
          (usd.Dimension IS NULL
          AND @dimension = 1)
      );

SELECT * FROM #AllUtilityDataDTO
END
GO

DROP TABLE #AllUtilityDataDTO
END

The SELECT Id FROM @respondentIdType should fetch all the respondent ids that is passed to the table-valued parameter from the code. The problem lies after this. I'm unable to convert the list of integers to a dataTable type in order to pass the values as a table-valued parameter.

Here is the code that I'm using in C# -

The respondentIds being fetched as a list of integers(List) from a respository function -

var respondentIds = _studyRepository.GetFilteredRespondentsIds(studyId, filters, minRLH);

I'm converting the ids that are being fetched into a using the below converter by passing the respondentIds variable as a parameter -

ListtoDataTableConverter dataTableConverter = new ListtoDataTableConverter();
            DataTable respondentIdTable = dataTableConverter.ToDataTable(respondentIds);
public class ListtoDataTableConverter
        {
            public DataTable ToDataTable<T>(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)
                {
                    //Setting column names as Property names
                    dataTable.Columns.Add(prop.Name);
                }
                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;
            }
        }

Then creating a SqlCommand to execute the stored procedure and adding parameters along with their values.

using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // Create a DataTable with the modified rows.  
                DataTable addedrespondentIds = respondentIdTable.GetChanges();

                // Configure the SqlCommand and SqlParameter.  
                SqlCommand insertCommand = new SqlCommand("GetSensitivityDataForDimensionProc", connection);
                insertCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter respondentParam = insertCommand.Parameters.AddWithValue("@respondentIdType", addedrespondentIds);
                respondentParam.SqlDbType = SqlDbType.Structured;
                respondentParam.TypeName = "dbo.RespondentTypeTable";
                SqlParameter studyIdParam = insertCommand.Parameters.AddWithValue("@studyId", studyId);
                respondentParam.SqlDbType = SqlDbType.Int;
                SqlParameter dimensionParam = insertCommand.Parameters.AddWithValue("@dimension", dimension);
                respondentParam.SqlDbType = SqlDbType.Int;

                // Execute the command.  
                insertCommand.ExecuteNonQuery();

                connection.Close();

            }

Here is the stored procedure and table type classes that i'm using for this -

SP class

namespace XYZData.Repositories.SqlStoredProcedures
{
    [StoredProcedure("GetSensitivityDataForDimension")]
    public class GetSensitivityDataForDimensionProc
    {
        [StoredProcedureParameter(SqlDbType.Structured)]
        public DataTable RespondentIds { get; set; }

        [StoredProcedureParameter(SqlDbType.Int)]
        public int StudyId { get; set; }
        
        [StoredProcedureParameter(SqlDbType.Int)]
        public int Dimension { get; set; }
    }
}

Table type class

namespace XYZData.Repositories.SqlUserDefinedTypes
{
    [UserDefinedTableType("[dbo].[RespondentTypeTable]")]
    public class RespondentTableType
    {
        [UserDefinedTableTypeColumn(1)]
        public Int Id { get; set; }
    }
}

I tried converting the list of integers to dataTable but it is giving System.InvalidCastException: 'Failed to convert parameter value from a DataTable to a Int32.' error while running the application. How will I pass the list of integers to the table-valued parameter?

Ankit Das
  • 9
  • 2
  • Maybe this [Stackoverflow](https://stackoverflow.com/questions/18522388/call-stored-procedure-with-custom-type-parameter-from-c) question can help you. – Max Dec 20 '22 at 14:40
  • Does this answer your question? [Pass Array Parameter in SqlCommand](https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) – MD Zand Dec 20 '22 at 17:45

0 Answers0