/// <summary> /// Gets information about primary keys /// </summary> /// <param name="connectionString"> /// Database connection string /// </param> /// <param name="timeoutSecs"> /// Command timeout /// </param> /// <param name="useCache"> /// Whether to use a cached version /// </param> /// <returns>Primary key info</returns> public static Dictionary <string, PrimaryKeyInfo> GetPrimaryKeyInfo(string connectionString, int timeoutSecs, bool useCache = true) { var results = new Dictionary <string, PrimaryKeyInfo>(StringComparer.OrdinalIgnoreCase); var dbKey = CreateDbNameKey(connectionString); var key = string.Concat(dbKey, ".PrimaryKeyInfo"); var o = useCache ? _memoryCache.Get(key) : null; if (o == null) { var sb = new SqlBuilder(); sb.Append("select tc.table_name, c.column_name, c.ordinal_position"); sb.Append("from INFORMATION_SCHEMA.table_constraints tc"); sb.Append("inner join INFORMATION_SCHEMA.key_column_usage col"); sb.Append("on col.constraint_name = tc.constraint_name"); sb.Append("and col.constraint_schema = tc.constraint_schema"); sb.Append("inner join INFORMATION_SCHEMA.COLUMNS c"); sb.Append("on c.column_name = col.column_name"); sb.Append("and c.table_name = tc.table_name"); sb.Append("where tc.constraint_type = 'Primary Key'"); sb.Append("order by table_name, ordinal_position"); EnumerateResults(connectionString, sb.ToString(), timeoutSecs, r => { var tableName = (string)r["table_name"]; var colName = (string)r["column_name"]; var colPosition = (int)r["ordinal_position"]; if (!results.TryGetValue(tableName, out var keys)) { keys = new PrimaryKeyInfo { TableName = tableName, Columns = new List <ColumnNameAndPosition>() }; results.Add(tableName, keys); } keys.Columns.Add(new ColumnNameAndPosition { ColumnName = colName, Position = colPosition }); }); if (useCache) { _memoryCache.Set(key, results, DateTimeOffset.UtcNow.AddMinutes(CacheLifetimeMinutes)); } } else { _log.Debug("Using cached primary key info"); results = (Dictionary <string, PrimaryKeyInfo>)o; } return(results); }
public static bool Identical(PrimaryKeyInfo pkInfo1, PrimaryKeyInfo pkInfo2) { if (!pkInfo1.TableName.Equals(pkInfo2.TableName, StringComparison.OrdinalIgnoreCase)) { return(false); } if (pkInfo1.Columns.Count != pkInfo2.Columns.Count) { return(false); } for (var n = 0; n < pkInfo1.Columns.Count; ++n) { if (!ColumnNameAndPosition.Identical(pkInfo1.Columns[n], pkInfo2.Columns[n])) { return(false); } } return(true); }