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