public void Load(string connectionString)
{
SqlSchema schema = new SqlSchema();
string sql = schema.GetColumnsSelectStatement(Name);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string columnName = reader.GetString(reader.GetOrdinal("Column_Name"));
if (!Columns.Contains(columnName))
{
Columns.Add(columnName);
}
}
reader.Close();
}
}
sql = schema.GetForeignKeyConstraints(Name);
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string foreignKeyName = reader.GetString(reader.GetOrdinal("ForeignKey"));
string editableTableName = reader.GetString(reader.GetOrdinal("TableName"));
string columnName = reader.GetString(reader.GetOrdinal("ColumnName"));
string referenceTableName = reader.GetString(reader.GetOrdinal("ReferenceTableName"));
string referenceColumnName = reader.GetString(reader.GetOrdinal("ReferenceColumnName"));
if (Columns.Contains(columnName))
{
ForeignKey foreignKey = null;
if (myForeignKeys.ContainsByForeignKeyName(foreignKeyName))
{
foreignKey = myForeignKeys.GetByForeignKeyName(foreignKeyName);
}
else
{
foreignKey = new ForeignKey()
{
Name = foreignKeyName, TableName = Name, ReferenceTableName = referenceTableName
};
myForeignKeys.Add(foreignKey);
}
foreignKey.ColumnsNames.Add(columnName);
foreignKey.ReferenceColumnsNames.Add(referenceColumnName);
}
}
reader.Close();
}
}
sql = schema.GetForeignKeyConstraintsToMe(Name);
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string foreignKeyName = reader.GetString(reader.GetOrdinal("ForeignKey"));
string editableTableName = reader.GetString(reader.GetOrdinal("TableName"));
string columnName = reader.GetString(reader.GetOrdinal("ColumnName"));
string referenceTableName = reader.GetString(reader.GetOrdinal("ReferenceTableName"));
string referenceColumnName = reader.GetString(reader.GetOrdinal("ReferenceColumnName"));
if (Columns.Contains(columnName))
{
ForeignKey foreignKey = null;
if (myForeignKeys.ContainsByForeignKeyName(foreignKeyName))
{
foreignKey = myForeignKeys.GetByForeignKeyName(foreignKeyName);
}
else
{
foreignKey = new ForeignKey()
{
Name = foreignKeyName, TableName = referenceTableName, ReferenceTableName = Name
};
myForeignKeys.Add(foreignKey);
}
foreignKey.ColumnsNames.Add(referenceColumnName);
foreignKey.ReferenceColumnsNames.Add(columnName);
}
}
reader.Close();
}
}
connection.Close();
}
}
public DataTable CreateTable(string viewName, string editableTableName, IDbCommand command)
{
//if (command is DuradosCommand)
// return CreateTable(viewName, editableTableName, ((DuradosCommand)command).Command);
DataTable tableFromCommand = null;
try
{
tableFromCommand = GetTableFromCommand(viewName, command is DuradosCommand ? ((DuradosCommand)command).Command : command);
}
catch { }
SqlSchema sqlSchema = GetNewSqlSchema(command);
string sql = sqlSchema.GetColumnsSelectStatement(viewName);
DataTable table = new DataTable(viewName);
command.CommandText = sql;
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string columnName = reader.GetString(reader.GetOrdinal("column_name"));
if (columnName == "b1")
{
int x = 0;
x++;
}
string dataType = reader.GetString(reader.GetOrdinal("data_type"));
bool isNullable = reader.GetString(reader.GetOrdinal("is_nullable")).Equals("YES");
string defaultValue = null;
try
{
if (!viewName.ToLower().Contains("durados"))
{
defaultValue = reader.IsDBNull(reader.GetOrdinal("column_default")) ? null : reader.GetString(reader.GetOrdinal("column_default"));
}
}
catch { }
bool unique = false;
try
{
if (reader is MySql.Data.MySqlClient.MySqlDataReader)
{
int ord = reader.GetOrdinal("column_key");
if (ord >= 0)
{
unique = reader.GetString(ord).Equals("UNI");
}
}
}
catch { }
DataColumn column = new DataColumn();
column.ColumnName = columnName;
Type type = GetType(dataType);
column.Unique = unique;
if (type == null && tableFromCommand != null)
{
if (tableFromCommand.Columns.Contains(columnName))
{
type = tableFromCommand.Columns[columnName].DataType;
}
}
if (type != null)
{
column.DataType = type;
column.AllowDBNull = isNullable;
try
{
if (defaultValue != null)
{
if (type == typeof(bool))
{
if (!string.IsNullOrEmpty(defaultValue))
{
column.DefaultValue = !defaultValue.Equals("b'0'");
}
}
else if (type == typeof(DateTime))
{
column.DefaultValue = Convert.ChangeType(defaultValue, type);
}
else
{
column.DefaultValue = Convert.ChangeType(defaultValue, type);
}
}
}
catch { }
if (column.DataType == typeof(string))
{
int?maxLength = int.MaxValue;
if (!reader.IsDBNull(reader.GetOrdinal("character_maximum_length")))
{
try
{
long maxLength64 = Convert.ToInt64(reader.GetValue((reader.GetOrdinal("character_maximum_length"))));
if (maxLength64 < maxLength)
{
maxLength = Convert.ToInt32(maxLength64);
}
}
catch
{
maxLength = 4000;
}
}
if (maxLength.Value == 100001)
{
maxLength = 100000;
}
if (dataType.ToLower() == "point")
{
maxLength = 100001;
}
column.MaxLength = maxLength.Value;
}
if (column.ExtendedProperties.ContainsKey("dataType"))
{
column.ExtendedProperties["dataType"] = dataType;
}
else
{
column.ExtendedProperties.Add("dataType", dataType);
}
table.Columns.Add(column);
}
}
reader.Close();
//sql = "select COLUMN_NAME, TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and TABLE_NAME = N'" + editableTableName + "'";
sql = sqlSchema.GetAutoIdentityColumns(editableTableName);
if (!string.IsNullOrEmpty(sql))
{
command.CommandText = sql;
reader = command.ExecuteReader();
if (reader.Read())
{
string columnName = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
if (table.Columns.Contains(columnName))
{
if (table.Columns[columnName].DefaultValue != null)
{
table.Columns[columnName].DefaultValue = null;
}
table.Columns[columnName].AutoIncrement = true;
}
}
reader.Close();
}
//sql = "SELECT Col.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Constraint_Type = 'PRIMARY KEY ' AND Col.Table_Name = N'" + editableTableName + "'";
sql = sqlSchema.GetPrimaryKeyColumns(editableTableName);
command.CommandText = sql;
reader = command.ExecuteReader();
List <DataColumn> pk = new List <DataColumn>();
int ordinal = reader.GetOrdinal("COLUMN_NAME");
while (reader.Read())
{
if (!reader.IsDBNull(ordinal))
{
string columnName = reader.GetString(ordinal);
if (table.Columns.Contains(columnName))
{
pk.Add(table.Columns[columnName]);
}
}
}
table.PrimaryKey = pk.ToArray();
reader.Close();
return(table);
}
