Friday, October 13, 2017

generic execution of stored procedures in c# accessing sql server

for generic execution of stored procedures I found some helpful links to generate code:

... 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 we can validate that:
  create procedure t1(@x int) as
    select 1 as resultValue

can be executed using (e.g.):
  exec dbo.t1 3;

parameters can be queried using
   '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:

    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)
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand))
        return dtData;

this link shows an easy way to map datatables and datarows to objects

(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.

No comments: