//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); } } } } }