private List <StoredProcedureResultElement> GetStoredProcedureResultElements(SqlConnection connection, string schema, string name)
{
var dtResult = new DataTable();
var result = new List <StoredProcedureResultElement>();
var sql = $@"exec dbo.sp_describe_first_result_set N'[{schema}].[{name}]';";
var adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, connection)
};
adapter.Fill(dtResult);
int rCounter = 0;
foreach (DataRow res in dtResult.Rows)
{
var parameter = new StoredProcedureResultElement()
{
Name = string.IsNullOrEmpty(res["name"].ToString()) ? $"Col{rCounter}" : res["name"].ToString(),
StoreType = res["system_type_name"].ToString(),
Ordinal = int.Parse(res["column_ordinal"].ToString()),
Nullable = (bool)res["is_nullable"],
};
result.Add(parameter);
rCounter++;
}
return(result);
}
public static SqlDbType DbType(this StoredProcedureResultElement storedProcedureResultElement)
{
return(GetSqlDbType(storedProcedureResultElement.StoreType));
}
public static Type ClrType(this StoredProcedureResultElement storedProcedureResultElement)
{
return(GetClrType(storedProcedureResultElement.StoreType, storedProcedureResultElement.Nullable));
}