public static SqlServerStoredProcedure SqlServerStoredProcedureCollectionToStoreProcedure(DataRow sqlServerStoredProcedure, DataTable sqlServerParametersColletions)
{
SqlServerStoredProcedure storedProcedure = new SqlServerStoredProcedure();
storedProcedure.Name = sqlServerStoredProcedure["routine_name"].ToString();
foreach (DataRow row in sqlServerParametersColletions.Rows)
{
SqlServerParameter parameter = new SqlServerParameter();
parameter.DBName = row["parameter_name"].ToString();
parameter.Direction = SqlServerTranslatorHelper.GetDirection(row["parameter_mode"].ToString());
SqlServerDbDataType dbDataType = new SqlServerDbDataType();
dbDataType.ProviderType = SqlServerDataTypeConverter.String2DatabaseType(row["data_type"].ToString());
dbDataType.Type = SqlServerDataTypeConverter.DatabaseType2NetType(dbDataType.ProviderType);
dbDataType.Precision = SqlServerTranslatorHelper.GetPrecision(row["numeric_precision"]);
dbDataType.Scale = SqlServerTranslatorHelper.GetScale(row["numeric_scale"]);
dbDataType.Size = SqlServerTranslatorHelper.GetParameterSize(dbDataType.ProviderType, row);
parameter.DbDataType = dbDataType;
storedProcedure.Parameters.Add(parameter);
}
return(storedProcedure);
}
private List <Parameter> GetProcedureResultSchema(DataTable schema)
{
List <Parameter> parameters = new List <Parameter>();
foreach (DataRow row1 in schema.Rows)
{
Type type1 = (Type)row1["DataType"];
int num1 = -1;
if (type1 == typeof(string))
{
num1 = (int)row1["ColumnSize"];
}
SqlServerParameter parameter = new SqlServerParameter();
parameter.DBName = SqlServerTranslatorHelper.GetQualifiedName((string)row1["ColumnName"]);
parameter.Direction = ParameterDirection.ReturnValue;
SqlServerDbDataType dbDataType = new SqlServerDbDataType();
dbDataType.Size = num1;
dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row1["ProviderType"]);
dbDataType.Type = type1;
parameter.DbDataType = dbDataType;
parameters.Add(parameter);
}
return(parameters);
}
/// <summary>
/// Gets the views.
/// </summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public override List <View> GetViews(DbConnection connection)
{
List <View> views = new List <View>();
DataTable viewsCollection = GetViewsCollection(connection);
DataRow[] rows = viewsCollection.Select("", "table_schema, table_name ASC");
foreach (DataRow view in rows)
{
string viewName = SqlServerTranslatorHelper.GetObjectName(view["table_schema"].ToString(), NamingHelper.EscapeObjectName(view["table_name"].ToString()));
views.Add(
SqlServerViewTranslator.SqlServerViewCollectionToView(
view,
GetViewColumnsCollection(connection, viewName)));
}
return(views);
}
/// <summary>
/// Gets the tables.
/// </summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
public override List <Table> GetTables(DbConnection connection)
{
List <Table> tables = new List <Table>();
DataTable tablesCollection = GetTablesCollection(connection);
DataRow[] rows = tablesCollection.Select("", "table_schema, table_name ASC");
foreach (DataRow table in rows)
{
string tableName = SqlServerTranslatorHelper.GetObjectName(table["table_schema"].ToString(), NamingHelper.EscapeObjectName(table["table_name"].ToString()));
tables.Add(
SqlServerTableTranslator.SqlServerTableCollectionToTable(
table,
GetTableColumnsCollection(connection, tableName, true),
GetTableForeignKeysCollection(connection, table["table_schema"].ToString(), table["table_name"].ToString())));
}
return(tables);
}
public override List <Parameter> GetSPResultSet(DbConnection connection, StoredProcedure procedure)
{
List <Parameter> parameters = new List <Parameter>();
using (SqlCommand command = (SqlCommand)connection.CreateCommand())
{
command.CommandText = procedure.DBName;
command.CommandType = CommandType.StoredProcedure;
if (connection.ConnectionTimeout != -1)
{
command.CommandTimeout = connection.ConnectionTimeout;
}
foreach (Parameter parameter in procedure.Parameters)
{
SqlParameter parameterToAdd = command.Parameters.Add(
parameter.DBName,
SqlServerDataTypeConverter.DatabaseType2SqlDbType(parameter.DbDataType.ProviderType));
parameterToAdd.Direction = parameter.Direction;
parameterToAdd.Value = SqlServerTranslatorHelper.GetDefaultParameterValue(parameter.DbDataType.Type);
}
SqlTransaction transaction = null;
try
{
transaction = (SqlTransaction)connection.BeginTransaction();
command.Transaction = transaction;
int index = 0;
IDataReader dataReader = command.ExecuteReader(CommandBehavior.SchemaOnly);
try
{
do
{
DataTable table = dataReader.GetSchemaTable();
if (table != null)
{
parameters = GetProcedureResultSchema(table);
}
index++;
} while(dataReader.NextResult());
}
finally
{
if (dataReader != null)
{
dataReader.Dispose();
}
}
}
finally
{
if (transaction != null)
{
transaction.Rollback();
}
}
}
return(parameters);
}
