Exemplo n.º 1
0
 public SPOutputConfig(SPResult spResult)
 {
     _definition = spResult;
     Columns = _definition
         .Columns
         .Select(λ => new OutputColumnConfig(λ))
         .ToList();
 }
Exemplo n.º 2
0
        /// <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;
            }
        }