public void SetAuditLogin(string appVer)
{
string pcName = Environment.MachineName.Substring(0, 8);
string pcOSVer = Environment.OSVersion.ToString();
string pcPlatForm = Environment.OSVersion.Platform.ToString();
int osMajor = Environment.OSVersion.Version.Major;
int osMinor = Environment.OSVersion.Version.Minor;
bool bitBool = Environment.Is64BitOperatingSystem; int bit = -1;
string appVersion = appVer;
if (bitBool)
{
bit = 64;
}
else
{
bit = 32;
}
long seqNum = GetSeq("annotator.GPJ_AUDIT_SEQ");
int month = DateTime.Now.Month; int day = DateTime.Now.Day; int year = DateTime.Now.Year;
conn.Open();
string sqlCommand = $"INSERT INTO GPJ_APP_AUDIT VALUES ({seqNum}, '{pcName}', SYSDATE, 'GPJ', '{pcName}', '{pcOSVer}', '{pcPlatForm}', {osMajor}, {osMinor}, " +
$"{bit.ToString()}, 'TWITTER_POSTER', '{appVersion}', 'TWITPSTR', SYSDATE, '{TimeZone.CurrentTimeZone.StandardName}');";
ADODB.Recordset rst = new ADODB.Recordset();
object rowsAffected;
rst = conn.Execute(sqlCommand, out rowsAffected);
conn.Close();
}
public void DtaDbCre()
{
if (!File.Exists(@"Data\Data.accdb"))
{
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=Data\\Data.accdb; Jet OLEDB:Engine Type=5");
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
//result = true;
}
catch //(Exception ex)
{
}
cat = null;
TblCre();
} //End if
} //End dbCre
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static bool CreateNewAccessDatabase(string fileName)
{
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
//ADOX.Table table = new ADOX.Table();
//Create the table and it's fields.
//table.Name = "Table1";
//table.Columns.Append("Field1");
//table.Columns.Append("Field2");
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + "; Jet OLEDB:Engine Type=5");
//cat.Tables.Append(table);
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
result = true;
}
catch (Exception)
{
result = false;
}
cat = null;
return(result);
}
/// <summary>
/// Create access database table; True = Success, False = Fail
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="table_name"></param>
/// <returns></returns>
public bool CreateTable <T>(string table_name)
{
bool r = true;
CatalogClass cat = openDatabase();
//Get properties of T
Type itemType = typeof(T);
var properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
try {
//Create the table and it's fields.
ADOX.Table table = new ADOX.Table();
table.Name = table_name;
//Add column to the table.
foreach (var p in properties)
{
table.Columns.Append(tableField(p.Name, cat, myConverter.FromVSTypeToTableAccessDataType(p.PropertyType.Name.ToString())));
}
//Add the table to our database
cat.Tables.Append(table);
// Close the connection to the database after we are done creating it and adding the table to it.
con = (ADODB.Connection)cat.ActiveConnection;
if (con != null && con.State != 0)
{
con.Close();
}
} catch { r = false; }
cat = null;
return(r);
}
/// <summary>
/// 关闭一个数据库连接
/// </summary>
/// <param name="conn"></param>
public void CloseDatabase(ADODB.Connection conn)
{
if (conn == null)
{
return;
}
int orgState = 0;
try
{
orgState = conn.State;
if (orgState == 1)
{
conn.Close();
OnDbClosedByUser(conn);
}
}
catch (Exception ex)
{
if (orgState == 1)
{
log.Error(ex);
}
else
{
log.Error(ex.Message);
}
}
}
public object[] db_access(string strSQL)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a onedimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
return(dataSuite);
}
public bool CreateDatabase()
{
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
try
{
string connection = this.GetConnectionString();
cat.Create(connection);
Table assignment = this.CreateAssignmentTable(cat);
cat.Tables.Append(assignment);
Table folders = this.CreateFoldersTable(cat);
cat.Tables.Append(folders);
Table image = this.CreateImageTable(cat);
cat.Tables.Append(image);
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
result = true;
}
catch (Exception ex)
{
result = false;
}
cat = null;
return(result);
}
public void Dispose()
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
/// <summary>
/// Closes the database connection.
/// </summary>
public void CloseDatabaseConnection()
{
if (this.Open)
{
connection.Close();
this.Open = false;
}
}
private void ADODB_Close(ADODB.Connection _ad_Con)
{
try
{
_ad_Con.Close();
}
catch
{
}
}
public void DisConnect()
{
//
if (con == null)
{
return;
}
con.Close();
con = null;
}
public void Disconnect()
{
try
{
dbConn.Close();
// Release the Catalog object from the memory.
System.Runtime.InteropServices.Marshal.ReleaseComObject(dtCatalog);
GC.Collect();
GC.GetTotalMemory(true);
GC.WaitForPendingFinalizers();
}
catch { }
}
protected override void OnDispose(bool disposing)
{
base.OnDispose(disposing);
try
{
if (_Columns != null && _Columns.Count > 0)
{
foreach (ADOColumn item in _Columns)
{
item.Dispose();
}
}
if (_Table != null)
{
Marshal.ReleaseComObject(_Table);
}
if (_Cat != null)
{
Marshal.ReleaseComObject(_Cat);
}
if (_Conn != null)
{
_Conn.Close();
Marshal.ReleaseComObject(_Conn);
}
if (_TableDef != null)
{
Marshal.ReleaseComObject(_TableDef);
}
if (_Db != null)
{
_Db.Close();
Marshal.ReleaseComObject(_Db);
}
if (_Dbe != null)
{
Marshal.ReleaseComObject(_Dbe);
}
}
catch (Exception ex)
{
if (DAL.Debug)
{
DAL.WriteLog(ex.ToString());
}
}
}
public static DataTable GetDataTableFromSQLAdoDb(string sql, string connectionString)
{
var conn = new ADODB.Connection();
var recordSet = new ADODB.Recordset();
conn.Open(connectionString);
recordSet.Open(sql, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0);
var table = new DataTable();
var adapter = new OleDbDataAdapter();
adapter.Fill(table, recordSet);
conn.Close();
return(table);
}
public void InsertAdo()
{
var con = new Connection();
con.Open("Provider='sqloledb';Data Source='(local)';Initial Catalog='Proba';Integrated Security='SSPI';");
var rec = new Recordset();
rec.Open("SELECT s1, s2 FROM Tabl1", con, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 0);
rec.MoveFirst();
rec.Fields["s1"].Value = "s";
rec.Fields["s2"].Value = "s";
rec.Update("s1", rec.Fields["s1"].Value);
rec.Close();
con.Close();
}
public void Dispose()
{
try
{
if (_Columns != null && _Columns.Count > 0)
{
foreach (var item in _Columns)
{
item.Dispose();
}
}
if (_Table != null)
{
Marshal.ReleaseComObject(_Table);
}
if (_Cat != null)
{
Marshal.ReleaseComObject(_Cat);
}
if (_Conn != null)
{
_Conn.Close();
Marshal.ReleaseComObject(_Conn);
}
if (_TableDef != null)
{
Marshal.ReleaseComObject(_TableDef);
}
if (_Db != null)
{
_Db.Close();
Marshal.ReleaseComObject(_Db);
}
if (_Dbe != null)
{
Marshal.ReleaseComObject(_Dbe);
}
}
catch (Exception ex)
{
if (DAL.Debug)
{
DAL.WriteLog(ex.ToString());
}
}
}
public static void ubahDB2(string db)
{
ADODB.Connection aco = default(ADODB.Connection);
aco = new ADODB.Connection();
if (aco.State.Equals(ObjectStateEnum.adStateOpen))
{
aco.Close();
aco = null;
}
aco.CursorLocation = CursorLocationEnum.adUseClient;
aco.Properties.Refresh();
aco.Open("DSN=DSNask2");
object obj = Type.Missing;
aco.Execute(db, out obj);
}
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;
}
}
private bool ADODB_Connect(ADODB.Connection _ad_Con, string _strCon)
{
try
{
if (_ad_Con.State == 1)
{
_ad_Con.Close();
}
_ad_Con.Open(_strCon, "", "", 0);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
return(true);
}
public Boolean CreateAccessDatabase()
{
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
//Create the table and it's fields.
table.Name = "UserInfo";
table.Columns.Append("username", ADOX.DataTypeEnum.adVarWChar, 40);
table.Columns.Append("password", ADOX.DataTypeEnum.adVarWChar, 40);
table.Columns.Append("age", ADOX.DataTypeEnum.adInteger, 2);
table.Columns.Append("gender", ADOX.DataTypeEnum.adVarWChar, 6);
table.Columns.Append("occupation", ADOX.DataTypeEnum.adVarWChar, 40);
table.Columns.Append("income", ADOX.DataTypeEnum.adDouble, 10);
table.Keys.Append("Primary Key", ADOX.KeyTypeEnum.adKeyPrimary, "password", "", "");
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\Umer\Documents\Visual Studio 2015\Projects\masterpage\" + "UserInfo.mdb" + "; Jet OLEDB:Engine Type=5");
//cat.Columns.Append("col1", DataTypeEnum.adInteger, 4);
cat.Tables.Append(table);
//Now Close the database
ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
if (con != null)
{
con.Close();
}
result = true;
}
catch (Exception ex)
{
Console.WriteLine(ex);
// Namebox.Text = ex.ToString();
result = false;
}
cat = null;
return(result);
}
/// <summary>
/// Close connection to access database; True = Success, False = Fail
/// </summary>
/// <returns></returns>
public bool Close()
{
bool r = true;
if (con == null || con.State == 0)
{
goto END;
}
try {
con.Close();
r = con.State == 0;
} catch { r = false; }
goto END;
END:
IsConnected = false;
return(r);
}
/// <summary>arma la tabla excel</summary>
/// <param name="strFileName">strfileName</param>
/// <param name="extencion">extension</param>
/// <returns>strTables</returns>
/// <example>
/// <code>public static string[] GetTableExcel(string strFileName, string extencion)
/// {
/// string[] strTables = new string[100];
/// Catalog oCatlog = new Catalog();
/// ADOX.Table oTable = new ADOX.Table();
/// ADODB.Connection oConn = new ADODB.Connection();
/// if (extencion == ".xls")
/// oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
/// if (extencion == ".xlsx")
/// {
/// try
/// {
/// oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + "; Jet OLEDB:Engine Type=5;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", "", "", 0);
/// }
/// catch (Exception e)
/// {
/// Global.setError(e.Message);
/// log.Error(e.Message);
/// throw;
/// }
///
/// }
///
///
/// oCatlog.ActiveConnection = oConn;
/// if (oCatlog.Tables.Count > 0)
/// {
/// int item = 0;
/// foreach (ADOX.Table tab in oCatlog.Tables)
/// {
/// if (tab.Type == "TABLE")
/// {
/// strTables[item] = tab.Name;
/// item++;
/// }
/// }
/// }
/// oConn.Close();
/// return strTables;
/// }</code>
/// </example>
public static string[] GetTableExcel(string strFileName, string extencion)
{
string[] strTables = new string[100];
Catalog oCatlog = new Catalog();
ADOX.Table oTable = new ADOX.Table();
ADODB.Connection oConn = new ADODB.Connection();
if (extencion == ".xls")
{
oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);
}
if (extencion == ".xlsx")
{
try
{
oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + "; Jet OLEDB:Engine Type=5;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", "", "", 0);
}
catch (Exception e)
{
Global.setError(e.Message);
log.Error(e.Message);
throw;
}
}
oCatlog.ActiveConnection = oConn;
if (oCatlog.Tables.Count > 0)
{
int item = 0;
foreach (ADOX.Table tab in oCatlog.Tables)
{
if (tab.Type == "TABLE")
{
strTables[item] = tab.Name;
item++;
}
}
}
oConn.Close();
return(strTables);
}
public static ADODB.Recordset PangggilDB(string db)
{
ADODB.Connection AC = new ADODB.Connection();
if (AC.State.Equals(ObjectStateEnum.adStateOpen))
{
AC.Close();
AC = null;
}
AC.CursorLocation = CursorLocationEnum.adUseClient;
AC.Properties.Refresh();
AC.Open("DSN=DSNask");
object obj = Type.Missing;
ADODB.Recordset AR = new ADODB.Recordset();
AR = AC.Execute(db, out obj);
return(AR);
}
public void MegaInsertAdo()
{
var con = new Connection();
con.Open("Provider='sqloledb';Data Source='(local)';Initial Catalog='Proba';Integrated Security='SSPI';");
var rec = new Recordset();
rec.Open("SELECT s1,s2 FROM Tabl2", con, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 0);
for (int i = 1; i <= 10000; ++i)
{
//string [] f={"s1","s2"};
int[] f = { 0, 1 };
string [] v = { "s1", "s2" };
rec.AddNew(f, v);
//rec.Fields["s2"].Value = i.ToString();
//rec.Fields["s2"].Value = "new";
rec.Update(f, v);
}
rec.Close();
con.Close();
}
public bool CreateNewAccessDatabase(string fileName)
{
if (File.Exists(fileName))
{
return(true);
}
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
table.Name = "ThreadData";
table.Columns.Append("ID", ADOX.DataTypeEnum.adInteger);
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID", null, null);
table.Columns.Append("ThreadID");
table.Columns.Append("Time");
table.Columns.Append("Data");
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + "; Jet OLEDB:Engine Type=5");
ADODB.Connection con = cat.ActiveConnection;
table.Columns["ID"].ParentCatalog = cat;
table.Columns["ID"].Properties["AutoIncrement"].Value = true;
cat.Tables.Append(table);
if (con != null)
{
con.Close();
}
result = true;
}
catch (Exception ex)
{
result = false;
}
cat = null;
return(result);
}
public static bool ExportAccessDb(String fileName, DataSet ds) {
bool result = false;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table table = new ADOX.Table();
try {
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + "; Jet OLEDB:Engine Type=5");
//create tables
for (int i = 0; i < ds.Tables.Count; i++) {
var ctTable = ds.Tables[i];
//Create the table and it's fields.
table.Name = ctTable.TableName;
CreateAccessCols(table, ctTable);
cat.Tables.Append(table);
}//end method
//now close the database
ADODB.Connection conn = cat.ActiveConnection as ADODB.Connection;
if (conn != null)
conn.Close();
result = true;
}
catch (Exception ex) {
result = false;
}
cat = null;
FillAccessDb(fileName, ds);
return result;
}//end method
public object[] db_access(string strSQL)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a onedimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
return dataSuite;
}
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 object[] db_access(string strSQL, ref int fndExcep)
{
ADODB.Connection objCon;
ADODB.Recordset objRec;
object[,] dataRows;
object[] dataSuite;
string strCon;
string tmpString;
dataSuite = null;
objCon = new ADODB.Connection();
objRec = new ADODB.Recordset();
try
{
//establish the connection string and open the database connection
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon.Open(strCon);
//execute the SQL and return the recrodset of results
objRec = objCon.Execute(strSQL, out missing, 0);
//populate a two dinmensional object array with the results
dataRows = objRec.GetRows();
//get a one dimensional array that can be placed into the Test Suite dropdown
dataSuite = thinArray(dataRows);
//close the recordset
objRec.Close();
//close the database connection
objCon.Close();
}
catch (Exception e)
{
tmpString = e.Message;
//set the variable to ternibate the script
fndExcep = -1;
}
return dataSuite;
}
private void btnExtract_Click(object sender, EventArgs e)
{
object[] rtnList;
string[] lstArray;
string argID;
string itmList;
string strCon;
string steID;
string thsFuncID;
string tstID;
int itmCount;
int argNum;
int numSteps;
int numTests;
ArrayList slctList;
ADODB.Connection objCon;
DialogResult valExtract;
itmList = "";
itmCount = lstCaseSelect.Items.Count;
lstArray = new string[itmCount];
strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" +
"database=functional_test_data;option=3";
objCon = new ADODB.Connection();
//set lstArray to all items in the lstCaseSelect box
for (int x = 0; x < itmCount; x++)
lstArray[x] = lstCaseSelect.Items[x].ToString();
slctList = new ArrayList(itmCount);
for (int x = 0; x < itmCount; x++)
{
//get a carriage return delimited string of all entries in lstCaseSelect
itmList = itmList + lstArray[x] + "\r\n";
//add the items to slctList whiule iterating through the lstCaseSelect items
slctList.Add(lstCaseSelect.Items[x]);
}
//show an information message box with an escape option
valExtract = MessageBox.Show("You will be extracting the following tests from the database \r\n\r\n" + itmList + "\r\nSelect Yes to continue. Select No to return to TestDriver ",
"Database Test Extractor", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (valExtract == DialogResult.Yes)
{
for (int cnt = 0; cnt < itmCount; cnt++)
{
string strSQL;
//get the function ID aznd number of steps from the test table
strSQL = "SELECT id, number_of_steps FROM test WHERE name = '" + lstArray[cnt] + "'";
rtnList = db_access(strSQL, ref fndExcep);
tstID = rtnList[0].ToString();
if (rtnList[1].ToString() != "")
{
numSteps = Convert.ToInt32(rtnList[1]);
}
else
{
numSteps = 0;
}
//open a connection to the database
objCon.Open(strCon);
//set a for loop with x + 1 being the current step number being processed
//renmove all steps that are not used in any other tests (recCount = 1)
for (int x = 0; x < numSteps; x++)
{
//get the function id and arg set id using the
strSQL = "SELECT function_id, argument_set_id FROM step WHERE (test_id = '" + tstID + "' AND number = '" + (x + 1).ToString() + "')" ;
rtnList = db_access(strSQL, ref fndExcep);
thsFuncID = rtnList[0].ToString();
argID = rtnList[1].ToString();
strSQL = "DELETE FROM step WHERE argument_set_id = '" + argID + "' AND function_id = '" + thsFuncID +
"' AND test_id = '" + tstID + "' AND number = '" + (x + 1).ToString() + "'";
objCon.Execute(strSQL, out missing, 0);
//if an argument set is no longer used, gert rid of it
strSQL = "SELECT COUNT(*) FROM step WHERE argument_set_id = '" + argID + "'";
rtnList = db_access(strSQL, ref fndExcep);
argNum = Convert.ToInt32(rtnList[0]);
if (argNum == 0)
{
strSQL = "DELETE FROM argument WHERE argument_set_id = '" + argID + "'";
objCon.Execute(strSQL, out missing, 0);
//delete t
strSQL = "DELETE FROM argument_set WHERE id = '" + argID + "'";
objCon.Execute(strSQL, out missing, 0);
}
}
//get the regression suite id from the test being extracted
strSQL= "SELECT regression_suite_id FROM test WHERE id = '" + tstID + "'";
rtnList = db_access(strSQL, ref fndExcep);
steID = rtnList[0].ToString();
//delete the test from the test rable
strSQL = "DELETE FROM test WHERE id = '" + tstID + "'";
objCon.Execute(strSQL, out missing, 0);
//get the number of tests from in the regression suite. If delete the regression suite
strSQL = "SELECT COUNT(*) FROM test WHERE regression_suite_id = '" + steID + "'";
rtnList = db_access(strSQL, ref fndExcep);
numTests = Convert.ToInt32(rtnList[0]);
//if there are no tests left in the database delete the suite
if (numTests == 0)
{
strSQL = "DELETE FROM regression_suite WHERE id = '" + steID + "'";
objCon.Execute(strSQL, out missing, 0);
}
//close the database connection
objCon.Close();
//get the list of
for (int x = 0; x < slctList.Count; x++)
{
if (Convert.ToString(slctList[x]) == lstArray[cnt])
{
slctList.Remove(lstArray[cnt]);
break;
}
}
}
lstCaseSelect.Items.Clear();
}
}
public bool Gf_Sp_Display3(ADODB.Connection Conn, FarPoint.Win.Spread.FpSpread oSpread, string sQuery, Collection lColumn, bool MsgChk)
{
bool returnValue = false;
int iCount;
int iRowCount;
int iColcount;
object[,] ArrayRecords;
ADODB.Recordset AdoRs;
if (Conn.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return(false);
}
}
AdoRs = new ADODB.Recordset();
try
{
returnValue = true;
//if (oSpread.ActiveSheet.RowCount > 0)
//{
// //
// //'Hux,修改。
// //'解决:Spread有两条数据时，点击列头排序后，再点击Spread插入，Spread行清除时报错。
// oSpread.ActiveSheet.AutoSortColumn(0);
// oSpread.ActiveSheet.RowCount = 0;
//}
//FarPoint.Win.Spread.FpSpread with_1 = oSpread;
iCount = 0;
Cursor.Current = Cursors.WaitCursor;
/////////20130606begin解决保存失败后，第一次查询提示算数运算符溢出的问题
AdoRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
AdoRs.CursorType = ADODB.CursorTypeEnum.adOpenStatic;
//AdoRs.CursorType =
//AdoRs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
//AdoRs.Open(sQuery, Conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1);
AdoRs.Open(sQuery, Conn);
/////////20130606end
if (AdoRs.BOF || AdoRs.EOF)
{
if (MsgChk)
{
GeneralCommon.Gp_MsgBoxDisplay("无法找到该资料..!!!", "I", "");
}
returnValue = false;
AdoRs.Close();
AdoRs = null;
Cursor.Current = Cursors.Default;
return(returnValue);
}
int RsRowCount = AdoRs.RecordCount;
int RsColCount = AdoRs.Fields.Count;
ArrayRecords = new object[RsRowCount, RsColCount];
int i = 0;
while (!AdoRs.EOF)
{
for (int j = 0; j < AdoRs.Fields.Count; j++)
{
ArrayRecords[i, j] = AdoRs.Fields[j].Value;
}
i++;
AdoRs.MoveNext();
}
AdoRs.Close();
AdoRs = null;
//此处进行表单数据录入
int ROW = 0;
int Col = 0;
if (ArrayRecords.GetLength(0) != 0)
{
for (iRowCount = 0; iRowCount < ArrayRecords.GetLength(0); iRowCount++)
{
for (iColcount = 1; iColcount <= 7; iColcount++)
{
if (substr(ArrayRecords[iRowCount, 0].ToString(), 1, 1) == "0")
{
Col = 2 * iColcount - 2;
}
else if (substr(ArrayRecords[iRowCount, 0].ToString(), 1, 1) == "1")
{
Col = 2 * iColcount - 1;
}
if (substr(ArrayRecords[iRowCount, 0].ToString(), 0, 1) == "A")
{
ROW = 0;
}
else if (substr(ArrayRecords[iRowCount, 0].ToString(), 0, 1) == "B")
{
ROW = 1;
}
else if (substr(ArrayRecords[iRowCount, 0].ToString(), 0, 1) == "C")
{
ROW = 2;
}
else if (substr(ArrayRecords[iRowCount, 0].ToString(), 0, 1) == "D")
{
ROW = 3;
}
else if (substr(ArrayRecords[iRowCount, 0].ToString(), 0, 1) == "T")
{
ROW = 4;
}
if (ArrayRecords[iRowCount, iColcount].ToString() == "" || ArrayRecords[iRowCount, iColcount].ToString() == "0")
{
oSpread.ActiveSheet.Cells[ROW, Col].Text = "";
}
else
{
oSpread.ActiveSheet.Cells[ROW, Col].Text = ArrayRecords[iRowCount, iColcount].ToString();
}
}
}
}
Cursor.Current = Cursors.Default;
ArrayRecords = null;
}
catch (Exception ex)
{
AdoRs = null;
returnValue = false;
GeneralCommon.Gp_MsgBoxDisplay((string)("Gf_Sp_Display Error : " + ex.Message), "", "");
Cursor.Current = Cursors.Default;
if (Information.Err().Number == 438 || Information.Err().Number == -2147467259)
{
Conn.Close();
}
}
return(returnValue);
}
public bool disconnect()
{
try {
if (conectionString.Contains("Provider=") == true)
{
if (adocon.State == 0)
{
return(true);
}
else if (adocon.State == 1)
{
adocon.Close();
return(true);
}
else if (adocon.State == 2)
{
adocon.Close();
return(true);
}
else if (adocon.State == 4)
{
adocon.Cancel();
adocon.Close();
return(true);
}
else if (adocon.State == 8)
{
adocon.Cancel();
adocon.Close();
return(true);
}
else
{
return(false);
}
}
else if (conectionString.Contains("Driver=") == true)
{
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
return(true);
}
else if (con.State == System.Data.ConnectionState.Executing)
{
con.Close();
return(true);
}
else if (con.State == System.Data.ConnectionState.Fetching)
{
con.Close();
return(true);
}
else if (con.State == System.Data.ConnectionState.Connecting)
{
con.Close();
return(true);
}
else if (con.State == System.Data.ConnectionState.Closed)
{
return(true);
}
else if (con.State == System.Data.ConnectionState.Broken)
{
con.Close();
return(true);
}
else
{
return(false);
}
}
else
{
if (mycon.State == System.Data.ConnectionState.Open)
{
mycon.Close();
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Executing)
{
mycon.Close();
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Fetching)
{
mycon.Close();
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Connecting)
{
mycon.Close();
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Closed)
{
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Broken)
{
mycon.Close();
return(true);
}
else
{
return(false);
}
}
} catch (Exception e) {
error = e.Message.ToString();
return(false);
}
}
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();
}
