private static ForeignKeyInfo GetForeignKey(SqlConnection con, string senderTable, string sentColumn) { var cmdForeignKeyReferences = new SqlCommand { CommandText = MyQueries.GetForeignKeyQuery(senderTable, sentColumn), CommandType = CommandType.Text, Connection = con }; var drForeignKeyReferences = cmdForeignKeyReferences.ExecuteReader(); ForeignKeyInfo foreignkey = null; if (drForeignKeyReferences.Read()) { foreignkey = new ForeignKeyInfo { FkName = drForeignKeyReferences[0].ToString(), TableName = drForeignKeyReferences[1].ToString(), ColumnName = drForeignKeyReferences[2].ToString(), ReferencedTable = drForeignKeyReferences[3].ToString(), ReferencedColumn = drForeignKeyReferences[4].ToString() }; } drForeignKeyReferences.Close(); return(foreignkey); }
public static Dictionary <string, List <ColumnInformation> > DatabaseSchemaCustomizationGetRequest(string connectionString) { var entities = new Dictionary <string, List <ColumnInformation> >(); var tableNames = new List <string>(); var columns = new List <ColumnInformation>(); using (var con = new SqlConnection(connectionString)) { con.Open(); var cmd = new SqlCommand(MyQueries.AllTenantTablesListQuery, con); var dr = cmd.ExecuteReader(); while (dr.Read()) { tableNames.Add(dr[0].ToString()); } dr.Close(); foreach (var tableName in tableNames) { columns.Clear(); SqlCommand cmdTable = new SqlCommand { Connection = con, CommandText = MyQueries.GetTableGeneralSelectQuery(tableName) }; var da = new SqlDataAdapter(cmdTable); var dt = new DataTable(tableName); da.Fill(dt); columns.AddRange(from DataColumn column in dt.Columns select new ColumnInformation { Name = column.ColumnName, DataTypeName = column.DataType.Name, AllowDbNull = column.AllowDBNull.ToString() }); entities.Add(tableName, new List <ColumnInformation>(columns)); } } return(entities); }
private static string CheckManyToOne(string receivedTable, string senderTable, string connectionString, out ForeignKeyInfo foreignKey) { using (var con = new SqlConnection(connectionString)) { con.Open(); var cmdForeignKeyReferences = new SqlCommand { CommandText = MyQueries.GetForeignKeyQueryBetweenTablesQuery(senderTable, receivedTable), CommandType = CommandType.Text, Connection = con }; var drForeignKeyReferences = cmdForeignKeyReferences.ExecuteReader(); ForeignKeyInfo foreignkey = null; if (drForeignKeyReferences.Read()) { foreignkey = new ForeignKeyInfo { FkName = drForeignKeyReferences[0].ToString(), TableName = drForeignKeyReferences[1].ToString(), ColumnName = drForeignKeyReferences[2].ToString(), ReferencedTable = drForeignKeyReferences[3].ToString(), ReferencedColumn = drForeignKeyReferences[4].ToString() }; } drForeignKeyReferences.Close(); var primaryKey = GetPrimaryKey(con, senderTable); string result; if (foreignkey != null) { result = "manytoone"; if (foreignkey.ColumnName == primaryKey.ColumnName) { result = (foreignkey.ColumnName == primaryKey.ColumnName) ? "onetoone" : "manytoone"; } } else { result = null; } foreignKey = foreignkey; return(result); } }
private static List <string> GetColumnList(SqlConnection con, string tableName) { var colList = new List <string>(); var cmd = new SqlCommand { Connection = con, CommandText = MyQueries.GetColumnsListQuery(tableName) }; SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { colList.Add(dr[0].ToString()); } dr.Close(); return(colList); }
private static string CheckOneToOne(string receivedTable, string senderTable, string connectionString, out ForeignKeyInfo oneToOneforeignkey) { using (var con = new SqlConnection(connectionString)) { con.Open(); var cmdOneToOneRelationship = new SqlCommand { CommandText = MyQueries.GetForeignKeyQueryBetweenTablesQuery(receivedTable, senderTable), CommandType = CommandType.Text, Connection = con }; var primaryKey = GetPrimaryKey(con, receivedTable); var drOneToOneRelationship = cmdOneToOneRelationship.ExecuteReader(); var oneToOneForeignKey = new ForeignKeyInfo(); if (drOneToOneRelationship.Read()) { oneToOneForeignKey.FkName = drOneToOneRelationship[0].ToString(); oneToOneForeignKey.TableName = drOneToOneRelationship[1].ToString(); oneToOneForeignKey.ColumnName = drOneToOneRelationship[2].ToString(); oneToOneForeignKey.ReferencedTable = drOneToOneRelationship[3].ToString(); oneToOneForeignKey.ReferencedColumn = drOneToOneRelationship[4].ToString(); } drOneToOneRelationship.Close(); string result; if (oneToOneForeignKey.ColumnName != null) { result = (primaryKey.ColumnName == oneToOneForeignKey.ColumnName) ? "onetoone" : "onetomany"; } else { result = ""; } oneToOneforeignkey = oneToOneForeignKey; return(result); } }
private static PrimaryKeyInfo GetPrimaryKey(SqlConnection con, string tableName) { var cmdPrimaryKeys = new SqlCommand { CommandText = MyQueries.GetPrimaryKeyQuery(tableName), CommandType = CommandType.Text, Connection = con }; var drPrimaryKeys = cmdPrimaryKeys.ExecuteReader(); PrimaryKeyInfo primaryKey = new PrimaryKeyInfo(); if (drPrimaryKeys.Read()) { primaryKey = new PrimaryKeyInfo { PkName = drPrimaryKeys[0].ToString(), TableName = drPrimaryKeys[1].ToString(), ColumnName = drPrimaryKeys[2].ToString() }; } drPrimaryKeys.Close(); return(primaryKey); }
private static bool CheckManyToMany(string receivedTable, string senderTable, string connectionString, out string matchedTable) { var tables = new List <string>(); using (var con = new SqlConnection(connectionString)) { con.Open(); PrimaryKeyInfo receiverprimaryKeyInfo = GetPrimaryKey(con, receivedTable); PrimaryKeyInfo senderprimaryKeyInfo = GetPrimaryKey(con, senderTable); SqlCommand cmd = new SqlCommand { Connection = con, CommandText = MyQueries.ManyToManyTableCheckingQuery(senderTable, receivedTable) }; var dr = cmd.ExecuteReader(); while (dr.Read()) { tables.Add(dr[0].ToString()); } dr.Close(); var otherTableFKs = new Dictionary <string, List <ForeignKeyInfo> >(); foreach (var table in tables) { var otherTableForeignKeysCommand = new SqlCommand { Connection = con, CommandText = MyQueries.OtherTableForeignKeysQuery(table, senderTable, receivedTable, senderprimaryKeyInfo, receiverprimaryKeyInfo) }; var drotherTableForeignKeysCommand = otherTableForeignKeysCommand.ExecuteReader(); var otherTableForeignKeyInfos = new List <ForeignKeyInfo>(); while (drotherTableForeignKeysCommand.Read()) { var fkInfo = new ForeignKeyInfo { FkName = drotherTableForeignKeysCommand[0].ToString(), TableName = drotherTableForeignKeysCommand[1].ToString(), ColumnName = drotherTableForeignKeysCommand[2].ToString(), ReferencedTable = drotherTableForeignKeysCommand[3].ToString(), ReferencedColumn = drotherTableForeignKeysCommand[4].ToString() }; otherTableForeignKeyInfos.Add(fkInfo); } drotherTableForeignKeysCommand.Close(); otherTableFKs.Add(table, otherTableForeignKeyInfos); } var primaryKeyInfoForCompareSenderTable = new PrimaryKeyInfoForCompare { ColumnName = senderprimaryKeyInfo.ColumnName, TableName = senderprimaryKeyInfo.TableName }; var primaryKeyInfoForCompareReceiverTable = new PrimaryKeyInfoForCompare { ColumnName = receiverprimaryKeyInfo.ColumnName, TableName = receiverprimaryKeyInfo.TableName }; var primaryKeyInfosForCompare = new Dictionary <string, List <PrimaryKeyInfoForCompare> >(); foreach (var otherTableFk in otherTableFKs) { var listt = otherTableFk.Value.Select(foreignKey => new PrimaryKeyInfoForCompare { ColumnName = foreignKey.ReferencedColumn, TableName = foreignKey.ReferencedTable }).ToList(); primaryKeyInfosForCompare.Add(otherTableFk.Key, listt); } foreach (var primaryKeyInfoForCompare in from primaryKeyInfoForCompare in primaryKeyInfosForCompare let infosList = primaryKeyInfoForCompare.Value.ToList() where infosList.Any(p => p.ColumnName == primaryKeyInfoForCompareSenderTable.ColumnName && p.TableName == primaryKeyInfoForCompareSenderTable.TableName) where infosList.Any(s => s.ColumnName == primaryKeyInfoForCompareReceiverTable.ColumnName && s.TableName == primaryKeyInfoForCompareReceiverTable.TableName) select primaryKeyInfoForCompare) { matchedTable = primaryKeyInfoForCompare.Key; return(true); } matchedTable = null; return(false); } }