public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
{
string sql = "exec [" + storedProcedure.Name + "] ";
for (int i = 0; i < storedProcedure.Parameters.Length; i++)
{
Model.StoredProcedure.Parameter parameter = storedProcedure.Parameters[i];
sql += parameter.Name + "=NULL";
if (i < storedProcedure.Parameters.Length - 1)
{
sql += ", ";
}
}
DataTable dataTable = RunQueryDataTable(sql);
while (storedProcedure.Columns.Length > 0)
{
storedProcedure.RemoveColumn(storedProcedure.Columns[0]);
}
foreach (DataColumn dataColumn in dataTable.Columns)
{
Column column = new Column(
dataColumn.ColumnName,
false,
storedProcedure,
dataColumn.Ordinal,
dataColumn.AllowDBNull,
GetSqlDataType(dataColumn.DataType),
0,
false,
dataColumn.AutoIncrement,
dataColumn.DefaultValue.ToString(),
false);
if (IsSupported(column))
{
storedProcedure.AddColumn(column);
}
}
}
public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
{
string sql = string.Format("SET FMTONLY ON EXEC [{0}].[{1}] ", storedProcedure.Schema, storedProcedure.Name);
for (int i = 0; i < storedProcedure.Parameters.Length; i++)
{
if (Providers.Database.Helper.SQLServer.IsDataTypeText(storedProcedure.Parameters[i].DataType))
{
sql += "\"0\", ";
}
else
{
sql += "0, ";
}
}
sql = sql.TrimEnd(EndChars);
DataTable dataTable;
try
{
dataTable = RunQueryDataTable(sql);
}
catch (Exception ex)
{
//ResetConnection();
sql = sql.Replace("SET FMTONLY ON", "SET FMTONLY OFF");
try
{
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.RequiresNew))
{
dataTable = RunQueryDataTable(sql);
// We must ALWAYS rollback because we are executing stored procs with FMTONLY = OFF, so just don't call scope.Complete()
}
}
catch
{
storedProcedure.Errors.Add(string.Format("Error getting returned columns: {0}. SQL: {1}", ex.Message, sql));
return;
}
}
while (storedProcedure.Columns.Length > 0)
{
storedProcedure.RemoveColumn(storedProcedure.Columns[0]);
}
foreach (DataColumn dataColumn in dataTable.Columns)
{
Column column = new Column(
dataColumn.ColumnName,
false,
storedProcedure,
dataColumn.Ordinal,
dataColumn.AllowDBNull,
GetSqlDataType(dataColumn.DataType),
0,
false,
dataColumn.AutoIncrement,
dataColumn.DefaultValue.ToString(),
false,
false,
0,
0);
if (IsSupported(column))
{
storedProcedure.AddColumn(column);
}
}
}