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 select statement into a local temporary table allutilitydatadto 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 datatable 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?