コード例 #1
0
        /// <summary>
        ///     Get Column info from Stored procedure result set
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        public List <ResultColumn> GetColumnInfoFromStoredProcResult(string schema, string storedProcName)
        {
            //this one actually needs to use the dataset because it has the only accurate information about columns and if they can be null or not.
            var sb = new StringBuilder();

            if (!String.IsNullOrEmpty(schema))
            {
                sb.Append(String.Format("exec [{0}].[{1}] ", schema, storedProcName));
            }
            else
            {
                sb.Append(String.Format("exec [{0}] ", storedProcName));
            }

            var prms = GetStoredProcedureInputParameters(schema, storedProcName);

            var count = 1;

            foreach (var param in prms)
            {
                sb.Append(String.Format("{0}=null", param.Name));
                if (count < prms.Count)
                {
                    sb.Append(", ");
                }
                count++;
            }

            var ds = new DataSet();

            using (var sqlConnection = (SqlConnection)databaseWrapper.GetOpenDbConnection())
            {
                using (var sqlAdapter = new SqlDataAdapter(sb.ToString(), sqlConnection))
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }

                    sqlAdapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly);

                    sqlConnection.Close();

                    sqlAdapter.FillSchema(ds, SchemaType.Source, "MyTable");
                }
            }

            var list = new List <ResultColumn>();

            if (ds.Tables.Count > 0)
            {
                list = ds.Tables["MyTable"].Columns.Cast <DataColumn>().ToList().ToResultColumns();
            }

            return(list);
        }