public bool ExecuteQuery(string strSql)
{
bool flag = false;
try
{
conn.BeginTrans();
ADODB.Command cmd = new ADODB.Command();
cmd.ActiveConnection = conn;
cmd.CommandTimeout = 90;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.CommandText = strSql;
object objRecordsAffected = Type.Missing;
object objParams = Type.Missing;
cmd.Execute(out objRecordsAffected, ref objParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
conn.CommitTrans();
flag = true;
}
catch (Exception ex)
{
flag = false;
conn.RollbackTrans();
//PublicLib.handleError("", this.GetType().Name, ex.Message + string.Format("({0})", strSql));
}
return(flag);
}
/// <summary>
/// Delete Row from access table
/// </summary>
/// <returns></returns>
public bool QueryDeleteOrUpdate(string queryString)
{
if (con == null || con.State != 1)
{
this.OpenConnection();
}
if (con.State != 1)
{
return(false);
}
//Assign the connection to the command
cmd = new CommandClass();
cmd.ActiveConnection = con;
cmd.CommandType = CommandTypeEnum.adCmdText;
//delete row
cmd.CommandText = queryString;
//Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmd.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
cmd = null;
return(true);
}
private void DisplayResults()
{
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "SELECT RCount = COUNT(*) " +
"FROM [common].[SQLDataValidation] " +
"WHERE UPPER(Severity) = 'HIGH' " +
"AND SchemaName = '" + CompanyCode + "'";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
DataTable dataTable = new DataTable();
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
try
{
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
for (int i = 0; i < recordSet.RecordCount; i++)
{
if (Convert.ToInt32(recordSet.Fields["RCount"].Value) == 0)
{
// Posting Enabled
PostingEnabledDisabled(true, false);
}
else
{
// Posting Disabled
PostingEnabledDisabled(false, false);
}
}
File.Delete(fileString);
Application.Exit();
}
public static bool Gf_ExecSql(ADODB.Connection Conn, string sQuery)
{
bool returnValue = false;
//Dim iCount As Integer
object[,] OutParam = new object[3, 5];
ADODB.Command adoCmd;
try
{
//Db Connection Check
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return(returnValue);
}
}
Cursor.Current = Cursors.WaitCursor;
//Ado Setting
GeneralCommon.M_CN1.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
adoCmd = new ADODB.Command();
Conn.BeginTrans();
adoCmd.ActiveConnection = GeneralCommon.M_CN1;
adoCmd.CommandText = "UPDATE GP_USTRESULT SET UST_LOC= '',UST_REMARTS = '' WHERE PLATE_NO = '05201206040102'";
object null_object = "";
object null_object2 = "";
adoCmd.Execute(out null_object, ref null_object2, -1);
Conn.CommitTrans();
adoCmd = null;
Cursor.Current = Cursors.Default;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
}
catch (Exception ex)
{
adoCmd = null;
Conn.RollbackTrans();
returnValue = false;
Cursor.Current = Cursors.Default;
if (GeneralCommon.M_CN1.State != 0)
{
GeneralCommon.M_CN1.Close();
}
GeneralCommon.Gp_MsgBoxDisplay((string)("Gf_ExecProcedure Error : " + ex.Message), "", "");
}
return(returnValue);
}
public int ExecuteSql(CommandTypeEnum eCmdType, SqlFrag frag)
{
ADODB.Command sqlCmd = GenerateCommand(eCmdType, frag);
object obRecsAffected = 0;
sqlCmd.Execute(out obRecsAffected);
return((int)obRecsAffected);
}
protected override void InternalExecute()
{
PrepareCommand(SQLIsolationLevel.Serializable);
try
{
SetParameters();
object recordsAffected;
object parameters = Missing.Value;
_command.Execute(out recordsAffected, ref parameters, -1);
GetParameters();
}
finally
{
UnprepareCommand();
}
}
/// <summary>
/// Insert data to table and ignored column tb_ID; True = Success, False = Fail
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="table_name"></param>
/// <returns></returns>
public bool InsertDataToTable <T> (T t, string table_name, string ignore_column_name)
{
if (con == null || con.State != 1)
{
this.OpenConnection();
}
if (con.State != 1)
{
return(false);
}
//Get properties of T
Type itemType = typeof(T);
var properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
//get field and value
string s1 = "", s2 = "";
foreach (var p in properties)
{
if (!p.Name.ToLower().Equals(ignore_column_name.ToLower()))
{
s1 += string.Format("[{0}],", p.Name);
s2 += string.Format("'{0}',", p.GetValue(t, null));
}
}
s1 = s1.Substring(0, s1.Length - 1);
s2 = s2.Substring(0, s2.Length - 1);
//Assign the connection to the command
cmd = new CommandClass();
cmd.ActiveConnection = con;
cmd.CommandType = CommandTypeEnum.adCmdText;
cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})", table_name, s1, s2);
//Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmd.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
cmd = null;
return(true);
}
private void GetExchequerVersion(out string ExVersion, ConnectionStringBuilder connObj)
{
ExVersion = "";
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
if (conn.State == 0)
{
if (connObj.DecryptedPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connObj.DecryptedPassword.Trim(),
(int)ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
try
{
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.CommandText = "SELECT name, value FROM fn_listextendedproperty(default, default, default, default, default, default, default); ";
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
if (recordSet.RecordCount > 0)
{
ExVersion = "Exchequer " + recordSet.Fields["value"].Value;
}
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
}
public DataTable GetData(string strSql, string strSqlData)
{
DataTable dt = new DataTable();
try
{
conn.BeginTrans();
ADODB.Command cmd = new ADODB.Command();
cmd.ActiveConnection = conn;
cmd.CommandTimeout = 90;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.CommandText = strSql;
object objRecordsAffected = Type.Missing;
object objParams = Type.Missing;
cmd.Execute(out objRecordsAffected, ref objParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
ADODB.Recordset rs = new ADODB.Recordset();
rs.Open(strSqlData, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, -1);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, rs);
adapter.Dispose();
rs.Close();
conn.CommitTrans();
}
catch (Exception ex)
{
conn.RollbackTrans();
//PublicLib.handleError("", this.GetType().Name, ex.Message + string.Format("({0})", strSql));
}
return(dt);
}
private void InitializeCustomerSettings(ADODB.Connection mycn)
{
var myInsert =
@"INSERT INTO tblCustomerSettings
( CustomerID,
BillingMethodID,
MobileCarrierID
)
SELECT
tblCustomers.CustomerID,
0 AS bmID,
0 AS mcID
FROM tblCustomers;";
var myCmd = new ADODB.Command {ActiveConnection = mycn, CommandText = myInsert};
myCmd.CommandType = CommandTypeEnum.adCmdText;
object dummy = Type.Missing;
ADODB.Recordset rs = myCmd.Execute(out dummy, ref dummy,0);
}
private void UpdateDBVersion(ADODB.Connection mycn)
{
var myUpdate = @"Update tblCaptions Set mdbVersion = '1.01.09'";
var myCmd = new ADODB.Command { ActiveConnection = mycn, CommandText = myUpdate };
myCmd.CommandType = CommandTypeEnum.adCmdText;
object dummy = Type.Missing;
ADODB.Recordset rs = myCmd.Execute(out dummy, ref dummy, 0);
}
private void CheckCompany()
{
this.UseWaitCursor = true;
clsCoreChecks CoreChecks = new clsCoreChecks();
clsTransactionHeaderChecks TransactionHeaderChecks = new clsTransactionHeaderChecks();
clsTransactionLineChecks TransactionLineChecks = new clsTransactionLineChecks();
clsTransactionLineJobCostingChecks TransactionLineJobCostingChecks = new clsTransactionLineJobCostingChecks();
clsTransactionLineStockCheck TransactionLineStockChecks = new clsTransactionLineStockCheck();
clsHistoryChecks HistoryChecks = new clsHistoryChecks();
clsHistoryCalculations HistoryCalculations = new clsHistoryCalculations();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "IF EXISTS (SELECT * FROM sys.tables WHERE name LIKE 'SQLDataValidation%') DROP TABLE common.SQLDataValidation; " +
"CREATE TABLE common.SQLDataValidation(IntegrityErrorNo varchar(50), " +
"IntegrityErrorCode varchar(50), " +
"Severity varchar(50), " +
"IntegrityErrorMessage varchar(max), " +
"IntegritySummaryDescription varchar(max), " +
"SchemaName varchar(10), " +
"TableName varchar(100), " +
"PositionId int);";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
try
{
Object recAff;
cmd.ActiveConnection = conn;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
Console.WriteLine("Table Created Successfully...");
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
// Clear Old Results for Company
cmd = new ADODB.Command();
cmd.CommandText = "DELETE common.SQLDataValidation WHERE SchemaName = '" + CompanyCode + "'";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ConnectModeEnum.adModeUnknown);
}
}
try
{
cmd.ActiveConnection = conn;
Object recAff;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
Console.WriteLine("Old Results Deleted Successfully...");
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
tsProgressBar.Value = 0;
tsProgressBar.Maximum = 26;
string Company = CompanyCode;
IncrementToolbar();
tsProgressBar.Refresh();
lblStatus.Text = "Checking Currencies";
// Run Core Checks
// Check for inverted currencies if system either Euro or Multi-Currency
if (MultiCurrency == true)
{
CoreChecks.CheckInvertedCurrencies(ExchequerCommonSQLConnection, Company, connPassword);
}
IncrementToolbar();
lblStatus.Text = "Checking Transaction Headers";
// Run Transaction Header Checks
// Check for Transaction Headers with Zero Folio Number
TransactionHeaderChecks.TransactionZeroFolioNum(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Header Trader Codes Exist
TransactionHeaderChecks.TransactionTraderCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Control GL codes exist if they are set
TransactionHeaderChecks.TransactionCheckControlGLCodesExistIfSet(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Control GL Codes are not Headers
TransactionHeaderChecks.TransactionCheckControlGLNotHeaders(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Currency Codes exist
TransactionHeaderChecks.TransactionCheckCurrencyCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
lblStatus.Text = "Checking Transaction Lines";
// Run Transaction Line Checks
// Check Transaction Header exists for non RUN transaction lines
TransactionLineChecks.TransactionLineExistsWithNoTransactionHeader(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Lines exist with invalid GL Code
TransactionLineChecks.TransactionLineExistWithInvalidGLCode(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Lines exist with Header GL Code
TransactionLineChecks.TransactionLineExistWithHeaderGLCode(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transction Lines exist with invalid Trader Code
TransactionLineChecks.TransactionLineExistInvalidTraderCode(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check if Transaction Line Trader Code is same as Transaction Header Trader Code
TransactionLineChecks.TransactionLineTraderCodeMatchesHeader(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Currency Codes Exist
TransactionLineChecks.TransactionLineCurrencyCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line VAT Code Exists
TransactionLineChecks.TransactionLineVATCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Inclusive VAT Code Exists
TransactionLineChecks.TransactionLineInclusiveVATCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Period Matches Header Period
TransactionLineChecks.TransactionLinePeriodMatchesHeaderPeriod(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check History Currency Codes Exist
//HistoryChecks.HistoryCheckCurrencyCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check History General Ledger Codes Exist
//HistoryChecks.HistoryCheckGLCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
// Check if Cost Centres/Departments are used
if (CCDept == true)
{
lblStatus.Text = "Checking Cost Centres and Departments";
// Check Transaction Line Cost Centre Codes Exist
TransactionLineChecks.TransactionLineCostCentresExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Department Codes Exist
TransactionLineChecks.TransactionLineDepartmentsExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check History Cost Centre Codes Exist
//HistoryChecks.HistoryCheckCostCentreCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check History Department Codes Exist
//HistoryChecks.HistoryCheckDepartmentCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
}
else
{
IncrementToolbar();
IncrementToolbar();
}
// Check if Job Costing enabled
if (JobCosting == true)
{
lblStatus.Text = "Checking Job Costing";
// Check Employee Codes on Header exist
TransactionHeaderChecks.TransactionCheckEmployeeCodesExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Job Exists
TransactionLineJobCostingChecks.TransactionLineJobExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Job check not Contract
TransactionLineJobCostingChecks.TransactionLineCheckJobNotContract(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Analysis Code Exists
TransactionLineJobCostingChecks.TransactionLineCheckAnalysisCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check Employee Currency History Codes Exist
//HistoryChecks.HistoryCheckEmployeeCurrencyCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Employee History Codes Exist
//HistoryChecks.HistoryCheckEmployeeCodesExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Job Currency History Codes Exist
//HistoryChecks.HistoryCheckJobCurrencyExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Job History Codes Exist
//HistoryChecks.HistoryCheckJobExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Analysis Id History Codes Exist
//HistoryChecks.HistoryCheckAnalysisIdExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
}
else
{
IncrementToolbar();
IncrementToolbar();
IncrementToolbar();
IncrementToolbar();
}
// Check if Stock enabled
if (StockModule == true)
{
lblStatus.Text = "Checking Stock Records";
// Check Transaction Line Stock Code Exists
TransactionLineStockChecks.TransactionLineStockCodeExists(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
// Check Transaction Line Stock Code not a Group
TransactionLineStockChecks.TransactionLineStockCodeNotGroup(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check Stock History Stock Codes Exists
//HistoryChecks.HistoryCheckStockExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
// Check if Locations is enabled
if (Locations == true)
{
lblStatus.Text = "Checking Locations";
// Check if Transaction Line Location Codes exist
TransactionLineStockChecks.TransactionLineLocationCodeDoesNotExist(ExchequerCommonSQLConnection, Company, connPassword);
IncrementToolbar();
//// Check Stock History Location Codes Exists
//HistoryChecks.HistoryCheckLocationExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
//// Check Stock History Stock Location Codes Exists
//HistoryChecks.HistoryCheckStockLocationExist(ExchequerCommonSQLConnection, Company);
//IncrementToolbar();
}
else
{
IncrementToolbar();
}
}
else
{
IncrementToolbar();
IncrementToolbar();
IncrementToolbar();
}
lblStatus.Text = "Checking Profit and Loss Brought Forward";
// Check Profit and Loss Brought Forward
HistoryCalculations.ProfitAndLossBroughtForward(ExchequerCommonSQLConnection, Company, CommitmentAccounting, connPassword);
IncrementToolbar();
this.UseWaitCursor = false;
lblStatus.Text = "Checking Completed";
tsProgressBar.Value = tsProgressBar.Maximum;
Thread.Sleep(100);
Application.DoEvents();
// Display Summary Results in UI
DisplayResults();
}
/// <summary>
///
/// </summary>
public void RefreshServerList()
{
ADODB.Connection cn = new ADODB.Connection();
ADODB.Recordset rs = new ADODB.Recordset();
ADODB.Command cmd = new ADODB.Command();
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Worksheet ws;
Excel.ListObject tbl;
int iCols = 0;
string msg = String.Empty;
string ldapQry = Properties.Settings.Default.Rdg_LdapQry;
try
{
cn.Open("Provider=ADsDSOObject;");
ldapQry = ldapQry.Replace("[Rdg.LdapPath]", Properties.Settings.Default.Rdg_LdapPath);
cmd.CommandText = ldapQry;
cmd.ActiveConnection = cn;
object objRecAff = null;
object objParameters = null;
rs = cmd.Execute(out objRecAff, ref objParameters, (int)ADODB.CommandTypeEnum.adCmdText);
bool sheetExists;
//For Each ws In wb.Sheets
// If My.Settings.Rdg_SheetName = ws.Name Then
// sheetExists = True
// ws.Activate()
// End If
//Next ws
//If sheetExists = False Then
// ws = wb.ActiveSheet
// Dim answer As Integer
// msg = "The sheet named '" & My.Settings.Rdg_SheetName & "' does not exist."
// msg = msg & vbCrLf & "Would you like to use the current sheet?"
// answer = MsgBox(msg, vbYesNo + vbQuestion, "Sheet Not Found")
// 'MessageBox.Show(msg, "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
// If answer = vbYes Then
// ws = wb.ActiveSheet
// My.Settings.Rdg_SheetName = wb.ActiveSheet.Name
// Else
// Exit Try
// End If
//Else
// ws = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(My.Settings.Rdg_SheetName)
//End If
//Globals.ThisAddIn.Application.Sheets(My.Settings.Rdg_SheetName).Activate
//Ribbon.ClearSheetContents()
//For iCols = 0 To rs.Fields.Count - 1
// ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
//Next
//ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True
//ws.Range("A2").CopyFromRecordset(rs)
//Ribbon.CreateTableFromRange()
//Ribbon.UpdateBlankCells()
//Ribbon.FormatDateColumns()
//'create server type column from the first 2 characters of the server name
//'If My.Settings.Rdg_ServerGroup = "ServerType" Then
//' tbl.ListColumns.Add(3).Name = My.Settings.Rdg_ServerGroup
//' tbl.ListColumns(My.Settings.Rdg_ServerGroup).DataBodyRange.FormulaR1C1 = "=UPPER(IFERROR(IF(SEARCH(""-"", [@Name]) > 0, LEFT([@Name], 2), """"), ""(NONE)""))"
//' Globals.ThisAddIn.Application.Columns.AutoFit()
//'End If
//Ribbon.InvalidateRibbon() 'reset dropdown lists
//Ribbon.ActivateTab()
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
private void lvSummaryResults_DoubleClick(object sender, EventArgs e)
{
lvDetailedResults.Items.Clear();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "SELECT [IntegrityErrorMessage] " +
", [SchemaName] " +
", [TableName] " +
", [PositionID] " +
"FROM [common].[SQLDataValidation] " +
"WHERE IntegrityErrorCode = '" + lvSummaryResults.SelectedItems[0].Text + "' " +
"AND SchemaName = '" + lvSummaryResults.SelectedItems[0].SubItems[3].Text + "' " +
"ORDER BY PositionID";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
try
{
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
for (int i = 0; i < recordSet.RecordCount; i++)
{
ListViewItem lvItem = new ListViewItem(recordSet.Fields[0].Value);
lvItem.SubItems.Add(recordSet.Fields[1].Value);
lvItem.SubItems.Add(recordSet.Fields[2].Value);
lvItem.SubItems.Add(recordSet.Fields[3].Value.ToString());
lvDetailedResults.Items.Add(lvItem);
recordSet.MoveNext();
}
if (conn.State == 1)
{
conn.Close();
}
lvDetailedResults.Visible = true;
btnReturnToSummary.Visible = true;
lvSummaryResults.Visible = false;
}
private void DisplayResults()
{
lvSummaryResults.Items.Clear();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = "SELECT [IntegrityErrorCode] " +
", [SummaryDescription] = CONVERT(VARCHAR(100), count(IntegrityErrorCode)) + ' ' + [IntegritySummaryDescription] " +
", [Severity] " +
", [SchemaName] " +
", [TableName] " +
"FROM [common].[SQLDataValidation] " +
"GROUP BY SchemaName, Severity, IntegrityErrorCode, IntegritySummaryDescription, TableName " +
"ORDER BY SchemaName, Severity, IntegrityErrorCode";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
try
{
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
for (int i = 0; i < recordSet.RecordCount; i++)
{
ListViewItem lvItem = new ListViewItem(recordSet.Fields[0].Value);
lvItem.SubItems.Add(recordSet.Fields[1].Value);
lvItem.SubItems.Add(recordSet.Fields[2].Value);
lvItem.SubItems.Add(recordSet.Fields[3].Value);
lvItem.SubItems.Add(recordSet.Fields[4].Value);
lvSummaryResults.Items.Add(lvItem);
recordSet.MoveNext();
}
if (conn.State == 1)
{
conn.Close();
}
// Set INI file based on results of each company
foreach (ListViewItem lvItem in lvCompanies.CheckedItems)
{
tsCheckStatus.Text = "Updating INI file: " + lvItem.SubItems[1].Text.TrimEnd();
Thread.Sleep(1000);
Application.DoEvents();
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
conn = new ADODB.Connection();
Command command = new ADODB.Command();
command.CommandText = "SELECT RCount = COUNT(*) " +
"FROM [common].[SQLDataValidation] " +
"WHERE UPPER(Severity) = 'HIGH' " +
"AND SchemaName = '" + lvItem.SubItems[1].Text.TrimEnd() + "'";
command.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
command.ActiveConnection = conn;
ADODB.Recordset rs = null;
Object objAff;
try
{
rs = (ADODB.Recordset)command.Execute(out objAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
}
catch
{
throw;
}
{
if (Convert.ToInt32(rs.Fields["RCount"].Value) == 0)
{
// Posting Enabled
tsCheckStatus.Text = "Posting Enabled: " + lvItem.SubItems[1].Text.TrimEnd();
Thread.Sleep(1000);
Application.DoEvents();
PostingEnabledDisabled(true, lvItem.SubItems[1].Text.TrimEnd());
lvItem.SubItems[4].Text = "Enabled";
}
else
{
// Posting Disabled
tsCheckStatus.Text = "Posting Disabled: " + lvItem.SubItems[1].Text.TrimEnd();
Thread.Sleep(1000);
Application.DoEvents();
PostingEnabledDisabled(false, lvItem.SubItems[1].Text.TrimEnd());
lvItem.SubItems[4].Text = "Disabled";
}
}
if (conn.State == 1)
{
conn.Close();
}
}
tsCheckStatus.Text = "Check Complete";
Thread.Sleep(1000);
Application.DoEvents();
}