/// <summary> /// extracts information from the COLUMNS table of INFORMATION_SCHEMA /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness /// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES /// </summary> private void GetColumnTypes() { DataTable stats = driver.fetchAll("SELECT COLUMNS.* FROM COLUMNS JOIN TABLES USING(TABLE_SCHEMA, TABLE_NAME) " + "WHERE TABLE_TYPE != \"VIEW\" AND TABLES.TABLE_SCHEMA = \"" + webDb + "\" ORDER BY COLUMNS.TABLE_NAME, ORDINAL_POSITION"); Dictionary <string, DataColumnCollection> res = new Dictionary <string, DataColumnCollection>(); BaseDriverMySql tempWebDb = new BaseDriverMySql(Common.Environment.project.ConnstringWeb); tempWebDb.BeginTransaction(); tempWebDb.query("SET SQL_SELECT_LIMIT=0"); foreach (string tableName in TableList()) { DataTable schema = tempWebDb.fetchAll("SELECT * FROM ", dbe.Table(tableName)); res[tableName] = schema.Columns; } tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT"); tempWebDb.CommitTransaction(); tempWebDb = null; foreach (DataRow r in stats.Rows) { DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName string columnType = r["COLUMN_TYPE"] as string; if (columnType.StartsWith("enum")) // enum type { string vals = columnType.Substring(5, columnType.Length - 5 - 1); // the brackets string[] split = vals.Split(new char[] { ',' }); List <string> EnumValues = new List <string>(); foreach (string s in split) { EnumValues.Add(s.Substring(1, s.Length - 2)); } col.ExtendedProperties.Add(CC.ENUM_COLUMN_VALUES, EnumValues); } if (col.DataType == typeof(string)) { col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } string extra = r["EXTRA"] as string; // set AutoIncrement if (extra == "auto_increment") { col.AutoIncrement = true; } if (!col.AutoIncrement) { col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules... } object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue // only if there is something sensible has been retrieved from the INFORMATION SCHEMA and no default value has been set // in the default datatable retrieved in a SELECT if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value) { string colDefaltStr = colDefault as string; if (colDefaltStr == "CURRENT_TIMESTAMP") { col.DefaultValue = DBNull.Value; // unneccessary, just for clarity col.AllowDBNull = true; } else if (col.DataType == typeof(string)) { col.DefaultValue = colDefaltStr; } else if (col.DataType == typeof(bool)) { switch (colDefaltStr) { case "0": col.DefaultValue = false; break; case "1": col.DefaultValue = true; break; default: break; } } else { object parsed = null; if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)) { col.DefaultValue = parsed; } } } col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES"; try { if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) { col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } } catch { } } // for each row in stats columnTypes = res; ColumnsToDisplay = new Dictionary <string, List <string> >(); IComparer <DataColumn> comparer = new Common.ColumnDisplayComparer(); foreach (string tableName in columnTypes.Keys) { List <DataColumn> innerList = new List <DataColumn>(); foreach (DataColumn col in columnTypes[tableName]) { innerList.Add(col); } innerList.Sort(comparer); columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList <string>(); } foreach (string tableName in FKs.Keys) { foreach (FK fk in FKs[tableName]) { columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk; } } }
/// <summary> /// extracts information from the COLUMNS table of INFORMATION_SCHEMA /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness /// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES /// </summary> private void GetColumnTypes() { DataTable stats = driver.fetchAll("SELECT COLS.*," + " COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'" + " FROM INFORMATION_SCHEMA.COLUMNS AS COLS" + " JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " + "WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION"); Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>(); BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb); tempWebDb.BeginTransaction(); //tempWebDb.query("SET SQL_SELECT_LIMIT=0"); foreach (string tableName in TableList()) { DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName)); res[tableName] = schema.Columns; } //tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT"); tempWebDb.CommitTransaction(); tempWebDb = null; foreach (DataRow r in stats.Rows) { DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName //string dataType = r["DATA_TYPE"] as string; if (col.DataType == typeof(string)) { col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } bool hasIdentity = (int)r["HAS_IDENTITY"] == 1; // set AutoIncrement if (hasIdentity) col.AutoIncrement = true; if (!col.AutoIncrement) col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules... object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value) { string colDefaltStr = colDefault as string; // similiar to StatsMySql save for the condition on CURRENT_TIMESTAMP - this could probably be expressed in various ways fro the MSSQL. // The result will be though the same, just one additional call to refrection when trying to parse the funcion signature into a datetime. if (col.DataType == typeof(string)) { col.DefaultValue = colDefaltStr; } else if (col.DataType == typeof(bool)) { switch (colDefaltStr) { case "0": col.DefaultValue = false; break; case "1": col.DefaultValue = true; break; default: break; } } else { object parsed = null; if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)) { col.DefaultValue = parsed; } } } col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES"; try { if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } catch { } } // for each row in stats columnTypes = res; ColumnsToDisplay = new Dictionary<string, List<string>>(); IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer(); foreach (string tableName in columnTypes.Keys) { List<DataColumn> innerList = new List<DataColumn>(); foreach (DataColumn col in columnTypes[tableName]) innerList.Add(col); innerList.Sort(comparer); columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>(); } foreach (string tableName in FKs.Keys) { foreach (FK fk in FKs[tableName]) { columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk; } } }
/// <summary> /// extracts information from the COLUMNS table of INFORMATION_SCHEMA /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness /// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES /// </summary> private void GetColumnTypes() { DataTable stats = driver.fetchAll("SELECT COLS.*," + " COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'" + " FROM INFORMATION_SCHEMA.COLUMNS AS COLS" + " JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " + "WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION"); Dictionary <string, DataColumnCollection> res = new Dictionary <string, DataColumnCollection>(); BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb); tempWebDb.BeginTransaction(); //tempWebDb.query("SET SQL_SELECT_LIMIT=0"); foreach (string tableName in TableList()) { DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName)); res[tableName] = schema.Columns; } //tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT"); tempWebDb.CommitTransaction(); tempWebDb = null; foreach (DataRow r in stats.Rows) { DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName //string dataType = r["DATA_TYPE"] as string; if (col.DataType == typeof(string)) { col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } bool hasIdentity = (int)r["HAS_IDENTITY"] == 1; // set AutoIncrement if (hasIdentity) { col.AutoIncrement = true; } if (!col.AutoIncrement) { col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules... } object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value) { string colDefaltStr = colDefault as string; // similiar to StatsMySql save for the condition on CURRENT_TIMESTAMP - this could probably be expressed in various ways fro the MSSQL. // The result will be though the same, just one additional call to refrection when trying to parse the funcion signature into a datetime. if (col.DataType == typeof(string)) { col.DefaultValue = colDefaltStr; } else if (col.DataType == typeof(bool)) { switch (colDefaltStr) { case "0": col.DefaultValue = false; break; case "1": col.DefaultValue = true; break; default: break; } } else { object parsed = null; if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)) { col.DefaultValue = parsed; } } } col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES"; try { if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) { col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } } catch { } } // for each row in stats columnTypes = res; ColumnsToDisplay = new Dictionary <string, List <string> >(); IComparer <DataColumn> comparer = new Common.ColumnDisplayComparer(); foreach (string tableName in columnTypes.Keys) { List <DataColumn> innerList = new List <DataColumn>(); foreach (DataColumn col in columnTypes[tableName]) { innerList.Add(col); } innerList.Sort(comparer); columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList <string>(); } foreach (string tableName in FKs.Keys) { foreach (FK fk in FKs[tableName]) { columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk; } } }
/// <summary> /// extracts information from the COLUMNS table of INFORMATION_SCHEMA /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness /// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES /// </summary> private void GetColumnTypes() { DataTable stats = driver.fetchAll("SELECT COLUMNS.* FROM COLUMNS JOIN TABLES USING(TABLE_SCHEMA, TABLE_NAME) " + "WHERE TABLE_TYPE != \"VIEW\" AND TABLES.TABLE_SCHEMA = \"" + webDb + "\" ORDER BY COLUMNS.TABLE_NAME, ORDINAL_POSITION"); Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>(); BaseDriverMySql tempWebDb = new BaseDriverMySql(Common.Environment.project.ConnstringWeb); tempWebDb.BeginTransaction(); tempWebDb.query("SET SQL_SELECT_LIMIT=0"); foreach(string tableName in TableList()){ DataTable schema = tempWebDb.fetchAll("SELECT * FROM ", dbe.Table(tableName)); res[tableName] = schema.Columns; } tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT"); tempWebDb.CommitTransaction(); tempWebDb = null; foreach (DataRow r in stats.Rows) { DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName string columnType = r["COLUMN_TYPE"] as string; if (columnType.StartsWith("enum")) { // enum type string vals = columnType.Substring(5, columnType.Length - 5 - 1); // the brackets string[] split = vals.Split(new char[]{','}); List<string> EnumValues = new List<string>(); foreach (string s in split) { EnumValues.Add(s.Substring(1, s.Length - 2)); } col.ExtendedProperties.Add(CC.ENUM_COLUMN_VALUES, EnumValues); } if (col.DataType == typeof(string)) { col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } string extra = r["EXTRA"] as string; // set AutoIncrement if (extra == "auto_increment") col.AutoIncrement = true; if(!col.AutoIncrement) col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules... object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue if(!((colDefault is DBNull) || (colDefault.ToString() == String.Empty))){ string colDefaltStr = colDefault as string; if(colDefaltStr == "CURRENT_TIMESTAMP") col.ExtendedProperties.Remove(Common.Constants.COLUMN_EDITABLE); //else{ //object parsed; //if(Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)){ // col.DefaultValue = parsed; //} //} } col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES"; if(!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } // for each row in stats columnTypes = res; ColumnsToDisplay = new Dictionary<string, List<string>>(); IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer(); foreach (string tableName in columnTypes.Keys) { List<DataColumn> innerList = new List<DataColumn>(); foreach (DataColumn col in columnTypes[tableName]) innerList.Add(col); innerList.Sort(comparer); columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>(); } foreach (string tableName in FKs.Keys) { foreach (FK fk in FKs[tableName]) { columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk; } } }
/// <summary> /// extracts information from the COLUMNS table of INFORMATION_SCHEMA /// sets each column`s datatype, default value, nullability, autoincrement, uniqueness /// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES /// </summary> private void GetColumnTypes() { DataTable stats = driver.fetchAll("SELECT COLS.*," +" COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'" +" FROM INFORMATION_SCHEMA.COLUMNS AS COLS" +" JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " + "WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION"); Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>(); BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb); tempWebDb.BeginTransaction(); //tempWebDb.query("SET SQL_SELECT_LIMIT=0"); foreach(string tableName in TableList()){ DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName)); res[tableName] = schema.Columns; } //tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT"); tempWebDb.CommitTransaction(); tempWebDb = null; foreach (DataRow r in stats.Rows) { DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName //string dataType = r["DATA_TYPE"] as string; if (col.DataType == typeof(string)) { col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } bool hasIdentity = (int)r["HAS_IDENTITY"] == 1; // set AutoIncrement if (hasIdentity) col.AutoIncrement = true; if(!col.AutoIncrement) col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules... object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue //if(!((colDefault is DBNull) || (colDefault.ToString() == String.Empty))){ //string colDefaltStr = colDefault as string; //if(colDefaltStr == "CURRENT_TIMESTAMP") // col.ExtendedProperties.Remove(Common.Constants.COLUMN_EDITABLE); //else{ //object parsed; //if(Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)){ // col.DefaultValue = parsed; //} //} //} col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES"; if(!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]); } // for each row in stats columnTypes = res; ColumnsToDisplay = new Dictionary<string, List<string>>(); IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer(); foreach (string tableName in columnTypes.Keys) { List<DataColumn> innerList = new List<DataColumn>(); foreach (DataColumn col in columnTypes[tableName]) innerList.Add(col); innerList.Sort(comparer); columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>(); } foreach (string tableName in FKs.Keys) { foreach (FK fk in FKs[tableName]) { columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk; } } }