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