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();
}
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 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 switchDatabase(string databasename)
{
try {
disconnect();
string conStr = conectionString.Replace(database, databasename);
database = databasename;
if (conectionString.Contains("Provider=") == true)
{
adocon = new ADODB.Connection(conStr);
adocon.Open();
}
else if (conectionString.Contains("Driver=") == true)
{
con = new OdbcConnection(conStr);
con.Open();
}
else
{
mycon = new MySqlConnection(conStr);
mycon.Open();
}
connect();
this.getSchema();
return(true);
} catch (Exception e) {
error = e.Message.ToString();
return(false);
}
}
/// <summary>
/// Підключення до Касового Апарату
/// Number
//Номер аппарата.
//Тип: строка
//Умолчание: "1"
//Позволяет указать кассовый аппарат для работы в виде:
//Логический номер (в виде "число")
//Комбинация номер порта, сетевой номер кассы (в виде "порт;сетевойномер")
//Комбинация номер порта, сетевой номер кассы, скорость обмена (в виде "порт;сетевойномер;скорость")
//Если в момент установки значения свойства сеанс связи с аппаратом был установлен, он закрывается.
//При использовании логического номера он должен совпадать с логическим номером, запрограммированным в кассе в параметрах связи с компьютером.
//Если используется сетевой номер, то он должен совпадать с сетевым номером, запрограммированным в кассе в параметрах связи с компьютером.
//Возможные значения скорости: 9600, 19200, 38400, 57600, 115200. Однако некоторые модели касс не поддерживают скорости 57600 и 115200. Указанная скорость должна совпадать со скоростью обмена с компьютером, запрограммированной в кассе.
//Если касса подключена к компьютеру и в её настройках разрешён обмен с компьютером, то с помощью утилиты comtest.exe вы можете выяснить, на какой скорости работает касса, какой протокол она использует для обмена (упрощённый или полный) и какой имеет сетевой номер. Эти данные помогут вам правильно сформировать строку для свойства Number. Иконка comtest.exe устанавливается на рабочем столе Windows большинством инсталляторов, которые устанавливают драйверы EQL..
/// </summary>
public void Connect(string Port, string LogicNumber, string UserId, string Password)
{
connection = new Connection();
string strCnn = "Provider=EQL OLE DB Provider;";
strCnn = strCnn + "Data Source='" + Port + ";" + LogicNumber + "'";
//strCnn = strCnn + "Data Source=’" + Port + ";" + LogicNumber + "’";
strCnn = strCnn + ";User Id=" + UserId + ";Password="******";";
strCnn = strCnn + "Extended Properties = \"Protocol=" + "L2Com.HcComSessionCreator\"";
connection.Open(strCnn);
srv = new Recordset();
//відкриття службової таблиці
srv.Open("EQL_service", connection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 512);
//отримання процедурного інтерфейсу
proc = srv.Fields[1].Value;
fp = (IHcFReg)proc;
fpext = (IHcFRegEx)proc;
report = (IHcReport)proc;
display = (IHcDisplay)proc;
fiscPrinter = (IHcFiscPrnt)proc;
fiscPrinterLastInfo = (IHmLastInfo)proc;
fiscPrinterSound = (IHcSound)proc;
this.OutTone(1000, 100);
}
public ADOConnection(string connectionString) : base()
{
_connection = new ADODB.Connection();
_connection.Open(connectionString, String.Empty, String.Empty, -1);
_connection.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
SetState(SQLConnectionState.Idle);
}
public Datasource(string conStr, string conName)
{
try {
conectionString = conStr;
connectionName = conName;
if (conectionString.Contains("Provider=") == true)
{
adocon = new ADODB.Connection(conStr);
adocon.Open();
}
else if (conectionString.Contains("Driver=") == true)
{
con = new OdbcConnection(conStr);
con.Open();
}
else
{
mycon = new MySqlConnection(conStr);
mycon.Open();
}
this.getSchema();
} catch (Exception e) {
error = e.Message.ToString();
}
}
public static bool session_valid(string sdate)
{
try
{
ADODB.Connection ADOconn = new ADODB.Connection();
ADODB.Recordset tmp = new ADODB.Recordset();
SqlConnectionStringBuilder decoder = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
ADODB.Recordset rec = new ADODB.Recordset();
string sql = "SELECT FINANCE_ID FROM FINANCE_SESSION WHERE FINANCE_STATUS=1 AND '" + sdate + "' between finance_start_date and finance_end_date";
Recordset TMP = new Recordset();
TMP.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (TMP.RecordCount == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
private void cmbTableUn_SelectedIndexChanged(object sender, EventArgs e)
{
grdData.Rows.Clear();
grdData.Columns.Clear();
mycon = new ADODB.Connection();
mycon.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + clsDataStorage.db.Name);
recordSet = new ADODB.Recordset();
recordSet.Open("SELECT * FROM " + cmbTableUn.Text, mycon, CursorTypeEnum.adOpenStatic);
//clsDataStorage.db.OpenTable(cmbTableUn.Text);
foreach (ADODB.Field field in recordSet.Fields)
{
grdData.Columns.Add("clm" + field.Name, field.Name);
}
while (!recordSet.EOF)
{
grdData.Rows.Add();
for (int i = 0; i < recordSet.Fields.Count; i++)
{
grdData.Rows[r].Cells[i].Value = recordSet.Fields[i].Value;
}
r++;
recordSet.MoveNext();
}
r = 0;
recordSet.Close();
}
/// <summary>
/// Підключення до Касового Апарату
/// Number
//Номер аппарата.
//Тип: строка
//Умолчание: "1"
//Позволяет указать кассовый аппарат для работы в виде:
//Логический номер (в виде "число")
//Комбинация номер порта, сетевой номер кассы (в виде "порт;сетевойномер")
//Комбинация номер порта, сетевой номер кассы, скорость обмена (в виде "порт;сетевойномер;скорость")
//Если в момент установки значения свойства сеанс связи с аппаратом был установлен, он закрывается.
//При использовании логического номера он должен совпадать с логическим номером, запрограммированным в кассе в параметрах связи с компьютером.
//Если используется сетевой номер, то он должен совпадать с сетевым номером, запрограммированным в кассе в параметрах связи с компьютером.
//Возможные значения скорости: 9600, 19200, 38400, 57600, 115200. Однако некоторые модели касс не поддерживают скорости 57600 и 115200. Указанная скорость должна совпадать со скоростью обмена с компьютером, запрограммированной в кассе.
//Если касса подключена к компьютеру и в её настройках разрешён обмен с компьютером, то с помощью утилиты comtest.exe вы можете выяснить, на какой скорости работает касса, какой протокол она использует для обмена (упрощённый или полный) и какой имеет сетевой номер. Эти данные помогут вам правильно сформировать строку для свойства Number. Иконка comtest.exe устанавливается на рабочем столе Windows большинством инсталляторов, которые устанавливают драйверы EQL..
/// </summary>
public void Connect(string Port, string LogicNumber, string UserId, string Password)
{
connection = new Connection();
string strCnn = "Provider=EQL OLE DB Provider;";
strCnn = strCnn + "Data Source='" + Port + ";" + LogicNumber + "'";
//strCnn = strCnn + "Data Source=’" + Port + ";" + LogicNumber + "’";
strCnn = strCnn + ";User Id=" + UserId + ";Password="******";";
strCnn = strCnn + "Extended Properties = \"Protocol=" + "L2Com.HcComSessionCreator\"";
connection.Open(strCnn);
srv = new Recordset();
//відкриття службової таблиці
srv.Open("EQL_service", connection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 512);
//отримання процедурного інтерфейсу
proc = srv.Fields[1].Value;
fp = (IHcFReg)proc;
fpext = (IHcFRegEx)proc;
report = (IHcReport)proc;
display = (IHcDisplay)proc;
fiscPrinter = (IHcFiscPrnt)proc;
fiscPrinterLastInfo = (IHmLastInfo)proc;
fiscPrinterSound = (IHcSound)proc;
this.OutTone(1000, 100);
}
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 void TestCreateConectionTheSaneWay()
{
ADODB.Connection connection = new ADODB.Connection();
connection.Provider = "sqloledb";
connection.ConnectionString = String.Format("Server={0};Database={1};User Id={2};Password={3}",
this.server, this.database, this.user, this.password);
connection.ConnectComplete += new ADODB.ConnectionEvents_ConnectCompleteEventHandler(TheConnectionComplete);
connection.Open();
}
private void save_form()
{
try
{
//ADOconn.BeginTrans();
ADODB.Recordset cus = new ADODB.Recordset();
//ADOconn.BeginTrans();
if (cmbgrp.Text == "")
{
MessageBox.Show("Invalid User Group", "Invalid Entry");
return;
}
dgv1.EndEdit();
ADODB.Connection ADOconn = new ADODB.Connection();
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
for (int i = 1; i < dgv1.RowCount; i++)
{
if (dgv1["id", i].Value == null)
{
continue;
}
cus = new ADODB.Recordset();
sql = "SELECT * FROM userpriv where GROUP_NAME ='" + cmbgrp.Text + "' and form_id=" + dgv1["id", i].Value;
cus.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
if (cus.RecordCount == 0)
{
cus.AddNew();
}
cus.Fields["GROUP_NAME"].Value = cmbgrp.Text;
cus.Fields["form_id"].Value = dgv1["id", i].Value;
cus.Fields["ins"].Value = dgv1["ins", i].Value;
cus.Fields["upd"].Value = dgv1["upd", i].Value;
//cus.Fields["qry"].Value = dgv1["qry",i].Value;
cus.Fields["del"].Value = dgv1["del", i].Value;
cus.Fields["dsp"].Value = dgv1["dsp", i].Value;
cus.Fields["Menu_Name"].Value = dgv1["Menu_Name", i].Value;
cus.Update();
}
string grp = cmbgrp.Text;
sql = "SELECT distinct group_name FROM UserPriv ";
SqlDataAdapter adagrp = new SqlDataAdapter(sql, Conn);
///ada.TableMappings.Add("Table", "Leaders");
DataSet grpds = new DataSet();
adagrp.Fill(grpds, "UserPriv");
cmbgrp.DisplayMember = "group_name";
cmbgrp.DataSource = grpds.Tables[0];
cmbgrp.Text = grp;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <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);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
CatalogClass openDatabase()
{
CatalogClass catalog = new CatalogClass();
con = new Connection();
try {
con.Open(this.conString);
catalog.ActiveConnection = con;
} catch { catalog.Create(this.conString); }
return(catalog);
}
public void Connect(string path)
{
//
if (con != null)
{
return;
}
con = new ADODB.Connection();
con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";", "", "", 0);
}
/// <summary>
/// Opens the database connection.
/// </summary>
private void OpenDBConnection()
{
try
{
connection.Open();
this.Open = true;
}
catch
{
Exception e = new Exception();
throw e;
}
}
public static DataTable GetDataTable(string strFileName)
{
ADODB.Connection oConn = new ADODB.Connection();
oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";", "", "", 0);
string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
ADODB.Recordset rs = new ADODB.Recordset();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
DataTable dt = new DataTable();
rs.Open(strQuery, "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\";",
ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
adapter.Fill(dt, rs);
return dt;
}
public DevADODBConn(string DSN)
{
try
{
if (conn == null)
{
conn = new ADODB.Connection();
conn.Open(DSN, null, null, 0);
}
}
catch (Exception ex)
{
//PublicLib.handleError("", this.GetType().Name, ex.Message);
}
}
public static double Get_Currency_rate(double acc_no, string currency)
{
try
{
SqlConnectionStringBuilder decoder = new SqlConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
Conn.Close();
Conn.Open();
ADODB.Connection ADOconn = new ADODB.Connection();
ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0);
string sql;
sql = "SELECT C.currency_code as ac_currency,C.currency_rate as ac_rate,c1.currency_code, c1.currency_rate FROM currency_master AS C " +
"inner join accounts on def_currency=currency_CODE inner join currency_master as c1 on c1.currency_code='" + currency + "' WHERE acc_no=" + acc_no;
ADODB.Recordset tmp = new Recordset();
ADODB.Recordset tmp1 = new Recordset();
tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
do
{
if (tmp.Fields["ac_currency"].Value.ToString() == tmp.Fields["currency_code"].Value.ToString())
{
//return 1;
return(Convert.ToDouble(tmp.Fields["ac_rate"].Value));
}
else
{
return(Convert.ToDouble(tmp.Fields["ac_rate"].Value));
}
tmp.MoveNext();
} while (!tmp.EOF);
return(1);
}
catch (Exception ex)
{
return(1);
}
}
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 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 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);
}
private bool Connect(out string errorMessage)
{
try
{
errorMessage = "";
dbConn = new ConnectionClass();
dbConn.Open(connectionString, txtUser, txtPassword, 0);
dbConn.DefaultDatabase = strCatalogName;
return(true);
}
catch (Exception e)
{
errorMessage = e.Source + " " + e.Message;
//MessageBox.Show(e.Source + " " + e.Message);
// Disconnect();
return(false);
}
}
private ADODB.Recordset ReturnRecordset(string strSQL, ref ADODB.Connection objConnection)
{
ADODB.Recordset rsDBase = new ADODB.Recordset();
try
{
objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\\\fedex01dwyer\\fedex;Extended Properties=dBASE IV;");
// objConnection.Execute("Select * from EPOSTAGE.dbf",rsDBase);
rsDBase.Open(strSQL, objConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
}
catch
{
}
return(rsDBase);
}
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();
}
/// <summary>
/// Open connection to access database; True = Success, False = Fail
/// </summary>
/// <returns></returns>
public bool OpenConnection()
{
bool r = true;
if (con != null && con.State == 1)
{
goto END;
}
con = new ADODB.ConnectionClass();
con.ConnectionString = this.conString;
try {
con.Open();
r = con.State == 1;
} catch { r = false; }
goto END;
END:
IsConnected = r;
return(r);
}
private bool createSchema()
{
bool result = false;
string strMsg = "";
OleDbConnection conn;
ADODB.Connection adodbCon = new ADODB.Connection();
adodbCon.ConnectionString = connString;
ADOX.Catalog cat = new ADOX.Catalog();
ADOX.Table tab;
try
{
DirectoryInfo dir = new DirectoryInfo($@"{Path.GetDirectoryName(txtPathToArc.Text)}\unarc");
FileInfo[] filesH = dir.GetFiles("H*.xml");
if (filesH.Length == 0)
{
strMsg = "Файлы для обработки отсутствуют";
MessageBox.Show(strMsg);
txtLog.AppendLine(strMsg);
return(result);
}
foreach (FileInfo fi in filesH)
{
DataSet ds = new DataSet();
ds.ReadXmlSchema(fi.FullName);
foreach (string tableName in tableListH)
{
if (ds.Tables.Contains(tableName))
{
DataTable dt = ds.Tables[tableName];
foreach (DataColumn dc in dt.Columns)
{
if (!addFieldsH[tableName].Contains(dc.ColumnName))
{
addFieldsH[tableName].Add(dc.ColumnName);
}
}
}
}
}
adodbCon.Open();
cat.ActiveConnection = adodbCon;
conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
txtLog.AppendLine("Создание структуры БД");
Application.DoEvents();
string postFix = "";
if (rbTypeTo.Checked)
{
postFix = "rokb";
}
foreach (string tabName in tableListH)
{
tab = new ADOX.Table();
tab.Name = $"{tabName}{postFix}";
//id
ADOX.Column column = new ADOX.Column();
column.Name = "id";
column.Type = ADOX.DataTypeEnum.adInteger;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = true;
tab.Columns.Append(column);
foreach (string str in addFieldsH[tabName])
{
tab.Columns.Append(defCol(str));
}
cat.Tables.Append(tab);
}
//L
FileInfo[] filesL = dir.GetFiles("L*.xml");
if (filesL.Length == 0)
{
strMsg = "Файлы для обработки отсутствуют";
MessageBox.Show(strMsg);
txtLog.AppendLine(strMsg);
return(result);
}
foreach (FileInfo fi in filesL)
{
DataSet ds = new DataSet();
ds.ReadXmlSchema(fi.FullName);
foreach (string tableName in tableListL)
{
if (ds.Tables.Contains(tableName))
{
DataTable dt = ds.Tables[tableName];
foreach (DataColumn dc in dt.Columns)
{
if (!addFieldsL[tableName].Contains(dc.ColumnName))
{
addFieldsL[tableName].Add(dc.ColumnName);
}
}
}
}
}
foreach (string tabName in tableListL)
{
tab = new ADOX.Table();
tab.Name = $"{tabName}{postFix}";
//id
ADOX.Column column = new ADOX.Column();
column.Name = "id";
column.Type = ADOX.DataTypeEnum.adInteger;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = true;
tab.Columns.Append(column);
foreach (string str in addFieldsL[tabName])
{
tab.Columns.Append(defCol(str));
}
cat.Tables.Append(tab);
}
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
strMsg = "Структура БД создана";
txtLog.AppendLine(strMsg);
result = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(result);
}
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();
}
}
private bool Connect(out string errorMessage)
{
try
{
errorMessage = "";
dbConn = new ConnectionClass();
dbConn.Open(connectionString, txtUser, txtPassword, 0);
dbConn.DefaultDatabase = strCatalogName;
return true;
}
catch (Exception e)
{
errorMessage = e.Source + " " + e.Message;
//MessageBox.Show(e.Source + " " + e.Message);
// Disconnect();
return false;
}
}
public bool connect()
{
try {
if (conectionString.Contains("Provider=") == true)
{
if (adocon.State == 0)
{
adocon.Open();
return(true);
}
else if (adocon.State == 1)
{
return(true);
}
else if (adocon.State == 2)
{
return(false);
}
else if (adocon.State == 4)
{
return(true);
}
else if (adocon.State == 8)
{
return(true);
}
else
{
return(false);
}
}
else if (conectionString.Contains("Driver=") == true)
{
if (con.State == System.Data.ConnectionState.Open)
{
return(true);
}
else if (con.State == System.Data.ConnectionState.Executing)
{
return(true);
}
else if (con.State == System.Data.ConnectionState.Fetching)
{
return(true);
}
else if (con.State == System.Data.ConnectionState.Connecting)
{
return(false);
}
else if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
return(true);
}
else if (con.State == System.Data.ConnectionState.Broken)
{
con.Open();
return(true);
}
else
{
return(false);
}
}
else
{
if (mycon.State == System.Data.ConnectionState.Open)
{
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Executing)
{
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Fetching)
{
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Connecting)
{
return(false);
}
else if (mycon.State == System.Data.ConnectionState.Closed)
{
mycon.Open();
return(true);
}
else if (mycon.State == System.Data.ConnectionState.Broken)
{
mycon.Open();
return(true);
}
else
{
return(false);
}
}
} catch (Exception e) {
error = e.Message.ToString();
return(false);
}
}
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;
string ldapQry = Properties.Settings.Default.Rdg_LdapQry;
try
{
cn.Open("Provider=ADsDSOObject;");
ldapQry = ldapQry.Replace("[Rdg.LdapPath]", Properties.Settings.Default.Rdg_LdapPath);
cmd.ActiveConnection = cn;
object recs;
rs = cn.Execute(ldapQry, out recs, 0);
//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
// string 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 = wb.Worksheets[Properties.Settings.Default.Rdg_SheetName];
ws = wb.ActiveSheet;
//End If
ws.Activate();
Ribbon.ClearSheetContents();
for (int i = 0; i <= rs.Fields.Count - 1; i++)
{
ws.Cells[1, i + 1].Value = rs.Fields[i].Name;
}
ws.Range[ws.Cells[1, 1], ws.Cells[1, rs.Fields.Count]].Font.Bold = true;
ws.Range["A2"].CopyFromRecordset(rs);
Ribbon.FormatTableFromRange();
Excel.ListObject tbl = Globals.ThisAddIn.Application.ActiveCell.ListObject;
Ribbon.UpdateZeroStringToNull(tbl);
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.ribbonref.InvalidateRibbon(); //'reset dropdown lists
Ribbon.ActivateTab();
}
catch (Exception ex)
{
ErrorHandler.DisplayMessage(ex);
}
}
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 void Connect(string path)
{
//
if (con != null)
return;
con = new ADODB.Connection();
con.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";", "", "", 0);
}
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;
}
