/// <summary>
/// Fill the Database Data based on Information that`s extracted of SQL Server Selected Database
/// in the Interface of the app
/// </summary>
/// <param name="customColumnsFileName"> Full path file for CustomMappings.xlsx file </param>
private void GetDBData(string customColumnsFileName)
{
dbData.DatabaseName = currentDB;
List <ManagedColumns> customInsertColumns = new List <ManagedColumns>();
List <ManagedColumns> customUpdateColumns = new List <ManagedColumns>();
List <ManagedColumns> customSelectColumns = new List <ManagedColumns>();
if (File.Exists(customColumnsFileName) && customColumns)
{
DataTable configTbl = ReadExcelFile.getExcellToDtbl(customColumnsFileName);
for (int i = 0; i < configTbl.Rows.Count; i++)
{
if (configTbl.Rows[i][0].ToString() != string.Empty)
{
ManagedColumns managedInsertColumns = new ManagedColumns();
managedInsertColumns.ColumnName = configTbl.Rows[i][0].ToString();
managedInsertColumns.ColumnValue = configTbl.Rows[i][1].ToString();
managedInsertColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][2].ToString()));
managedInsertColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][3].ToString()));
customInsertColumns.Add(managedInsertColumns);
ManagedColumns managedUpdateColumns = new ManagedColumns();
managedUpdateColumns.ColumnName = configTbl.Rows[i][0].ToString();
managedUpdateColumns.ColumnValue = configTbl.Rows[i][1].ToString();
managedUpdateColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][4].ToString()));
managedUpdateColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][5].ToString()));
customUpdateColumns.Add(managedUpdateColumns);
ManagedColumns managedSelectColumns = new ManagedColumns();
managedSelectColumns.ColumnName = configTbl.Rows[i][0].ToString();
managedSelectColumns.ColumnValue = configTbl.Rows[i][1].ToString();
managedSelectColumns.UsedForParam = Convert.ToBoolean(int.Parse(configTbl.Rows[i][6].ToString()));
managedSelectColumns.UsedForColumn = Convert.ToBoolean(int.Parse(configTbl.Rows[i][7].ToString()));
customSelectColumns.Add(managedSelectColumns);
}
}
dbData.ManagedInsertColumns = customInsertColumns;
dbData.ManagedUpdateColumns = customUpdateColumns;
dbData.ManagedSelectColumns = customSelectColumns;
}
else if (customColumns)
{
MessageBox.Show("File CustomColumsMappings.xlsx does not exist!\nNot found in the Application Configuration dir!");
}
int startvalue = 1;
//1 RANK 2 tableName, 3 columnname, 4 primarikey chek, 5 columndatatype , 6 null , 7 IsFK
procGenData = sqlConn.GetProcTablesData(serverAddress, serverUser, serverUserPassword, currentDB, configurationFolderPath, columnsDataSql);
string currentPrimaryKey = string.Empty;
string currentPrimaryKeyDataType = string.Empty;
List <ColumnData> currentColumns = new List <ColumnData>();
for (int i = 0; i < procGenData.Rows.Count; i++)
{
if ((startvalue == int.Parse(procGenData.Rows[i][0].ToString()) - 1) || ((i + 1) == procGenData.Rows.Count))
{
TableData currentTbl = new TableData();
currentTbl.Columns = currentColumns;
currentTbl.TableName = procGenData.Rows[i - 1][1].ToString();
currentTbl.PrimaryKey = currentPrimaryKey;
currentTbl.PrimaryKeyDataType = currentPrimaryKeyDataType;
dbData.Tables.Add(currentTbl);
currentColumns = new List <ColumnData>();
startvalue = int.Parse(procGenData.Rows[i][0].ToString());
}
if (bool.Parse(procGenData.Rows[i][3].ToString()))
{
currentPrimaryKey = procGenData.Rows[i][2].ToString();
currentPrimaryKeyDataType = procGenData.Rows[i][4].ToString();
}
if (bool.Parse(procGenData.Rows[i][3].ToString()) == false)
{
ColumnData currentColumn = new ColumnData();
currentColumn.ColumnName = procGenData.Rows[i][2].ToString();
currentColumn.ColumnDataType = procGenData.Rows[i][4].ToString();
currentColumn.isNullable = bool.Parse(procGenData.Rows[i][5].ToString());
currentColumn.isFK = bool.Parse(procGenData.Rows[i][6].ToString());
currentColumns.Add(currentColumn);
}
}
}