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