public static String GetColumnDbType(String strTableName, string strCol)
{
TableColumnData col = GetTableColumn(strTableName, strCol);
if (col == null)
{
return(String.Empty);
}
return(col.DBType);
}
private static void InitDataTableList(bool isSystemTable)
{
bool isSQL = (isSystemTable && DataQueryProvider.IsSystemSQLConnection) || (isSystemTable == false && DataQueryProvider.IsCompanySQLConnection);
String strQuery;
Dictionary <String, TableStructureData> lstTables = new Dictionary <string, TableStructureData>();
if (isSystemTable)
{
SystemTables = new List <string>();
}
#region Table
String strCol;
if (isSQL)
{
strQuery = @"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE='BASE TABLE') AND (TABLE_NAME<>'sysdiagrams')
ORDER BY TABLE_NAME" ;
strCol = "TABLE_NAME";
}
else
{
strQuery = @"SELECT * FROM sqlite_master WHERE type='table'";
strCol = "tbl_name";
}
DataSet ds;
if (isSystemTable)
{
ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(strQuery);
}
else
{
ds = DataQueryProvider.CompanyDatabaseHelper.RunQuery(strQuery);
}
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
String strTableName = row[strCol].ToString();
if (lstTables.ContainsKey(strTableName) == false)
{
TableStructureData table = new TableStructureData(strTableName);
lstTables.Add(strTableName, table);
if (isSystemTable)
{
SystemTables.Add(strTableName);
}
}
}
}
#endregion
if (isSQL)
{
#region All Columns
strQuery = String.Format(@"SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS " );
ds = null;
if (isSystemTable)
{
ds = DataQueryProvider.SystemDatabaseHelper.RunQuery(strQuery);
}
else
{
ds = DataQueryProvider.CompanyDatabaseHelper.RunQuery(strQuery);
}
int iCount = -1;
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
TableStructureData table = null;
if (lstTables.TryGetValue(dr["TABLE_NAME"].ToString(), out table))
{
iCount++;
String strColumnName = dr["COLUMN_NAME"].ToString();
if (table.ColumnsList.ContainsKey(strColumnName) == false)
{
#region ColumnData
TableColumnData colData = new TableColumnData();
colData.Index = iCount;
colData.Name = strColumnName;
String strMaxLength = dr["CHARACTER_MAXIMUM_LENGTH"].ToString();
if (String.IsNullOrWhiteSpace(strMaxLength) == false && strMaxLength != "NULL" && dr["DATA_TYPE"].ToString() != "ntext")
{
colData.DBType = String.Format(@"{0}({1})", dr["DATA_TYPE"].ToString(), strMaxLength);
}
else
{
colData.DBType = dr["DATA_TYPE"].ToString();
}
colData.Nullable = (dr["IS_NULLABLE"].ToString() == "YES");
#region Primary Column
if (strColumnName.EndsWith("ID"))
{
String strTableName = strColumnName.Substring(0, strColumnName.Length - 2) + "s";
if (table.TableName.Equals(strTableName))
{
table.PrimaryColumn = strColumnName;
}
}
#endregion
#region NO column
if (strColumnName.ToUpper().Equals("NO"))
{
table.NOColumn = strColumnName;
}
else if (strColumnName.ToUpper().EndsWith("NO"))
{
String stTemp = table.TableName.ToUpper().Substring(0, table.TableName.Length - 1) + "NO";
if (strColumnName.ToUpper().Equals(stTemp))
{
table.NOColumn = strColumnName;
}
}
#endregion
#region NAME column
if (strColumnName.ToUpper().Equals("NAME"))
{
table.NAMEColumn = strColumnName;
}
if (strColumnName.ToUpper().EndsWith("NAME"))
{
String stTemp = table.TableName.ToUpper().Substring(0, table.TableName.Length - 1) + "NAME";
if (strColumnName.ToUpper().Equals(stTemp))
{
table.NAMEColumn = strColumnName;
}
}
#endregion
#endregion
table.ColumnsList.Add(strColumnName, colData);
if (strColumnName.Contains("FK_"))
{
String strTemp = strColumnName.Split('_')[1];
String strPrimaryTableName = strTemp.Substring(0, strTemp.Length - 2) + "s";
if (table.ForeignColumnsList == null)
{
table.ForeignColumnsList = new Dictionary <String, String>();
}
table.ForeignColumnsList.Add(strColumnName, strPrimaryTableName);
}
}
}
}
}
#endregion
}
foreach (String strTableName in lstTables.Keys)
{
if (DataTablesList.ContainsKey(strTableName) == false)
{
DataTablesList.Add(strTableName, lstTables[strTableName]);
}
}
}