internal SPConfig(SPInfo definition, ISPLoader loader, TypeMap typeMap)
{
_core = definition;
_loader = loader;
_typeMap = typeMap;
//Defaults:
FriendlyName = NamingHelpers.SplitObjectName(definition.Name);
Enabled = true;
}
/// <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;
}
}
private IEnumerable<SPInfo> getProcedures(SqlDataReader reader)
{
var index = reader.GetColumnLookup();
foreach (var r in reader)
{
var id = reader.GetInt32(index["ID"]);
var name = reader.GetString(index["Name"]);
var schema = reader.GetString(index["Schema"]);
var dateCreated = reader.GetDateTime(index["Created"]);
var dateModified = reader.GetDateTime(index["Modified"]);
var sp = new SPInfo(id, name, schema, dateCreated, dateModified);
yield return sp;
}
}
/// <summary>
/// Runs the specified stored procedure but doesn't read the result
/// This is used to make sure the command/query is actually valid
/// </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>
public void TestExecution(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 = null;
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
{
reader = cmd.ExecuteReader();
ErrorMsg = null;
}
catch(SqlException ex)
{
ErrorMsg = ex.Message;
}
finally
{
if(reader != null) reader.Close();
};
}
}
/// <summary>
/// Gets a list of the parameters for the specified stored procedure
/// </summary>
/// <param name="procedure"></param>
/// <returns>All parameters for the specified query</returns>
public IList<SPParameter> GetParameters(SPInfo procedure)
{
var query = @"select p.name as Name, p.object_id as ProcedureId, p.parameter_id as 'position', t.name as 't.name',
p.is_output as 'output', p.max_length as 't.maxLen', p.precision as 't.precision',
p.scale as 't.scale', p.is_readonly as readonly, p.has_default_value as hasDefault,
default_value as defaultValue
from sys.parameters p
left join sys.types t ON t.user_type_id = p.user_type_id
where p.object_id = @sp_id";
using (var cnn = new SqlConnection(_connectionString))
{
cnn.Open();
var cmd = new SqlCommand(query, cnn);
cmd.Parameters.Add(new SqlParameter("@sp_id", procedure.Id));
using(var reader = cmd.ExecuteReader())
{
var result = getParameters(reader).Select(λ => λ.Item2).ToList();
return result;
}
}
}
