private DatabaseXML loadDBFromConnectionString(string projectName, string connectionString, string databaseName) { SQL.HelperSQL.ConnectionString = connectionString; SqlConnection connection = new SqlConnection(connectionString); connection.Open(); DataTable dt = connection.GetSchema("Columns"); StringWriter stringWriter = new StringWriter(); dt.WriteXml(stringWriter); string xml = stringWriter.ToString(); XElement element = XElement.Parse(xml); DatabaseXML db = new DatabaseXML { Name = projectName, ConnectionString = connectionString }; string getColumnScript = @" SELECT [type], TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, NUMERIC_SCALE FROM sysobjects as so right join INFORMATION_SCHEMA.COLUMNS on so.name = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME WHERE ([type] = 'U' OR [type] = 'V') AND category = 0"; List <Dictionary <string, string> > columnList = SQL.HelperSQL.query(getColumnScript); foreach (Dictionary <string, string> columnDic in columnList) { string isNull = columnDic["IS_NULLABLE"]; string tableName = columnDic["TABLE_NAME"]; string columnName = columnDic["COLUMN_NAME"]; string Type = columnDic["DATA_TYPE"]; string NUMERIC_PRECISION = columnDic["NUMERIC_PRECISION"]; string CHARACTER_MAXIMUM_LENGTH = columnDic["CHARACTER_MAXIMUM_LENGTH"]; string NUMERIC_SCALE = columnDic["NUMERIC_SCALE"]; string tableType = columnDic["type"]; if (tableType.Contains("U")) { tableType = "Normal"; } if (tableType.Contains("V")) { tableType = "Vue"; } int Length = 0; int DecimalLength = 0; if (Type == "varchar" || Type == "nvarchar") { Length = int.Parse(CHARACTER_MAXIMUM_LENGTH); } if (Type == "decimal") { Length = int.Parse(NUMERIC_PRECISION); DecimalLength = int.Parse(NUMERIC_SCALE); } Table table = db.getTableByName(tableName); if (table == null) { table = new Table(); table.Name = tableName; table.Type = tableType; db.Content.Add(table); } Column column = new Column { Name = columnName, Length = Length, Type = Database.TypeString.toType(Type), DecimalLength = DecimalLength }; if (isNull == "NO") { column.isNull = 0; } else { column.isNull = 1; } db.getTableByName(tableName).Columns.Add(column); } string primaryKeys = @"SELECT TableName = i1.TABLE_NAME, ColumnName = i2.COLUMN_NAME, Position = i2.ORDINAL_POSITION, PrimaryKeyName = i1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'"; string foreignKeys = @"SELECT TableName = FK.TABLE_NAME, ColumnName = CU.COLUMN_NAME, ExternTableName = PK.TABLE_NAME, ExternColumnName = PT.COLUMN_NAME, ForeignKeyName = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME"; string identities = @"select ColumnName = COLUMN_NAME, TableName = TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 order by TableName"; string indexes = @"SELECT TableName = t.name, IndexName = ind.name, IndexId = ind.index_id, ColumnId = ic.index_column_id, ColumnName = col.name FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id "; string uniqueKeys = @"SELECT TableName = i1.TABLE_NAME, ColumnName = i2.COLUMN_NAME, Position = i2.ORDINAL_POSITION, UniqueKeyName = i1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'UNIQUE'"; List <Dictionary <string, string> > primaryKeysList = SQL.HelperSQL.query(primaryKeys); List <Dictionary <string, string> > foreignKeysList = SQL.HelperSQL.query(foreignKeys); List <Dictionary <string, string> > identitiesList = SQL.HelperSQL.query(identities); //List<Dictionary<string, string>> indexesList = SQL.HelperSQL.query(indexes); List <Dictionary <string, string> > uniqueKeysList = SQL.HelperSQL.query(uniqueKeys); // Dictionary <string, List <Dictionary <string, string> > > primaryKeyDic = listToDic(primaryKeysList); Dictionary <string, List <Dictionary <string, string> > > foreignKeysDic = listToDic(foreignKeysList); Dictionary <string, List <Dictionary <string, string> > > identitiesDic = listToDic(identitiesList); //Dictionary<string, List<Dictionary<string, string>>> indexesDic = listToDic(indexesList); Dictionary <string, List <Dictionary <string, string> > > UniqueKeysDic = listToDic(uniqueKeysList); foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in primaryKeyDic) { string tableName = kvp.Key; // Rempli pour mettre les index dans les bonnes positions for (int i = 0; i < kvp.Value.Count; i++) { db.getTableByName(tableName).PrimaryKey.Add("youpi"); } foreach (Dictionary <string, string> dic in kvp.Value) { int index = Int32.Parse(dic["Position"]) - 1; db.getTableByName(tableName).PrimaryKey[index] = dic["ColumnName"]; } } foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in foreignKeysDic) { string tableName = kvp.Key; foreach (Dictionary <string, string> dic in kvp.Value) { string fkName = dic["ForeignKeyName"]; ForeignKey fk = db.getTableByName(tableName).getForeignKeyByName(fkName); ForeignKeyInside fki = new ForeignKeyInside { ColumnName = dic["ColumnName"], ExternColumnName = dic["ExternColumnName"] }; if (fk == null) { db.getTableByName(tableName).addForeignKey(new ForeignKey { ExternTableName = dic["ExternTableName"], Name = fkName, Info = new List <ForeignKeyInside> { fki } }); } else { db.getTableByName(tableName).getForeignKeyByName(fkName).Info.Add(fki); } } } /*foreach (KeyValuePair<string, List<Dictionary<string, string>>> kvp in indexesDic) * { * string tableName = kvp.Key; * foreach (Dictionary<string, string> dic in kvp.Value) * { * string indexName = dic["IndexName"]; * Index index = db.getTableByName(tableName).getIndexByName(indexName); * string ColumnName = dic["ColumnName"]; * * if (index == null) * { * db.getTableByName(tableName).Indexes.Add(new Index * { * Name = indexName, * List = new List<string> * { * ColumnName * } * }); * } * else * { * db.getTableByName(tableName).getIndexByName(indexName).List.Add(ColumnName); * } * } * foreach (Dictionary<string, string> dic in kvp.Value) * { * string indexName = dic["IndexName"]; * string ColumnName = dic["ColumnName"]; * int i = Int32.Parse(dic["ColumnId"]) - 1; * db.getTableByName(tableName).getIndexByName(indexName).List[i] = ColumnName; * } * }*/ foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in UniqueKeysDic) { string tableName = kvp.Key; foreach (Dictionary <string, string> dic in kvp.Value) { string ukName = dic["UniqueKeyName"]; UniqueKey uk = db.getTableByName(tableName).getUniqueKeyByName(ukName); string ColumnName = dic["ColumnName"]; if (uk == null) { db.getTableByName(tableName).UniqueKeys.Add(new UniqueKey { Name = ukName, List = new List <string> { ColumnName } }); } else { db.getTableByName(tableName).getUniqueKeyByName(ukName).List.Add(ColumnName); } } foreach (Dictionary <string, string> dic in kvp.Value) { string ukName = dic["UniqueKeyName"]; string ColumnName = dic["ColumnName"]; int i = Int32.Parse(dic["Position"]) - 1; db.getTableByName(tableName).getUniqueKeyByName(ukName).List[i] = ColumnName; } } foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in identitiesDic) { string tableName = kvp.Key; foreach (Dictionary <string, string> dic in kvp.Value) { string columnName = dic["ColumnName"]; db.getTableByName(tableName).getColumnByName(columnName).Type = Database.Type.Int_AI; } } return(db); }
// // GET: /SQLManagement/Home/ public ActionResult Index() { string connectionString = ConfigurationManager.ConnectionStrings["mssqlConnection"].ConnectionString; SqlConnection connection = new SqlConnection(connectionString); connection.Open(); DataTable dt = connection.GetSchema("Columns"); StringWriter stringWriter = new StringWriter(); dt.WriteXml(stringWriter); string xml = stringWriter.ToString(); XElement element = XElement.Parse(xml); DatabaseXML db = new DatabaseXML(); foreach (XElement elem1 in element.Elements()) { string isNull = elem1.Element("IS_NULLABLE").Value; string tableName = elem1.Element("TABLE_NAME").Value; string columnName = elem1.Element("COLUMN_NAME").Value; string Type = elem1.Element("DATA_TYPE").Value; int Length = 0; if (Type == "int") { Length = Int32.Parse(elem1.Element("NUMERIC_PRECISION").Value); } else if (Type == "varchar") { Length = Int32.Parse(elem1.Element("CHARACTER_MAXIMUM_LENGTH").Value); } Table table = db.getTableByName(tableName); if (table == null) { table = new Table(); table.Name = tableName; db.Content.Add(table); } Column column = new Column { Name = columnName, Length = Length, Type = Database.TypeString.toType(Type) }; if (isNull == "NO") { column.isNull = 0; } else { column.isNull = 1; } db.getTableByName(tableName).Columns.Add(column); if (columnName == "col7") { Console.WriteLine("youpi"); } } string primaryKeys = @"SELECT TableName = i1.TABLE_NAME, ColumnName = i2.COLUMN_NAME, Position = i2.ORDINAL_POSITION, PrimaryKeyName = i1.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'"; string foreignKeys = @"SELECT TableName = FK.TABLE_NAME, ColumnName = CU.COLUMN_NAME, ExternTableName = PK.TABLE_NAME, ExternColumnName = PT.COLUMN_NAME, ForeignKeyName = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME"; string identities = @"select ColumnName = COLUMN_NAME, TableName = TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 order by TableName"; string indexes = @"SELECT TableName = t.name, IndexName = ind.name, IndexId = ind.index_id, ColumnId = ic.index_column_id, ColumnName = col.name FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id "; List <Dictionary <string, string> > primaryKeysList = query(primaryKeys); List <Dictionary <string, string> > foreignKeysList = query(foreignKeys); List <Dictionary <string, string> > identitiesList = query(identities); List <Dictionary <string, string> > indexesList = query(indexes); Dictionary <string, List <Dictionary <string, string> > > primaryKeyDic = getDic(primaryKeysList); Dictionary <string, List <Dictionary <string, string> > > foreignKeysDic = getDic(foreignKeysList); Dictionary <string, List <Dictionary <string, string> > > identitiesDic = getDic(identitiesList); Dictionary <string, List <Dictionary <string, string> > > indexesDic = getDic(indexesList); foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in primaryKeyDic) { string tableName = kvp.Key; for (int i = 0; i < kvp.Value.Count; i++) { db.getTableByName(tableName).PrimaryKey.Add("youpi"); } foreach (Dictionary <string, string> dic in kvp.Value) { int index = Int32.Parse(dic["Position"]) - 1; db.getTableByName(tableName).PrimaryKey[index] = dic["ColumnName"]; } } foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in foreignKeysDic) { string tableName = kvp.Key; foreach (Dictionary <string, string> dic in kvp.Value) { string fkName = dic["ForeignKeyName"]; ForeignKey fk = db.getTableByName(tableName).getForeignKeyByName(fkName); ForeignKeyInside fki = new ForeignKeyInside { ColumnName = dic["ColumnName"], ExternColumnName = dic["ExternColumnName"] }; if (fk == null) { db.getTableByName(tableName).addForeignKey(new ForeignKey { ExternTableName = dic["ExternTableName"], Name = fkName, Info = new List <ForeignKeyInside> { fki } }); } else { db.getTableByName(tableName).getForeignKeyByName(fkName).Info.Add(fki); } } } foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in indexesDic) { string tableName = kvp.Key; foreach (Dictionary <string, string> dic in kvp.Value) { string indexName = dic["IndexName"]; Index index = db.getTableByName(tableName).getIndexByName(indexName); string ColumnName = dic["ColumnName"]; if (index == null) { db.getTableByName(tableName).Indexes.Add(new Index { Name = indexName, List = new List <string> { ColumnName } }); } else { db.getTableByName(tableName).getIndexByName(indexName).List.Add(ColumnName); } } foreach (Dictionary <string, string> dic in kvp.Value) { string indexName = dic["IndexName"]; string ColumnName = dic["ColumnName"]; int i = Int32.Parse(dic["ColumnId"]) - 1; db.getTableByName(tableName).getIndexByName(indexName).List[i] = indexName; } } foreach (KeyValuePair <string, List <Dictionary <string, string> > > kvp in indexesDic) { string tableName = kvp.Key; foreach (Dictionary <string, string> dic in kvp.Value) { string columnName = dic["ColumnName"]; db.getTableByName(tableName).getColumnByName(columnName).Type = Database.Type.Int_AI; } } /* * TABLE_NAME * COLUMN_NAME * IS_NULLABLE * DATA_TYPE * (int) => NUMERIC_PRECISION, * (varchar) => CHARACTER_MAXIMUM_LENGTH * (float) , * (datetime) => DATETIME_PRECISION, * (text), * (decimal) => NUMERIC_PRECISION = *.NUMERIC_SCALE (5.05 => {NUMERIC_PRECISION = 3, NUMERIC_SCALE = 2}) * */ return(View()); }