private void LoadTableDetails()
{
lock (LoadingTablesLock)
{
string query = "";
DataTable result = new DataTable();
Dictionary <string, List <Column> > tableDetails = new Dictionary <string, List <Column> >();
foreach (string currTable in TableNames)
{
#region Gather-Schema
List <Column> columns = new List <Column>();
switch (DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.LoadTableColumnsQuery(Database, currTable);
break;
case DbTypes.MySql:
query = MysqlHelper.LoadTableColumnsQuery(Database, currTable);
break;
case DbTypes.PgSql:
query = PgsqlHelper.LoadTableColumnsQuery(Database, currTable);
break;
}
#endregion
#region Process-Schema
result = RawQuery(query);
if (result != null && result.Rows.Count > 0)
{
foreach (DataRow currColumn in result.Rows)
{
#region Process-Each-Column
/*
* public bool IsPrimaryKey;
* public string Name;
* public string DataType;
* public int? MaxLength;
* public bool Nullable;
*/
Column tempColumn = new Column();
int maxLength = 0;
switch (DbType)
{
case DbTypes.MsSql:
#region Mssql
tempColumn.Name = currColumn["COLUMN_NAME"].ToString();
if (currColumn["CONSTRAINT_NAME"].ToString().StartsWith("PK_"))
{
tempColumn.IsPrimaryKey = true;
}
else
{
tempColumn.IsPrimaryKey = false;
}
tempColumn.DataType = currColumn["DATA_TYPE"].ToString();
if (!Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
break;
#endregion
case DbTypes.MySql:
#region Mysql
tempColumn.Name = currColumn["COLUMN_NAME"].ToString();
if (String.Compare(currColumn["COLUMN_KEY"].ToString(), "PRI") == 0)
{
tempColumn.IsPrimaryKey = true;
}
else
{
tempColumn.IsPrimaryKey = false;
}
tempColumn.DataType = currColumn["DATA_TYPE"].ToString();
if (!Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
break;
#endregion
case DbTypes.PgSql:
#region Pgsql
tempColumn.Name = currColumn["column_name"].ToString();
if (String.Compare(currColumn["is_primary_key"].ToString(), "YES") == 0)
{
tempColumn.IsPrimaryKey = true;
}
else
{
tempColumn.IsPrimaryKey = false;
}
tempColumn.DataType = currColumn["DATA_TYPE"].ToString();
if (!Int32.TryParse(currColumn["max_len"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
break;
#endregion
}
columns.Add(tempColumn);
#endregion
}
tableDetails.Add(currTable, columns);
}
#endregion
}
#region Replace-Table-Details
TableDetails = new ConcurrentDictionary <string, List <Column> >();
foreach (KeyValuePair <string, List <Column> > curr in tableDetails)
{
TableDetails.TryAdd(curr.Key, curr.Value);
}
#endregion
return;
}
}
/// <summary>
/// Show the columns and column metadata from a specific table.
/// </summary>
/// <param name="tableName">The table to view.</param>
/// <returns>A list of column objects.</returns>
public List <Column> DescribeTable(string tableName)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
string query = null;
DataTable result = null;
List <Column> columns = new List <Column>();
switch (_DbType)
{
case DbTypes.MsSql:
query = MssqlHelper.LoadTableColumnsQuery(_DatabaseName, tableName);
break;
case DbTypes.MySql:
query = MysqlHelper.LoadTableColumnsQuery(_DatabaseName, tableName);
break;
case DbTypes.PgSql:
query = PgsqlHelper.LoadTableColumnsQuery(_DatabaseName, tableName);
break;
}
result = Query(query);
if (result != null && result.Rows.Count > 0)
{
foreach (DataRow currColumn in result.Rows)
{
#region Process-Each-Column
/*
* public bool PrimaryKey;
* public string Name;
* public string DataType;
* public int? MaxLength;
* public bool Nullable;
*/
Column tempColumn = new Column();
tempColumn.Name = currColumn["COLUMN_NAME"].ToString();
int maxLength = 0;
if (!Int32.TryParse(currColumn["CHARACTER_MAXIMUM_LENGTH"].ToString(), out maxLength))
{
tempColumn.MaxLength = null;
}
else
{
tempColumn.MaxLength = maxLength;
}
tempColumn.Type = DataTypeFromString(currColumn["DATA_TYPE"].ToString());
if (String.Compare(currColumn["IS_NULLABLE"].ToString(), "YES") == 0)
{
tempColumn.Nullable = true;
}
else
{
tempColumn.Nullable = false;
}
switch (_DbType)
{
case DbTypes.MsSql:
if (currColumn["CONSTRAINT_NAME"] != null &&
currColumn["CONSTRAINT_NAME"] != DBNull.Value &&
!String.IsNullOrEmpty(currColumn["CONSTRAINT_NAME"].ToString()))
{
if (currColumn["CONSTRAINT_NAME"].ToString().ToLower().StartsWith("pk"))
{
tempColumn.PrimaryKey = true;
}
}
break;
case DbTypes.MySql:
if (currColumn["COLUMN_KEY"] != null &&
currColumn["COLUMN_KEY"] != DBNull.Value &&
!String.IsNullOrEmpty(currColumn["COLUMN_KEY"].ToString()))
{
if (currColumn["COLUMN_KEY"].ToString().ToLower().Equals("pri"))
{
tempColumn.PrimaryKey = true;
}
}
break;
case DbTypes.PgSql:
if (currColumn["IS_PRIMARY_KEY"] != null &&
currColumn["IS_PRIMARY_KEY"] != DBNull.Value &&
!String.IsNullOrEmpty(currColumn["IS_PRIMARY_KEY"].ToString()))
{
if (currColumn["IS_PRIMARY_KEY"].ToString().ToLower().Equals("yes"))
{
tempColumn.PrimaryKey = true;
}
}
break;
}
if (!columns.Exists(c => c.Name.Equals(tempColumn.Name)))
{
columns.Add(tempColumn);
}
#endregion
}
}
return(columns);
}