//TODO: Support more column metadata?
//private bool isNullable;
//private int charMaxLength;
//private int numericPrecision;
//private int datetimePrecision;
//private DatabaseCharset charSet;
public DBColumn(DBTable table, string name, bool isPrimaryKey, DBDatatype datatype)
{
this.table = table;
this.name = name;
this.isPrimaryKey = isPrimaryKey;
this.dataType = datatype;
}
private void setColumns(List <DBTable> tables, Database database)
{
string schemaCol = "SCHEMA";
string tableCol = "TABLE";
string columnCol = "COLUMN";
string pkCol = "PRIMARY_KEY";
string dataTypeCol = "DATA_TYPE";
string command = "SELECT C.TABLE_SCHEMA AS [{0}], C.TABLE_NAME AS [{1}], C.COLUMN_NAME AS [{2}], CASE WHEN PK.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS [{3}], C.DATA_TYPE AS {4} FROM {5}.INFORMATION_SCHEMA.COLUMNS C LEFT JOIN (SELECT TC.CONSTRAINT_CATALOG, TC.CONSTRAINT_SCHEMA, TC.TABLE_NAME, KCU.COLUMN_NAME FROM {5}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN {5}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') PK ON C.TABLE_CATALOG = PK.CONSTRAINT_CATALOG AND C.TABLE_SCHEMA = PK.CONSTRAINT_SCHEMA AND C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME";
List <DBColumn> columns = new List <DBColumn>();
using (SqlConnection sqlConnection = createSqlConnection())
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = String.Format(command, schemaCol, tableCol, columnCol, pkCol, dataTypeCol, database.Name);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string schema = (string)reader[schemaCol];
string tableName = (string)reader[tableCol];
string columnName = (string)reader[columnCol];
int pk = (int)reader[pkCol];
bool isPk = false;
if ((int)reader[pkCol] == 1)
{
isPk = true;
}
DBDatatype dataType = stringToDatatype((string)reader[dataTypeCol]);
DBTable table = tables.Where <DBTable>(t => t.Name == tableName && t.Schema == schema).FirstOrDefault();
if (table != null)
{
DBColumn column = new DBColumn(table, columnName, isPk, dataType);
table.addColumn(column);
}
}
}
}
}
//TODO: Support more column metadata?
//private bool isNullable;
//private int charMaxLength;
//private int numericPrecision;
//private int datetimePrecision;
//private DatabaseCharset charSet;
public DBColumn(DBTable table, string name, bool isPrimaryKey, DBDatatype datatype)
{
this.table = table;
this.name = name;
this.isPrimaryKey = isPrimaryKey;
this.dataType = datatype;
}