for generic execution of stored procedures I found some helpful links to generate code:
https://stackoverflow.com/questions/20115881/how-to-get-stored-procedure-parameters-details
https://raresql.com/2014/01/18/sql-server-how-to-retrieve-the-metadata-of-a-stored-procedure/
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-describe-first-result-set-transact-sql
... but in fact I want to pass in generic data without much of validation before hand, because I want to keep it RAD (rapid application development) and test it with integration tests.
Nevertheless with sqlfiddle.com we can validate that:
create procedure t1(@x int) as
select 1 as resultValue
go
can be executed using (e.g.):
exec dbo.t1 3;
parameters can be queried using
select
'Parameter_name' = name,
'Type' = type_name(user_type_id),
'Nullable' = is_nullable,
'DirectionOut' = is_output,
'Length' = max_length,
'Prec' = case when type_name(system_type_id) = 'uniqueidentifier'
then precision
else OdbcPrec(system_type_id, max_length, precision) end,
'Scale' = OdbcScale(system_type_id, scale),
'Param_order' = parameter_id,
'Collation' = convert(sysname,
case when system_type_id in (35, 99, 167, 175, 231, 239)
then ServerProperty('collation') end) ,
system_type_id, user_type_id
from sys.parameters
where object_id = object_id('dbo.t1')
order by param_order
(first) result record set meta data can be queried using
SELECT * FROM sys.dm_exec_describe_first_result_set ('exec dbo.t1 3', NULL, 0) ;
So execute a stored procedure and retrieve a datatable can be achieved using the code from:
https://stackoverflow.com/questions/25121021/generic-execution-of-stored-procedure-in-csharp
public DataTable RunSP_ReturnDT(string procedureName, List<SqlParameter> parameters, string connectionString)
{
DataTable dtData = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
using (SqlCommand sqlCommand = new SqlCommand(procedureName, sqlConn))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
sqlCommand.Parameters.AddRange(parameters.ToArray());
}
using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(dtData);
}
}
}
return dtData;
}
this link shows an easy way to map datatables and datarows to objects
https://www.exceptionnotfound.net/mapping-datatables-and-datarows-to-objects-in-csharp-and-net-using-reflection/
(things dapper is doing for us in general).
So this opens up a lot of opportunities for strongly typed argument objects (or an on-the-fly generated instance from a json-string) and output handling with a list of strongly typed instances mapped by datarows. There only needs to be a mapping between class and procedure name AND a mapping between argument fields and parameter names.
