Generic Execution of Stored Procedure in CSharp

c# computer-science dapper sql-server stored-procedures

Question

I am creating a generic library to execute stored procedures. Different stored procedures could have different number of input parameters.

Return type is always a table.

Using ADO.NET or Dapper, I can execute an SP with parameters, but how to make it generic to execute any SP that takes input parameters and returns a table result.

Thanks

Accepted Answer

    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;
    }


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why