public override DataDomain BuildDomain()
{
DataDomain domain = new DataDomain(this.Driver.DomainUrl);
using (MySqlConnection connection = this.Driver.OpenConnection() as MySqlConnection)
{
string commandTextTables = string.Format("SELECT Table_Name FROM information_schema.TABLES WHERE Table_Schema=\"{0}\" AND Table_Type=\"Base Table\"", this.Driver.DomainUrl.Database);
MySqlCommand command = new MySqlCommand(commandTextTables, connection);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string tableName = reader.GetString(0);
Table table = new Table(tableName);
domain.AddTable(table);
}
}
string commandTextColumns = string.Format("SELECT table_catalog,table_schema,table_name,column_name,is_nullable,data_type,extra,column_type,column_key,character_maximum_length,numeric_precision,numeric_scale FROM information_schema.COLUMNS WHERE table_schema=\"{0}\"", this.Driver.DomainUrl.Database);
command = new MySqlCommand(commandTextColumns, connection);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string tableName = reader.GetString(2); //"TABLE_NAME"
Table table = domain[tableName];
if (table != null)
{
Column column = new Column();
column.Name = reader.GetString(3); //"COLUMN_NAME"
string dataType = reader.GetString(5); //"DATA_TYPE"
MySqlDbType mySqlDbType = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), dataType);
column.OriginalSQLType = mySqlDbType.ToString();
if (_sqlTypesDictionary.Contains(dataType))
{
column.SqlType = (SqlType)_sqlTypesDictionary[dataType];
}
else
{
column.SqlType = SqlType.Unknown;
}
if (_netDataTypes.ContainsKey(column.SqlType))
{
column.NetDataType = (string)_netDataTypes[column.SqlType];
column.ActionScriptType = TypeMapper.GetActionScriptType(_netDataTypes[column.SqlType] as string);
}
else
{
column.NetDataType = "unknown";
}
if ((column.SqlType == SqlType.Char) ||
(column.SqlType == SqlType.AnsiChar) ||
(column.SqlType == SqlType.VarChar) ||
(column.SqlType == SqlType.AnsiVarChar) ||
(column.SqlType == SqlType.Text) ||
(column.SqlType == SqlType.Binary) ||
(column.SqlType == SqlType.VarBinary))
{
column.Length = reader.GetInt32(9); //"CHARACTER_MAXIMUM_LENGTH";
}
else if (column.SqlType == SqlType.Decimal)
{
column.Precision = reader.GetByte(10); //numeric_precision
column.Scale = reader.GetInt32(11); //"NUMERIC_SCALE"
}
if ((column.SqlType == SqlType.Binary) ||
(column.SqlType == SqlType.VarBinary))
{
column.IsBlob = true;
}
if (column.Length == -1)
{
switch (column.SqlType)
{
case SqlType.VarChar:
column.SqlType = SqlType.VarCharMax;
column.Length = 0;
break;
case SqlType.AnsiVarChar:
column.SqlType = SqlType.AnsiVarCharMax;
column.Length = 0;
break;
case SqlType.VarBinary:
column.SqlType = SqlType.VarBinaryMax;
column.Length = 0;
break;
default:
break;
}
}
if (!reader.IsDBNull(4)) //IS_NULLABLE
{
string tmp = reader.GetString(4);
column.IsNullable = ("yes" == tmp.ToLower());
}
if (!reader.IsDBNull(6)) //"extra"
{
string tmp = reader.GetString(6);
column.IsIdentity = ("auto_increment" == tmp.ToLower());
}
table.AddColumn(column);
}
}
}
string commandTextKeys = string.Format("SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA and kcu.TABLE_NAME = tc.TABLE_NAME WHERE ((tc.CONSTRAINT_TYPE = 'PRIMARY KEY') and (kcu.TABLE_SCHEMA =\"{0}\" )) order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION", this.Driver.DomainUrl.Database);
command = new MySqlCommand(commandTextKeys, connection);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string tableName = reader.GetString(1); //"TABLE_NAME"
Table table = domain[tableName];
if (table != null)
{
string columnName = reader.GetString(4); //"COLUMN_NAME"
Column column = table[columnName];
if (column != null)
{
column.IsPrimaryKey = true;
}
}
}
}
}
return(domain);
}
public override DataDomain BuildDomain()
{
DataDomain domain = new DataDomain(this.Driver.DomainUrl);
using (IDbConnection connection = this.Driver.OpenConnection())
{
DataTable schemaTable = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[0]);
DataColumn tableTypeColumn = schemaTable.Columns["TABLE_TYPE"];
DataColumn tableNameColumn = schemaTable.Columns["TABLE_NAME"];
foreach (DataRow schemaRowTable in schemaTable.Rows)
{
if (string.Compare(schemaRowTable[tableTypeColumn].ToString(), "TABLE") == 0)
{
string tableName = schemaRowTable[tableNameColumn].ToString();
Table table = new Table(tableName);
DataTable schemaColumns = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName });
DataColumn ordinalPosition = schemaColumns.Columns["ORDINAL_POSITION"];
DataColumn dataType = schemaColumns.Columns["DATA_TYPE"];
DataColumn columnNameColumn = schemaColumns.Columns["COLUMN_NAME"];
DataColumn numericPrecisionColumn = schemaColumns.Columns["NUMERIC_PRECISION"];
DataColumn allowDBNull = schemaColumns.Columns["IS_NULLABLE"];
DataColumn columnSize = schemaColumns.Columns["CHARACTER_MAXIMUM_LENGTH"];
DataColumn numericScale = schemaColumns.Columns["NUMERIC_SCALE"];
DataColumn descriptionColumn = schemaColumns.Columns["DESCRIPTION"];
schemaColumns.DefaultView.Sort = ordinalPosition.ColumnName;
foreach (DataRowView schemaRowColumn in schemaColumns.DefaultView)
{
string columnName = schemaRowColumn[columnNameColumn.Ordinal].ToString();
Column column = new Column(columnName);
column.IsNullable = (bool)schemaRowColumn[allowDBNull.Ordinal];
OleDbType oleDbType = (OleDbType)schemaRowColumn[dataType.Ordinal];
column.OriginalSQLType = oleDbType.ToString();
if (_sqlTypesDictionary.Contains(oleDbType))
{
column.SqlType = (SqlType)_sqlTypesDictionary[oleDbType];
}
else
{
column.SqlType = SqlType.Unknown;
}
if (_netDataTypes.ContainsKey(column.SqlType))
{
column.NetDataType = (string)_netDataTypes[column.SqlType];
column.ActionScriptType = TypeMapper.GetActionScriptType(_netDataTypes[column.SqlType] as string);
}
else
{
column.NetDataType = "unknown";
}
if (schemaRowColumn[numericPrecisionColumn.Ordinal] != DBNull.Value)
{
column.Length = Convert.ToInt32(schemaRowColumn[numericPrecisionColumn.Ordinal]);
}
if ((column.SqlType == SqlType.VarChar) || (column.SqlType == SqlType.VarBinary) || (column.SqlType == SqlType.Binary))
{
column.Length = Convert.ToInt32(schemaRowColumn[columnSize.Ordinal]);
if (column.SqlType == SqlType.VarChar)
{
column.SqlType = SqlType.Text;
//column.Length = 0;
}
else
{
column.IsBlob = true;
}
}
else if (column.SqlType == SqlType.Decimal)
{
column.Length = Convert.ToInt32(schemaRowColumn[numericPrecisionColumn.Ordinal]);
column.Scale = Convert.ToInt32(schemaRowColumn[numericScale.Ordinal]);
}
if (schemaRowColumn[descriptionColumn.Ordinal] != DBNull.Value)
{
string description = schemaRowColumn[descriptionColumn.Ordinal] as string;
if (description == "AutoNumber")
{
column.IsIdentity = true;
}
}
table.AddColumn(column);
}
DataTable schemaPrimaryKeys = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object [] { null, null, tableName });
DataColumn pkColumnNameColumn = schemaColumns.Columns["COLUMN_NAME"];
foreach (DataRowView schemaRowPK in schemaPrimaryKeys.DefaultView)
{
string columnName = (string)schemaRowPK[pkColumnNameColumn.Ordinal];
Column column = table[columnName];
if (column != null)
{
column.IsPrimaryKey = true;
}
}
domain.AddTable(table);
}
}
foreach (DataRow schemaRowTable in schemaTable.Rows)
{
if (string.Compare(schemaRowTable[tableTypeColumn].ToString(), "TABLE") == 0)
{
string tableName = schemaRowTable[tableNameColumn].ToString();
DataTable schemaForeignKeys = ((OleDbConnection)connection).GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[0]);
DataColumn constraintName = schemaForeignKeys.Columns["FK_NAME"];
DataColumn columnOrdinal = schemaForeignKeys.Columns["ORDINAL"];
DataColumn childTableName = schemaForeignKeys.Columns["FK_TABLE_NAME"];
DataColumn parentColumnName = schemaForeignKeys.Columns["FK_COLUMN_NAME"];
DataColumn updateRule = schemaForeignKeys.Columns["UPDATE_RULE"];
DataColumn deleteRule = schemaForeignKeys.Columns["DELETE_RULE"];
DataColumn parentTableName = schemaForeignKeys.Columns["PK_TABLE_NAME"];
DataColumn childColumnName = schemaForeignKeys.Columns["PK_COLUMN_NAME"];
schemaForeignKeys.DefaultView.Sort = constraintName + "," + columnOrdinal.ColumnName;
schemaForeignKeys.DefaultView.RowFilter = childTableName.ColumnName + " = '" + tableName + "'";
foreach (DataRowView schemaRowFK in schemaForeignKeys.DefaultView)
{
string parentTable = schemaRowFK[parentTableName.Ordinal].ToString();
string primaryKeyColumnName = schemaRowFK[childColumnName.Ordinal].ToString();
}
}
}
}
return(domain);
}
public override DataDomain BuildDomain()
{
DataDomain domain = new DataDomain(this.Driver.DomainUrl);
using(MySqlConnection connection = this.Driver.OpenConnection() as MySqlConnection )
{
string commandTextTables = string.Format("SELECT Table_Name FROM information_schema.TABLES WHERE Table_Schema=\"{0}\" AND Table_Type=\"Base Table\"", this.Driver.DomainUrl.Database);
MySqlCommand command = new MySqlCommand(commandTextTables, connection);
using(IDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
string tableName = reader.GetString(0);
Table table = new Table(tableName);
domain.AddTable(table);
}
}
string commandTextColumns = string.Format("SELECT table_catalog,table_schema,table_name,column_name,is_nullable,data_type,extra,column_type,column_key,character_maximum_length,numeric_precision,numeric_scale FROM information_schema.COLUMNS WHERE table_schema=\"{0}\"", this.Driver.DomainUrl.Database);
command = new MySqlCommand(commandTextColumns, connection);
using (IDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
string tableName = reader.GetString(2);//"TABLE_NAME"
Table table = domain[tableName];
if( table != null )
{
Column column = new Column();
column.Name = reader.GetString(3);//"COLUMN_NAME"
string dataType = reader.GetString(5);//"DATA_TYPE"
MySqlDbType mySqlDbType = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), dataType);
column.OriginalSQLType = mySqlDbType.ToString();
if(_sqlTypesDictionary.Contains(dataType))
column.SqlType = (SqlType)_sqlTypesDictionary[dataType];
else
column.SqlType = SqlType.Unknown;
if (_netDataTypes.ContainsKey(column.SqlType))
{
column.NetDataType = (string)_netDataTypes[column.SqlType];
column.ActionScriptType = TypeMapper.GetActionScriptType(_netDataTypes[column.SqlType] as string);
}
else
column.NetDataType = "unknown";
if ((column.SqlType == SqlType.Char) ||
(column.SqlType == SqlType.AnsiChar) ||
(column.SqlType == SqlType.VarChar) ||
(column.SqlType == SqlType.AnsiVarChar) ||
(column.SqlType == SqlType.Text) ||
(column.SqlType == SqlType.Binary) ||
(column.SqlType == SqlType.VarBinary))
{
column.Length = reader.GetInt32(9);//"CHARACTER_MAXIMUM_LENGTH";
}
else if (column.SqlType == SqlType.Decimal)
{
column.Precision = reader.GetByte(10);//numeric_precision
column.Scale = reader.GetInt32(11);//"NUMERIC_SCALE"
}
if ((column.SqlType == SqlType.Binary) ||
(column.SqlType == SqlType.VarBinary))
{
column.IsBlob = true;
}
if (column.Length == -1)
{
switch (column.SqlType)
{
case SqlType.VarChar:
column.SqlType = SqlType.VarCharMax;
column.Length = 0;
break;
case SqlType.AnsiVarChar:
column.SqlType = SqlType.AnsiVarCharMax;
column.Length = 0;
break;
case SqlType.VarBinary:
column.SqlType = SqlType.VarBinaryMax;
column.Length = 0;
break;
default:
break;
}
}
if( !reader.IsDBNull(4) )//IS_NULLABLE
{
string tmp = reader.GetString(4);
column.IsNullable = ("yes" == tmp.ToLower());
}
if( !reader.IsDBNull(6) )//"extra"
{
string tmp = reader.GetString(6);
column.IsIdentity = ("auto_increment" == tmp.ToLower());
}
table.AddColumn(column);
}
}
}
string commandTextKeys = string.Format("SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA and kcu.TABLE_NAME = tc.TABLE_NAME WHERE ((tc.CONSTRAINT_TYPE = 'PRIMARY KEY') and (kcu.TABLE_SCHEMA =\"{0}\" )) order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION", this.Driver.DomainUrl.Database);
command = new MySqlCommand(commandTextKeys, connection);
using (IDataReader reader = command.ExecuteReader())
{
while(reader.Read())
{
string tableName = reader.GetString(1);//"TABLE_NAME"
Table table = domain[tableName];
if( table != null )
{
string columnName = reader.GetString(4);//"COLUMN_NAME"
Column column = table[columnName];
if( column != null )
column.IsPrimaryKey = true;
}
}
}
}
return domain;
}
