/// <summary>
/// Filter by database schema
/// </summary>
/// <param name="db"></param>
/// <param name="arg">'+' - include schema, '-' - exclude schema</param>
/// <param name="schemeArray">array of schemas</param>
/// <returns></returns>
public static DbMsSqlMetadata FilterByScheme(this DbMsSqlMetadata db, string arg, params string[] schemeArray)
{
if (string.Equals(arg, "+"))
{
for (int i = 0; i < db.TableList.Count; i++)
{
MTable t = db.TableList[i];
if (!schemeArray.Contains(t.Table_schema))
{
db.TableList.RemoveAt(i);
i--;
}
} // for
}
if (string.Equals(arg, "-"))
{
for (int i = 0; i < db.TableList.Count; i++)
{
MTable t = db.TableList[i];
if (schemeArray.Contains(t.Table_schema))
{
db.TableList.RemoveAt(i);
i--;
}
} // for
}
return(db);
}
/// <summary>
/// Set primary key. Some time it need for VIEW.
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">table or view 'app.AppUser'</param>
/// <param name="columnName"></param>
/// <returns></returns>
public static DbMsSqlMetadata SetPk(this DbMsSqlMetadata db, string tableName, string columnName)
{
var t = db.TableList.FirstOrDefault(c => $"{c.Table_schema}.{c.Table_name}" == tableName);
if (t == null)
{
throw new Exception($"Table {tableName} not found");
}
var col = t.ColumnList.FirstOrDefault(c => c.column_name == columnName);
if (t == null)
{
throw new Exception($"Column {tableName}.{columnName} not found");
}
foreach (var c in t.ColumnList)
{
c.IsPrimaryKey = string.Equals(c.column_name, columnName);
}
//col.IsPrimaryKey = true;
return(db);
}
/// <summary>
/// Filter by table, with output funcfion (for debug info)
/// </summary>
/// <param name="db"></param>
/// <param name="arg">'+' - include table, '-' - exclude table</param>
/// <param name="tableArray">array of tables 'schema.table' ('app.AppUser')</param>
/// <returns></returns>
public static DbMsSqlMetadata FilterByTable(this DbMsSqlMetadata db, string arg, Action <string> outputWriteFunc, params string[] tableArray)
{
if (string.Equals(arg, "+"))
{
for (int i = 0; i < db.TableList.Count; i++)
{
MTable t = db.TableList[i];
var tName = $"{t.Table_schema}.{t.Table_name}";
if (!tableArray.Contains(tName))
{
// debuf info
outputWriteFunc?.Invoke($"// - remove table {tName}");
db.TableList.RemoveAt(i);
i--;
}
} // for
}
if (string.Equals(arg, "-"))
{
for (int i = 0; i < db.TableList.Count; i++)
{
MTable t = db.TableList[i];
var tName = $"{t.Table_schema}.{t.Table_name}";
if (tableArray.Contains(tName))
{
db.TableList.RemoveAt(i);
i--;
}
} // for
}
return(db);
}
/// <summary>
/// Filter by table
/// </summary>
/// <param name="db"></param>
/// <param name="arg">'+' - include table, '-' - exclude table</param>
/// <param name="tableArray">array of tables 'schema.table' ('app.AppUser')</param>
/// <returns></returns>
public static DbMsSqlMetadata FilterByTable(this DbMsSqlMetadata db, string arg, params string[] tableArray)
{
if (string.Equals(arg, "+"))
{
for (int i = 0; i < db.TableList.Count; i++)
{
MTable t = db.TableList[i];
var tName = $"{t.Table_schema}.{t.Table_name}";
if (!tableArray.Contains(tName))
{
db.TableList.RemoveAt(i);
i--;
}
} // for
}
if (string.Equals(arg, "-"))
{
for (int i = 0; i < db.TableList.Count; i++)
{
MTable t = db.TableList[i];
var tName = $"{t.Table_schema}.{t.Table_name}";
if (tableArray.Contains(tName))
{
db.TableList.RemoveAt(i);
i--;
}
} // for
}
return(db);
}
/// <summary>
/// <example> var db1 = DbMetadata.Load().FilterByScheme("+", "nav", "usr").FilterByTable("+","nav.Pass","usr.Watch"); </example>
/// </summary>
/// <returns></returns>
public static DbMsSqlMetadata Load(string connStr, double minTimeOut = 1)
{
var result = new DbMsSqlMetadata();
lock (_lck)
{
var sw = Stopwatch.StartNew();
if (_tableList.Any() && (DateTime.Now - _tableListCreateTime).TotalSeconds < minTimeOut * 60)
{
CopyTableList(ref _tableList, ref result.TableList);
return(result);
}
using (var conn = new SqlConnection(connStr))
{
conn.Open();
// tables
var tables = conn.GetSchema("Tables", new string[] { null, null, null, null });
var tableList = (from DataRow table in tables.Rows select new MTable(table)).ToList();
// if(tables.Rows.Count != tableList.Count)
// {
// var s = 1;
// }
result.Tables = tables;
// columns
var columns = conn.GetSchema("Columns", new string[] { null, null, null });
var columnList = (from DataRow column in columns.Rows select new MColumn(column)).ToList();
// indexes
var idxColumns = conn.GetSchema("IndexColumns", new string[] { null, null, null });
var idxList = (from DataRow index in idxColumns.Rows select new MIndex(index)).ToList();
// constraints
var foreignKeys = conn.GetSchema("ForeignKeys", new string[] { null, null, null, null });
var fkList = (from DataRow fKey in foreignKeys.Rows select new MForeignKey(fKey)).ToList();
// column descriptions
var commentList = GetColumnComments(conn);
// set comments into columns
foreach (var cm in commentList)
{
var col = columnList.FirstOrDefault(c => c.TableKey == cm.TableKey && c.column_name == cm.column_name);
if (col != null)
{
col.Comment = cm.comment;
}
}
var lck = new object();
Action <MTable> funcTable = (t) =>
{
try
{
lock (lck)
{
result.TableList.Add(t);
}
t.ColumnList.AddRange(columnList.Where(c => c.TableKey == t.TableKey));
t.IdxList.AddRange(idxList.Where(c => c.TableKey == t.TableKey));
t.FkList.AddRange(fkList.Where(c => c.TableKey == t.TableKey));
var pkCol = t.ColumnList.FirstOrDefault(c => c.ordinal_position == 1);
if (pkCol != null)
{
pkCol.IsPrimaryKey = true;
}
}
catch (Exception ex)
{
Trace.TraceInformation(ex.Message);
}
};
Parallel.ForEach(tableList, funcTable);
}// using
result.TableList = result.TableList.OrderBy(c => c.TableKey).ToList();
CopyTableList(ref result.TableList, ref _tableList);
_tableListCreateTime = DateTime.Now;
sw.Stop();
}
return(result);
}