public SPOutputConfig(SPResult spResult)
{
_definition = spResult;
Columns = _definition
.Columns
.Select(λ => new OutputColumnConfig(λ))
.ToList();
}
/// <summary>
/// Runs the specified stored procedure and examines the output schema
/// Calling code should handle SQLExceptions for cases where the command is invalid
/// </summary>
/// <param name="procedure">The stored procedure to be tested</param>
/// <param name="parameterInfo">The required parameter, type info, and test value</param>
/// <param name="useRollback">If true the query will be run inside a transaction and rolled back</param>
/// <returns>A list of output schemas</returns>
public IList<SPResult> GetOutputSchema(SPInfo procedure, IEnumerable<IParameterTestInfo> parameterInfo, out string ErrorMsg, bool useRollback = true)
{
using(var con = new SqlConnection(_connectionString))
{
var resultSet = new List<SPResult>();
SqlTransaction testTransaction = null;
SqlDataReader reader;
con.Open();
if (useRollback) testTransaction = con.BeginTransaction("SpTestTransaction");
var cmd = new SqlCommand(string.Format("[{0}].{1}", procedure.Schema, procedure.Name));
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Transaction = testTransaction;
//Populate test parameters
foreach(var pi in parameterInfo)
{
var p = new SqlParameter(pi.Name, pi.DBType);
p.Value = TypeHelpers.ConvertToType(pi.SampleValue, pi.DotNetType);
cmd.Parameters.Add(p);
}
try {
using( reader = cmd.ExecuteReader())
{
var resultIndex = 0;
do
{
//Determine schema for each set returned by stored procedure
var result = new SPResult(resultIndex);
for (int c = 0; c < reader.FieldCount; c++)
{
result.Columns.Add(new OutputColumn(reader.GetFieldType(c), reader.GetName(c)));
}
resultSet.Add(result);
}
while (reader.NextResult());
}
ErrorMsg = null;
}
catch(SqlException ex)
{
ErrorMsg = ex.Message;
}
finally
{
if (useRollback) testTransaction.Rollback();
}
return resultSet;
}
}