public bool LoadDatabaseMetadata(string database_name, string connection_string)
{
if (string.IsNullOrEmpty(database_name))
{
throw new ArgumentException("Database name is null or empty");
}
Reset();
Name = database_name;
ConnectionString = connection_string;
// load and parse out table data
try
{
string sql_query = GetTableData();
DataTable dt = Database.ExecuteQuery(sql_query, null, ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
string table_name = (string)dr["TableName"];
string column_name = (string)dr["ColumnName"];
if (!Tables.ContainsKey(table_name))
{
SqlTable sql_table = new SqlTable(this, table_name);
Tables.Add(table_name, sql_table);
}
SqlColumn sql_column = new SqlColumn();
sql_column.Table = Tables[table_name];
sql_column.Name = (string)dr["ColumnName"];
sql_column.DataType = (string)dr["DataType"];
sql_column.Length = Convert.ToInt32(dr["Length"]);
sql_column.Precision = Convert.ToInt32(dr["Precision"]);
sql_column.IsNullable = Convert.ToBoolean(dr["IsNullable"]);
sql_column.IsPk = Convert.ToBoolean(dr["IsPK"]);
sql_column.IsIdentity = Convert.ToBoolean(dr["IsIdentity"]);
sql_column.ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]);
if (Tables[table_name].Columns.ContainsKey(column_name))
{
throw new Exception($"Column {column_name} already exists in table {Tables[table_name]}");
}
else
{
Tables[table_name].Columns.Add(column_name, sql_column);
}
}
}
}
catch (Exception ex)
{
ErrorList.Add(ex);
}
// get SP
try
{
string sql_query = GetStoredProcedures();
DataTable dt = Database.ExecuteQuery(sql_query, null, ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
SqlScript sql_script = new SqlScript();
sql_script.Name = (string)dr["Name"];
sql_script.Body = (string)dr["Body"];
if (StoredProcedures.ContainsKey(sql_script.Name))
{
StoredProcedures[sql_script.Name].Body += sql_script.Body;
}
else
{
StoredProcedures.Add(sql_script.Name, sql_script);
}
}
}
}
catch (Exception ex)
{
ErrorList.Add(ex);
}
// get functions
try
{
string sql_query = GetFunctions();
DataTable dt = Database.ExecuteQuery(sql_query, null, ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
SqlScript sql_script = new SqlScript();
sql_script.Name = (string)dr["Name"];
sql_script.Body = (string)dr["Body"];
if (Functions.ContainsKey(sql_script.Name))
{
Functions[sql_script.Name].Body += sql_script.Body;
}
else
{
Functions.Add(sql_script.Name, sql_script);
}
}
}
}
catch (Exception ex)
{
ErrorList.Add(ex);
}
// get constraints
try
{
string sql_query = GetConstraints();
DataTable dt = Database.ExecuteQuery(sql_query, null, ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
SqlConstraint sql_constraint = new SqlConstraint();
sql_constraint.ConstraintName = (string)dr["ConstraintName"];
sql_constraint.FKTable = (string)dr["FKTable"];
sql_constraint.FKColumn = (string)dr["FKColumn"];
sql_constraint.PKTable = (string)dr["PKTable"];
sql_constraint.PKColumn = (string)dr["PKColumn"];
if (Constraints.ContainsKey(sql_constraint.ConstraintName))
{
throw new Exception(string.Format("Constraint {0} already exists.", sql_constraint.ConstraintName));
}
else
{
Constraints.Add(sql_constraint.ConstraintName, sql_constraint);
}
}
}
}
catch (Exception ex)
{
ErrorList.Add(ex);
}
// load default values
try
{
string sql_query = GetDefaultValues();
DataTable dt = Database.ExecuteQuery(sql_query, null, ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
if (Tables.ContainsKey((string)dr["TableName"]))
{
if (Tables[(string)dr["TableName"]].Columns.ContainsKey((string)dr["ColumnName"]))
{
Tables[(string)dr["TableName"]].Columns[(string)dr["ColumnName"]].DefaultValue = RemoveWrappingCharacters((string)dr["DefaultValue"]);
}
}
}
}
}
catch (Exception ex)
{
ErrorList.Add(ex);
}
return(ErrorList.Count == 0);
}
public bool LoadDatabaseMetadata(string database_name, string connection_string)
{
if (string.IsNullOrEmpty(database_name))
throw new ArgumentException("Database name is null or empty");
Reset();
_Name = database_name;
_ConnectionString = connection_string;
// load and parse out table data
try
{
string sql_query = GetTableData();
DataTable dt = Database.ExecuteQuery(sql_query, null, _ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
string table_name = (string)dr["TableName"];
string column_name = (string)dr["ColumnName"];
if (!_Tables.ContainsKey(table_name))
{
SqlTable sql_table = new SqlTable(this,table_name);
_Tables.Add(table_name, sql_table);
}
SqlColumn sql_column = new SqlColumn();
sql_column.Table = _Tables[table_name];
sql_column.Name = (string)dr["ColumnName"];
sql_column.DataType = (string)dr["DataType"];
sql_column.Length = Convert.ToInt32(dr["Length"]);
sql_column.Precision = Convert.ToInt32(dr["Precision"]);
sql_column.IsNullable = Convert.ToBoolean(dr["IsNullable"]);
sql_column.IsPk = Convert.ToBoolean(dr["IsPK"]);
sql_column.IsIdentity = Convert.ToBoolean(dr["IsIdentity"]);
sql_column.ColumnOrdinal = Convert.ToInt32(dr["ColumnOrdinal"]);
if (_Tables[table_name].Columns.ContainsKey(column_name))
throw new Exception(string.Format("Column {0} already exists in table {1}.", column_name, _Tables[table_name]));
else
_Tables[table_name].Columns.Add(column_name, sql_column);
}
}
}
catch (Exception ex)
{
_ErrorList.Add(ex);
}
// get SP
try
{
string sql_query = GetStoredProcedures();
DataTable dt = Database.ExecuteQuery(sql_query, null, _ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
SqlScript sql_script = new SqlScript();
sql_script.Name = (string)dr["Name"];
sql_script.Body = (string)dr["Body"];
if (_StoredProcedures.ContainsKey(sql_script.Name))
_StoredProcedures[sql_script.Name].Body += sql_script.Body;
else
_StoredProcedures.Add(sql_script.Name, sql_script);
}
}
}
catch (Exception ex)
{
_ErrorList.Add(ex);
}
// get functions
try
{
string sql_query = GetFunctions();
DataTable dt = Database.ExecuteQuery(sql_query, null, _ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
SqlScript sql_script = new SqlScript();
sql_script.Name = (string)dr["Name"];
sql_script.Body = (string)dr["Body"];
if (_Functions.ContainsKey(sql_script.Name))
_Functions[sql_script.Name].Body += sql_script.Body;
else
_Functions.Add(sql_script.Name, sql_script);
}
}
}
catch (Exception ex)
{
_ErrorList.Add(ex);
}
// get constraints
try
{
string sql_query = GetConstraints();
DataTable dt = Database.ExecuteQuery(sql_query, null, _ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
SqlConstraint sql_constraint = new SqlConstraint();
sql_constraint.ConstraintName = (string)dr["ConstraintName"];
sql_constraint.FKTable = (string)dr["FKTable"];
sql_constraint.FKColumn = (string)dr["FKColumn"];
sql_constraint.PKTable = (string)dr["PKTable"];
sql_constraint.PKColumn = (string)dr["PKColumn"];
if (_Constraints.ContainsKey(sql_constraint.ConstraintName))
throw new Exception(string.Format("Constraint {0} already exists.", sql_constraint.ConstraintName));
else
_Constraints.Add(sql_constraint.ConstraintName, sql_constraint);
}
}
}
catch (Exception ex)
{
_ErrorList.Add(ex);
}
// load default values
try
{
string sql_query = GetDefaultValues();
DataTable dt = Database.ExecuteQuery(sql_query, null, _ConnectionString);
if (dt != null && dt.Rows.Count != 0 && dt.Columns.Count != 0)
{
foreach (DataRow dr in dt.Rows)
{
if (_Tables.ContainsKey((string)dr["TableName"]))
if (_Tables[(string)dr["TableName"]].Columns.ContainsKey((string)dr["ColumnName"]))
_Tables[(string)dr["TableName"]].Columns[(string)dr["ColumnName"]].DefaultValue = RemoveWrappingCharacters((string)dr["DefaultValue"]);
}
}
}
catch (Exception ex)
{
_ErrorList.Add(ex);
}
return _ErrorList.Count == 0;
}
