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