/// <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); } } }