public Dictionary<string, List<SpColumn>> RetriveStoreProcedureParameterMeta(string[] storeProcedureNames)
{
var stroePorcedureParameterMeta = new Dictionary<string, List<SpColumn>>();
for (int i = 0; i < storeProcedureNames.Length; i++)
{
List<SpColumn> columns = new List<SpColumn>();
SqlParameterCollection collection = GetParameterCollectionByProcedure(storeProcedureNames[i]);
for (int j = 0; j < collection.Count; j++)
{
SpColumn spColumn = new SpColumn();
spColumn.CName = collection[j].ParameterName;
spColumn.IsNullable = collection[j].IsNullable;
spColumn.CType = DataTypeMapper.MapFromDBType(collection[j].SqlDbType.ToString());
spColumn.Length = collection[j].Size;
columns.Add(spColumn);
}
stroePorcedureParameterMeta.Add(storeProcedureNames[i], columns);
}
return stroePorcedureParameterMeta;
}
public Dictionary<string, List<SpColumn>> RetriveStoreProcedureResultMeta(Dictionary<string, string> storeProcedureFailed, string[] storeProcedureNames, [Optional, DefaultParameterValue(null)] Dictionary<string, SqlParameter[]> storeProcedureParameters)
{
var storeProcedureResultMeta = new Dictionary<string, List<SpColumn>>();
SqlCommand innerCommand = new SqlCommand();
SqlDataReader innerReader = null;
innerCommand.CommandType = CommandType.StoredProcedure;
innerCommand.Connection = connection;
for (int i = 0; i < storeProcedureNames.Length; i++)
{
try
{
List<SpColumn> columns = new List<SpColumn>();
innerCommand.CommandText = storeProcedureNames[i];
innerCommand.Parameters.Clear();
if (storeProcedureParameters == null)
{
SqlParameterCollection collection = GetParameterCollectionByProcedure(storeProcedureNames[i]);
for (int j = 0; j < collection.Count; j++)
{
if (collection[j].IsNullable)
continue;
SqlParameter newParameter = new SqlParameter(collection[j].ParameterName, collection[j].Value);
innerCommand.Parameters.Add(newParameter);
}
}
else
{
innerCommand.Parameters.AddRange(storeProcedureParameters[storeProcedureNames[i]]);
}
if (connection.State == System.Data.ConnectionState.Closed)
connection.Open();
innerReader = innerCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataTable table = innerReader.GetSchemaTable();
if (table == null)
{
table = emptyDataTable;
innerReader.Close();
}
int emptyColumnNameSerial = 0;
for (int k = 0; k < table.Rows.Count; k++)
{
SpColumn spColumn = new SpColumn();
spColumn.CName = table.Rows[k]["ColumnName"].ToString().Trim() == "" ? "Column" + (++emptyColumnNameSerial) : table.Rows[k]["ColumnName"].ToString().Trim();
spColumn.IsNullable = true;
spColumn.CType = DataTypeMapper.MapFromDBType(table.Rows[k]["DataTypeName"].ToString());
columns.Add(spColumn);
}
storeProcedureResultMeta.Add(storeProcedureNames[i], columns);
innerReader.Close();
}
catch (Exception e)
{
storeProcedureFailed.Add(storeProcedureNames[i], e.Message);
}
finally
{
if (innerReader!=null && !innerReader.IsClosed)
innerReader.Close();
}
}
return storeProcedureResultMeta;
}
public void FillParameter(StoreProcedure sp)
{
List<SpColumn> columns = new List<SpColumn>();
SqlParameterCollection collection = GetParameterCollectionByProcedure(sp.Name);
for (int j = 0; j < collection.Count; j++)
{
SpColumn spColumn = new SpColumn();
spColumn.CName = collection[j].ParameterName;
spColumn.IsNullable = collection[j].IsNullable;
spColumn.CType = DataTypeMapper.MapFromDBType(collection[j].SqlDbType.ToString());
columns.Add(spColumn);
}
sp.Parameters = columns;
}