public static SqlServerView SqlServerViewCollectionToView(DataRow sqlServerView, DataTable sqlServerColumnsColletions)
{
SqlServerView view = new SqlServerView();
view.Name = sqlServerView["table_name"].ToString();
view.Schema = sqlServerView["table_schema"].ToString();
foreach (DataRow row in sqlServerColumnsColletions.Rows)
{
SqlServerColumn column = new SqlServerColumn();
SqlServerDbDataType dbDataType = new SqlServerDbDataType();
column.Name = row["ColumnName"].ToString();
column.IsNullable = (bool)row["AllowDBNull"];
dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row["ProviderType"]);
dbDataType.Type = (Type)row["DataType"];
dbDataType.Size = (int)row["ColumnSize"];
dbDataType.Precision = (Int16)row["NumericPrecision"];
dbDataType.Scale = (Int16)row["NumericScale"];
column.DbDataType = dbDataType;
view.Columns.Add(column);
}
return(view);
}
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);
}
public static SqlServerTable SqlServerTableCollectionToTable(DataRow sqlServerTable, DataTable sqlServerColumnsColletions, DataTable sqlServerForeignKeysCollection)
{
SqlServerTable table = new SqlServerTable();
table.Name = sqlServerTable["table_name"].ToString();
table.Schema = sqlServerTable["table_schema"].ToString();
DataRow[] rows = sqlServerColumnsColletions.Select("", "ColumnName ASC");
foreach (DataRow row in rows)
{
SqlServerColumn column = new SqlServerColumn();
SqlServerDbDataType dbDataType = new SqlServerDbDataType();
column.Name = row["ColumnName"].ToString();
column.IsPrimaryKey = (bool)row["IsKey"];
column.IsNullable = (bool)row["AllowDBNull"];
column.IsUnique = (bool)row["IsUnique"];
column.IsIdentity = (bool)row["IsIdentity"];
column.IsReadOnly = (bool)row["IsReadOnly"];
dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row["ProviderType"]);
dbDataType.Type = (Type)row["DataType"];
dbDataType.Size = (int)row["ColumnSize"];
dbDataType.Precision = (Int16)row["NumericPrecision"];
dbDataType.Scale = (Int16)row["NumericScale"];
column.DbDataType = dbDataType;
table.Columns.Add(column);
}
foreach (DataRow row in sqlServerForeignKeysCollection.Rows)
{
string columnName = row["ColumnName"].ToString();
table.Columns.Find(
delegate(Column column)
{ return(column.Name.Equals(columnName)); }).IsForeignKey = true;
}
return(table);
}
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);
}
