/// <summary>
///
/// </summary>
/// <param name="queryString"></param>
/// <returns></returns>
public List <double> QueryDataReturnListDouble(string queryString)
{
if (con == null || con.State != 1)
{
this.OpenConnection();
}
if (con.State != 1)
{
return(null);
}
//open recordset
rs = new ADODB.Recordset();
rs.Open(queryString, con);
//get data
object[,] dataRows = (object[, ])rs.GetRows();
List <double> ts = new List <double>();
for (int i = 0; i < dataRows.GetLength(1); i++)
{
object[] p = Enumerable.Range(0, dataRows.GetLength(0)).Select(x => dataRows[x, i]).ToArray();
ts.Add((double)p[0]);
}
//close recordset
rs.Close();
rs = null;
//return data
return(ts);
}
public static UsuariosBO UpdateUsuario(UsuariosBO Usuario, ADODB.Connection cn)
{
var RsUsuarios = new ADODB.Recordset();
var LibORGM = new SQL();
var Serializer = new SerializerFO();
try
{
RsUsuarios.Open(String.Format("SELECT * FROM dbo.Usuarios WHERE Usuario = '{0}'", Usuario.Usuario), cn, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic);
if (RsUsuarios.EOF)
{
RsUsuarios.AddNew();
RsUsuarios.Fields["Usuario"].Value = Usuario.Usuario;
RsUsuarios.Fields["SenhaAcesso"].Value = Usuario.SenhaAcesso;
RsUsuarios.Fields["TokenAcesso"].Value = CriptografiaFO.EncriptarMD5(Usuario.TokenAcesso);
}
RsUsuarios.Fields["Inativo"].Value = Usuario.Inativo;
RsUsuarios.Update();
RsUsuarios.Close();
Usuario = UsuariosDA.GetUsuarios(Usuario.Usuario, cn);
}
catch (Exception)
{
throw;
}
return(Usuario);
}
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 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();
}
private string GetParameter(string p_sParameterName, string p_sSubSystemName, string p_sSubsystemComponent, bool p_bErrorIfNotExist)
{
//Retrieves the parameter value from the recordset.
//Throws exception if parameter not found.
ADODB.Recordset oRS = null;
Exception oEx = null;
System.String sRetVal = string.Empty;
try
{
oRS = m_oDataContext.OpenRecordset("select * from " + M_SIWGENERALPARAMATERTABLE + " where PARAM_NAME=? and SUBSYSTEM_COMPONENT = ? and SUBSYSTEM_NAME =?", ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, (System.Int32)ADODB.CommandTypeEnum.adCmdText, p_sParameterName, p_sSubSystemName, p_sSubsystemComponent);
}
catch (Exception ex)
{
if (!(oRS == null))
{
if ((int)ADODB.ObjectStateEnum.adStateOpen == oRS.State)
{
oRS.Close();
}
}
throw (ex);
}
if (oRS.BOF && oRS.EOF)
{
oRS.Close();
if (p_bErrorIfNotExist)
{
oEx = new Exception("Entry not found in " + M_SIWGENERALPARAMATERTABLE + " where PARAM_NAME = \'" + System.Convert.ToString(p_sParameterName) + "\'");
throw (oEx);
}
}
else
{
sRetVal = oRS.Fields["PARAM_VALUE"].Value.ToString();
oRS.Close();
}
return(sRetVal);
}
private void GetOH(long value)
{
string sql = "SELECT fwd_on_hand, res_on_hand FROM dbo.item WHERE itm_num =" + value;
ADODB.Recordset rs = DMSConnection.GetInstance().Recordset(sql);
if (!(rs.BOF && rs.EOF))
{
_oh = (int)rs.Fields[0].Value + (int)rs.Fields[1].Value;
}
rs.Close();
rs = null;
}
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();
}
/// <summary>
/// Get Conn Seq NextVal
/// </summary>
/// <param name="RNO"></param>
/// <returns></returns>
internal int GetConnSeqNextVal(short RNO)
{
int NodeId = 0;
ADODB.Recordset rs = m_gtApplication.DataContext.MetadataRecordset("G3E_ADD_REL_PARAMS_OPTABLE");
rs.Filter = "G3E_PARAMNAME = " + RNO + "-NodeEdgeConnectivitySequence";
//Get the nodeid sequence number
if (!(rs.Fields["G3E_PARAMVALUE"].Value is DBNull))
{
ADODB.Recordset oRSNodeID = m_gtApplication.DataContext.OpenRecordset("select " + rs.Fields["G3E_PARAMVALUE"].Value + ".nextval from dual", ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText, new Object[1]);
NodeId = Convert.ToInt32(oRSNodeID.Fields[0].Value);
oRSNodeID.Close();
}
rs.Close();
return(NodeId);
}
private bool ReplacewithSameCU(string strCucode, string strCategory)
{
ADODB.Recordset rs = null;
string[] sArrCategories = null;
string sSql = "";
try
{
if (!string.IsNullOrEmpty(strCategory))
{
sArrCategories = strCategory.Split(',');
}
sSql = "select count(*) from CULIB_UNIT where CU_ID ='" + strCucode + "' and category_c in (";
for (int i = 0; i < sArrCategories.Length; i++)
{
if (i != sArrCategories.Length - 1)
{
sSql = sSql + "'" + sArrCategories[i] + "'" + ",";
}
else
{
sSql = sSql + "'" + sArrCategories[i] + "'" + ")";
}
}
rs = m_oDataContext.OpenRecordset(sSql, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText, (int)0);
rs.MoveFirst();
if (Convert.ToString(rs.Fields[0].Value).Equals("0"))
{
return(false);
}
}
catch
{
throw;
}
finally
{
rs.Close();
rs = null;
}
return(true);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryString"></param>
/// <returns></returns>
public List <T> QueryDataReturnListObject <T>(string queryString) where T : new()
{
if (con == null || con.State != 1)
{
this.OpenConnection();
}
if (con.State != 1)
{
return(null);
}
//Get properties of T
Type itemType = typeof(T);
var properties = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
//open recordset
rs = new ADODB.Recordset();
rs.Open(queryString, con);
//get data
object[,] dataRows = (object[, ])rs.GetRows();
List <T> ts = new List <T>();
for (int i = 0; i < dataRows.GetLength(1); i++)
{
object[] p = Enumerable.Range(0, dataRows.GetLength(0)).Select(x => dataRows[x, i]).ToArray();
T t = new T();
for (int k = 0; k < properties.Length; k++)
{
properties[k].SetValue(t, Convert.ChangeType(p[k], properties[k].PropertyType));
}
ts.Add(t);
}
//close recordset
rs.Close();
rs = null;
//return data
return(ts);
}
public DataTable GetData(string strSql)
{
DataTable dt = new DataTable();
dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
try
{
ADODB.Recordset rs = new ADODB.Recordset();
rs.Open(strSql, conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, -1);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, rs);
adapter.Dispose();
rs.Close();
}
catch (Exception ex)
{
//PublicLib.handleError("", this.GetType().Name, ex.Message + string.Format("({0})", strSql));
}
return(dt);
}
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>
/// isGraphicGeoComp method returns input cno is non graphic component or not.
/// </summary>
/// <param name="cno"></param>
/// <returns></returns>
private bool IsNonGraphicGeoComp(short cno)
{
ADODB.Recordset tmprs = null;
bool isNonGraphicGeoComp = false;
try
{
tmprs = m_iGtApplication.DataContext.MetadataRecordset("G3E_COMPONENTINFO_OPTABLE");
tmprs.Filter = "G3E_CNO = " + cno;
if ((Convert.ToInt16(tmprs.Fields["g3e_type"].Value) == 1) && Convert.ToInt16(tmprs.Fields["g3e_detail"].Value) == 0)
{
isNonGraphicGeoComp = true;
}
tmprs.Close();
tmprs = null;
}
catch (Exception ex)
{
throw ex;
}
return(isNonGraphicGeoComp);
}
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);
}
public static ImpostoRetidoBO Impostos(float Valordafatura, Connection cn)
{
var RsTabelaTaxas = new ADODB.Recordset();
var Chamada = new ImpostoRetidoBO();
try
{
//Pega as taxas cadastradas da tabela "TabelaTaxas"
RsTabelaTaxas.Open(string.Format("SELECT * FROM TabelaTaxas"), cn, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockReadOnly);
if (!RsTabelaTaxas.EOF)
{
//Faz algumas verificações no valor da Fatura, faz o arredondamento para duas casas Decimais após a virgula e preenche o Objeto com os Totais.
if (Valordafatura * (double)RsTabelaTaxas.Fields["TaxaIR_Retido"].Value > 10)
{
Chamada.IR_Retido = Math.Round((Valordafatura * (double)RsTabelaTaxas.Fields["TaxaIR_Retido"].Value), 2);
}
if (Valordafatura > 5000)
{
Chamada.PIS_Retido = Math.Round((Valordafatura * (double)RsTabelaTaxas.Fields["TaxaPIS_Retido"].Value), 2);
}
Chamada.Cofins_Retido = Math.Round((Valordafatura * (double)RsTabelaTaxas.Fields["TaxaCOFINS_Retido"].Value), 2);
Chamada.CSLL_Retido = Math.Round((Valordafatura * (double)RsTabelaTaxas.Fields["TaxaCSLL_Retido"].Value), 2);
}
else
{
throw new Exception("Cadastre as Taxas dos impostos");
}
RsTabelaTaxas.Close();
return(Chamada);
}catch (Exception) {
throw;
}
}
public void SearchCommentsData()
{
string sql;
string sDate;
string sQuery;
int i;
if (txt_DATE.RawDate.Length != 8)
{
sDate = DateTime.Now.AddDays(-1).ToString("yyyyMMdd");
}
else
{
sDate = txt_DATE.RawDate;
}
sql = " SELECT YEAR_PLAN_WGT , YEAR_FIN_WGT , YEAR_PROD_PROG , ";
sql = sql + " YEAR_PROG , YEAR_AVE_NEED , YEAR_LEFT_DAY , ";
sql = sql + " NULL , ";
sql = sql + " MONTH_PLAN_WGT , MONTH_FIN_WGT , MONTH_PROD_PROG , ";
sql = sql + " MONTH_PROG , MONTH_AVE_NEED , MONTH_LEFT_DAY , ";
sql = sql + " NULL ";
sql = sql + " FROM gp_zb_mon ";
sql = sql + " WHERE PLT = 'C3' ";
sql = sql + " AND PROD_DATE = '" + sDate + "' ";
sQuery = sql;
if (GeneralCommon.M_CN1.State == 0)
{
if (!GeneralCommon.GF_DbConnect())
{
return;
}
}
ADODB.Recordset AdoRs = new ADODB.Recordset();
try
{
AdoRs.Open(sQuery, GeneralCommon.M_CN1, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly);
while (!AdoRs.EOF)
{
for (int j = 6; j <= 7; j++)
{
for (i = 2; i <= 14; i += 2)
{
if (j == 6)
{
if (!(AdoRs.Fields[i / 2 - 1].Value == null || convertD(AdoRs.Fields[i / 2 - 1].Value.ToString()) == 0))
{
ss3.ActiveSheet.Cells[j - 1, i - 1].Text = AdoRs.Fields[i / 2 - 1].Value.ToString();
}
}
else
{
if (!(AdoRs.Fields[i / 2 + 6].Value == null | convertD(AdoRs.Fields[i / 2 + 6].Value.ToString()) == 0))
{
ss3.ActiveSheet.Cells[j - 1, i - 1].Text = AdoRs.Fields[i / 2 + 6].Value.ToString();
}
}
}
}
AdoRs.MoveNext();
}
AdoRs.Close();
AdoRs = null;
//判断是不是需要关闭连接对象，有时候该方法是在查询过程中调用，关闭对象会导致框架查询报错 韩超
GeneralCommon.M_CN1.Close();
}
catch (Exception ex)
{
// if (GeneralCommon.M_CN1.State != 0) GeneralCommon.M_CN1.Close();
AdoRs.Close();
AdoRs = null;
}
}
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);
}
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;
}
/// <summary>
/// Відключення від Касового Апарату
/// </summary>
public void Disconnect()
{
srv.Close();
}
private void ExecuteSql(ThreadObj Param)
{
string query = Param.Query;
// new recordset reference
ADODB.Recordset Data = null;
// keep a connection reference
ADODB.Connection Connect = null;
try
{
// change cursor to wait cursor
this.Invoke(ChangeCursorDel, new object[] { Cursors.WaitCursor });
// initialize the message string to be empty
string Message = "";
string Warnings = "";
// keep track of the max number of columns
int nMaxNumCols = 0;
// fill in a data table with our results
DataTable SqlResults = new DataTable();
// open the connection
Connect = new Connection();
Connect.ConnectionString = Param.ConnectionString;
Connect.CursorLocation = CursorLocationEnum.adUseClient;
Connect.Open();
// initialize an object to store the number of records affected
object obRecsAffected;
// number of queries returned so far is none
int nQueriesReturned = 0;
// construct a command object
ADODB.Command SqlCmd = new Command();
SqlCmd.ActiveConnection = Connect;
SqlCmd.CommandText = (string)query;
SqlCmd.CommandType = CommandTypeEnum.adCmdText;
// execute the sql statement
Data = Connect.Execute((string)query, out obRecsAffected);
// while there are recordsets
while (Data != null)
{
// for this query, print out all the errors that occurred
foreach (ADODB.Error e in Connect.Errors)
{
Warnings += e.Description + "\r\n";
}
// we have another query, increment the count
nQueriesReturned++;
// if there is something to read (hence the object is not closed)...
if ((ADODB.ObjectStateEnum)Data.State != ADODB.ObjectStateEnum.adStateClosed)
{
// count of rows retrieved
int nRowsRecieved = 0;
// if this recordset has more columns in it than previous columns
for (int i = nMaxNumCols; i < Data.Fields.Count; i++)
{
// add a column and increment the max col count
DataColumn temp = new DataColumn(Data.Fields[i].Name, typeof(string));
SqlResults.Columns.Add(temp);
nMaxNumCols++;
}
// grab the current time in ticks
long InitialTime = DateTime.Now.Ticks;
// while there is stuff in the recordset
while (!Data.EOF)
{
// create a new row
DataRow tempRow = SqlResults.NewRow();
// fill each column with data
for (int i = 0; i < Data.Fields.Count; i++)
{
tempRow[i] = Data.Fields[i].Value.ToString();
}
// add it to the results
SqlResults.Rows.Add(tempRow);
nRowsRecieved++;
// if the user didnt cancel, display the record number being loaded, otherwise the user did cancel so stop doing work
if (!m_bUserCancel)
{
this.Invoke(SetResultsDel, new object[] { String.Format("Loading Record {0} in Query {1}...", nRowsRecieved, nQueriesReturned), false });
}
else
{
this.Invoke(SetResultsDel, new object[] { "Canceling... Please wait...", false });
break;
}
// continue to the next record in the recordset
Data.MoveNext();
}
// the time elapsed is the time now minus the initial
double TimeElapsed = ((float)(DateTime.Now.Ticks - InitialTime)) / 10000000.0;
// add the number of rows returned and how long it took to the messages to display once the results are ready to be displayed
Message += nQueriesReturned + ": " + nRowsRecieved + " row(s) returned. " + String.Format("({0:0.0000} seconds)", TimeElapsed) + "\r\n";
}
else
{
// otherwise, it was a non action query, so records must have been affected
Message += nQueriesReturned + ": " + (int)obRecsAffected + " record(s) affected.\r\n";
}
// if the user canceled, just return the form to its original state and state that the user canceled
if (m_bUserCancel)
{
this.Invoke(SetUpFormDel, new object[] { true });
this.Invoke(ShowResultsDel, new object[] { SqlResults, Message });
this.Invoke(SetResultsDel, new object[] { "Loading Was Canceled by User.", true });
return;
}
// try to go to the next recordset
try
{
Data = Data.NextRecordset(out obRecsAffected);
}
catch (COMException ex)
{
// if for some reason, the provider doesnt like that, just break out on the first recordset
if (ex.ErrorCode == -2146825037)
{
Data = null;
}
else
{
// otherwise, just rethrow our excetion, its a bigger problem
throw ex;
}
}
}
this.Invoke(SetMessageDel, new object[] { Warnings.Trim() });
// after all records have been run through, go ahead and show the results
this.Invoke(ShowResultsDel, new object[] { SqlResults, Message });
}
catch (Exception ex)
{
string ErrorText = "";
if (Connect.Errors.Count > 0)
{
// for this query, print out all the errors that occurred
foreach (ADODB.Error e in Connect.Errors)
{
ErrorText += "Error " + e.Number + ": " + e.Description + "\r\n";
}
Connect.Errors.Clear();
}
else
{
ErrorText = "Error: " + ex.Message;
}
// if any exception does happen, dont display results, but print out the error
this.Invoke(SetResultsDel, new object[] { ErrorText.Trim(), true });
this.Invoke(SetUpFormDel, new object[] { true });
// change cursor to default cursor
this.Invoke(ChangeCursorDel, new object[] { Cursors.Default });
return;
}
finally
{
if (Data != null && Data.State != (int)ConnectionState.Closed)
{
Data.Close();
}
}
}
private void btnConnect_Click(object sender, EventArgs e)
{
if (txtConnStr.Text.Trim().Length == 0)
{
MessageBox.Show("Connection string cannot be null!");
return;
}
if (btnConnect.Text == "Disconnect")
{
EnableGenEntity(false);
return;
}
RefreshConnectionStringAutoComplete();
DataSet dsTables = null;
DataSet dsViews = null;
if (radioSql.Checked || radioAccess.Checked)
{
try
{
if (radioSql.Checked)
{
Gateway.SetDefaultDatabase(DatabaseType.SqlServer, txtConnStr.Text);
if (checkSql2005.Checked)
{
dsTables = Gateway.Default.SelectDataSet("select [name] from sysobjects where xtype = 'U' and [name] <> 'sysdiagrams' order by [name]", null);
}
else
{
dsTables = Gateway.Default.SelectDataSet("select [name] from sysobjects where xtype = 'U' and status > 0 order by [name]", null);
}
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["Name"].ToString());
}
if (checkSql2005.Checked)
{
dsViews = Gateway.Default.SelectDataSet("select [name] from sysobjects where xtype = 'V' order by [name]", null);
}
else
{
dsViews = Gateway.Default.SelectDataSet("select [name] from sysobjects where xtype = 'V' and status > 0 order by [name]", null);
}
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["Name"].ToString());
}
}
else if (radioAccess.Checked)
{
Gateway.SetDefaultDatabase(DatabaseType.MsAccess, txtConnStr.Text);
ADODB.ConnectionClass conn = new ADODB.ConnectionClass();
conn.Provider = "Microsoft.Jet.OLEDB.4.0";
string connStr = txtConnStr.Text;
conn.Open(connStr.Substring(connStr.ToLower().IndexOf("data source") + "data source".Length).Trim('=', ' '), null, null, 0);
ADODB.Recordset rsTables = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaTables }) as ADODB.Recordset;
ADODB.Recordset rsViews = conn.GetType().InvokeMember("OpenSchema", BindingFlags.InvokeMethod, null, conn, new object[] { ADODB.SchemaEnum.adSchemaViews }) as ADODB.Recordset;
while (!rsViews.EOF)
{
if (!(rsViews.Fields["TABLE_NAME"].Value as string).StartsWith("MSys"))
{
views.Items.Add(rsViews.Fields["TABLE_NAME"].Value.ToString());
}
rsViews.MoveNext();
}
while (!rsTables.EOF)
{
if (!(rsTables.Fields["TABLE_NAME"].Value as string).StartsWith("MSys"))
{
bool isView = false;
foreach (string item in views.Items)
{
if (item.Equals(rsTables.Fields["TABLE_NAME"].Value.ToString()))
{
isView = true;
break;
}
}
if (!isView)
{
tables.Items.Add(rsTables.Fields["TABLE_NAME"].Value.ToString());
}
}
rsTables.MoveNext();
}
rsTables.Close();
rsViews.Close();
conn.Close();
}
EnableGenEntity(true);
}
catch (Exception ex)
{
EnableGenEntity(false);
MessageBox.Show("Read/write database error!\r\n" + ex.ToString());
}
}
else if (radioOracle.Checked)
{
Gateway.SetDefaultDatabase(DatabaseType.Oracle, txtConnStr.Text);
dsTables = Gateway.Default.SelectDataSet("select * from user_tables where global_stats = 'NO' and (not table_name like '%$%')", null);
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
dsViews = Gateway.Default.SelectDataSet("select * from user_views where (not view_name like '%$%') and (not view_name like 'MVIEW_%') and (not view_name like 'CTX_%') and (not view_name = 'PRODUCT_PRIVS')", null);
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["VIEW_NAME"].ToString());
}
EnableGenEntity(true);
}
else if (radioMySql.Checked)
{
System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex("(^.*database=)([^;]+)(;.*)", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
string dbName = r.Replace(txtConnStr.Text, "$2").ToLower();
Gateway.SetDefaultDatabase(DatabaseType.MySql, r.Replace(txtConnStr.Text, "$1information_schema$3"));
dsTables = Gateway.Default.SelectDataSet("select * from TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = '" + dbName + "'", null);
foreach (DataRow row in dsTables.Tables[0].Rows)
{
tables.Items.Add(row["TABLE_NAME"].ToString());
}
dsViews = Gateway.Default.SelectDataSet("select * from TABLES where TABLE_TYPE = 'VIEW' and TABLE_SCHEMA = '" + dbName + "'", null);
foreach (DataRow row in dsViews.Tables[0].Rows)
{
views.Items.Add(row["TABLE_NAME"].ToString());
}
EnableGenEntity(true);
}
else
{
EnableGenEntity(false);
MessageBox.Show("EntityGen tool only supports SqlServer, MsAccess, MySql and Oracle Database!");
}
}
private void frmMain_Load(object sender, EventArgs e)
{
ADODB.Recordset rsMail = null;
ADODB.Recordset rsContract = null;
ADODB.Recordset rsMailSettings = null;
ADODB.Connection connDb = null;
bool solde = false;
string destinataire = "";
bool contratEchu = false;
string CONN_DB = "";
// if (args==null)
// Application.Exit();
if (args == null)
{
CONN_DB = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\lementor\StudiosUnis\LeMentorÉlèveTables.mdb;";
}
else
{
CONN_DB = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + args;
}
try
{
connDb = new ADODB.Connection();
rsMail = new ADODB.Recordset();
rsMailSettings = new ADODB.Recordset();
rsContract = new ADODB.Recordset();
System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
client.UseDefaultCredentials = false;
client.EnableSsl = true;
client.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
System.Net.Mail.MailMessage msg = null;
connDb.Open(CONN_DB);
rsMail.Open("select * from emailmessage", connDb, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic);
rsMailSettings.Open("select * from cie", connDb, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic);
if (!rsMail.EOF)
{
rsMail.MoveFirst();
}
if (!rsMailSettings.EOF)
{
rsMailSettings.MoveFirst();
client.Port = rsMailSettings.Fields["PortSmtp"].Value; // rsMailSettings.Fields["PortSmtp"].Value;
client.Host = rsMailSettings.Fields["ServerSmtp"].Value.ToString();
client.Credentials = new System.Net.NetworkCredential(rsMailSettings.Fields["E-mail"].Value.ToString(), rsMailSettings.Fields["MotdePasseE-mail"].Value);
}
while (!rsMail.EOF)
{
solde = rsMail.Fields["solde"].Value;
contratEchu = rsMail.Fields["contratechu"].Value;
//destinataire = rsMail.Fields["destinataire"].Value.ToString();
// destinataire = "dquirion78@@gmail.com";
msg = new System.Net.Mail.MailMessage();
msg.From = new MailAddress(rsMailSettings.Fields["e-mail"].Value.ToString());
msg.Subject = rsMail.Fields["sujet"].Value.ToString();
msg.Body = rsMail.Fields["texte"].Value.ToString();
msg.IsBodyHtml = false;
if (string.IsNullOrEmpty(rsMail.Fields["destinataire"].Value.ToString()))
{
throw new Exception("Destinataire Manquant!");
}
else
{
msg.To.Add(destinataire);
}
if (!string.IsNullOrEmpty(rsMail.Fields["cc"].Value.ToString()))
{
msg.CC.Add(rsMail.Fields["cc"].Value.ToString());
}
if (!string.IsNullOrEmpty(rsMail.Fields["cci"].Value.ToString()))
{
msg.Bcc.Add(rsMail.Fields["cci"].Value.ToString());
}
if (rsMail.Fields["fichier"].Value.ToString().Length > 3)
{
System.Net.Mail.Attachment attachment;
attachment = new System.Net.Mail.Attachment(rsMail.Fields["fichier"].Value.ToString());
msg.Attachments.Add(attachment);
}
client.Send(msg);
// if (rsMail.Fields["fichier"].Value.ToString().Length > 3)
// File.Delete(rsMail.Fields["fichier"].Value.ToString());
msg = null;
rsContract.Open("select * from GA_Eleve_Cours where [noidcours] =" + rsMail.Fields["noidcours"].Value.ToString(), connDb, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic);
if (!rsContract.EOF)
{
rsContract.MoveFirst();
if (rsMail.Fields["ContratEchu"].Value == true)
{
rsContract.Fields["NotficationContratEchu"].Value = true;
}
if (rsMail.Fields["solde"].Value == true)
{
rsContract.Fields["DernDateNotificationsolde"].Value = rsMail.Fields["DernDateNotificationsolde"].Value;
rsContract.Fields["NbreNotificationsolde"].Value = rsMail.Fields["NbreNotificationsolde"].Value;
}
rsContract.Update();
}
rsContract.Close();
rsMail.Delete();
rsMail.MoveNext();
if (!rsMail.EOF)
{
System.Threading.Thread.Sleep(5000);//limite les chances d'être taggé comme spam par le fournisseur d'envoi en envoyant pas trop vite les emails.
}
//msg.CC.Add(Email);
}
/*
* if (Attachments.Count() > 0)
* {
* foreach (var item in Attachments)
* {
* if (!string.IsNullOrEmpty(item))
* {
* System.Net.Mail.Attachment attachment;
* attachment = new System.Net.Mail.Attachment(item);
* msg.Attachments.Add(attachment);
* }
* }
* }
*/
if (((solde == false && contratEchu == false) || (rsMail.RecordCount == 1)) && destinataire.Length > 0)
{
MessageBox.Show("Message Envoyé : à " + destinataire);
Application.Exit();
}
else
{
// MessageBox.Show("Message Envoyé : à " + rsMail.RecordCount.ToString() + " clients.");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
MessageBox.Show("ÉCHEC d'envoi de courier !");
Application.Exit();
}
try
{
if (rsMail.State != 0)
{
rsMail.Close();
}
rsMail = null;
if (rsMailSettings.State != 0)
{
rsMailSettings.Close();
}
rsMailSettings = null;
if (rsContract.State != 0)
{
rsContract.Close();
}
rsContract = null;
Application.Exit();
}
catch
{
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;
}
private void App_ActiveJobChanged(object sender, GTActiveJobChangedEventArgs e)
{
string jobStatus = null;
string wmisStatus = null;
string Job = e.ActiveJob;
if (string.IsNullOrEmpty(Job)) //if no job is active, do nothing
{
return;
}
IGTApplication app = (IGTApplication)sender;
IGTDataContext dc = app.DataContext;
string SQL = "select G3E_JOBSTATUS, WMIS_STATUS_C , G3E_ENABLELOGGING FROM G3E_JOB WHERE G3E_IDENTIFIER=?";
ADODB.Recordset RS = dc.OpenRecordset(SQL, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText, Job);
if (RS != null && RS.RecordCount == 1)
{
RefreshLoggingProperty(app, Convert.ToString(RS.Fields["G3E_ENABLELOGGING"].Value));
//this section handles various WMIS statuses, then deactivates the job and skips all remaining steps.
wmisStatus = Convert.ToString(RS.Fields["WMIS_STATUS_C"].Value).ToUpper().Trim();
if (wmisStatus != "SUCCESS" && wmisStatus != "FAILURE" && !string.IsNullOrEmpty(wmisStatus))
{
if (InteractiveMode)
{
switch (wmisStatus)
{
case "WRITEBACK":
MessageBox.Show("Job cannot be activated while a writeback operation is in progress.", "G/Technology", MessageBoxButtons.OK);
break;
case "BATCH":
MessageBox.Show("Job cannot be activated while a batch operation is in progress.", "G/Technology", MessageBoxButtons.OK);
break;
case "STATUSCHG":
MessageBox.Show("Job cannot be activated while a status change is being communicated to WMIS.", "G/Technology", MessageBoxButtons.OK);
break;
case "ERROR":
MessageBox.Show("Job cannot be activated due to an outstanding error; please notify Support.", "G/Technology", MessageBoxButtons.OK);
break;
default:
break;
}
}
DeactivateJob(Job); //deactivate job
return; //skip remaining actions (job status section)
}
//this section handles various job statuses
jobStatus = Convert.ToString(RS.Fields["G3E_JOBSTATUS"].Value).ToUpper().Trim();
if (!string.IsNullOrEmpty(jobStatus))
{
switch (jobStatus)
{
case "APPROVALPENDING":
if (InteractiveMode)
{
DialogResult ApprovalPendingResult = MessageBox.Show("WR is marked for approval. Unmark?", "G/Technology", MessageBoxButtons.YesNo);
if (ApprovalPendingResult == DialogResult.Yes)
{
//change job status to Design and leave job active
WMISStatus wMISStatus = new WMISStatus();
wMISStatus.SetJobStatus(Job, "Design");
}
if (ApprovalPendingResult == DialogResult.No)
{
MessageBox.Show("Job is being deactivated and the WR will remain marked for approval.", "G/Technology", MessageBoxButtons.OK);
DeactivateJob(Job); //deactivate job
}
}
else // UnattendedMode
{
//change job status to Design and leave job active
WMISStatus wMISStatus = new WMISStatus();
wMISStatus.SetJobStatus(Job, "Design");
}
break;
case "DESIGN":
bool hasAlternateDesign = false; //initialize to false. This will be set in the if statements below if an alternate design exists for the job.
//this if/else determines if the active job contains a "-" character. Whether this is true or not determines what the SQL statement looks like.
if (Job.Contains("-"))
{
string jobSubstring = Job.Substring(0, Job.IndexOf("-"));
SQL = "select G3E_IDENTIFIER, G3E_JOBSTATUS from G3E_JOB where G3E_IDENTIFIER like ? and G3E_JOBSTATUS=?";
RS = dc.OpenRecordset(SQL, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText, jobSubstring + "-%", "ApprovalPending");
}
else
{
SQL = "select G3E_IDENTIFIER, G3E_JOBSTATUS from G3E_JOB where G3E_IDENTIFIER like ? and G3E_JOBSTATUS=?";
RS = dc.OpenRecordset(SQL, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText, Job + "-%", "ApprovalPending");
}
if (RS != null && RS.RecordCount > 0) //check to see if recordset from above if/else has records. If yes, this job has an alternate design.
{
hasAlternateDesign = true;
}
if (hasAlternateDesign)
{
string altJobIdentifier = Convert.ToString(RS.Fields["G3E_IDENTIFIER"].Value);
if (InteractiveMode)
{
DialogResult DesignResult = MessageBox.Show("An alternate design for this WR is marked for approval. Unmark it so that this alternate may be activated?", "G/Technology", MessageBoxButtons.YesNo);
if (DesignResult == DialogResult.Yes)
{
//update job status to Design for the alternate design, and leave the current job active
WMISStatus wMISStatus = new WMISStatus();
wMISStatus.SetJobStatus(altJobIdentifier, "Design");
}
if (DesignResult == DialogResult.No)
{
MessageBox.Show("Job is being deactivated and the alternate design will remain marked for approval.", "G/Technology", MessageBoxButtons.OK);
DeactivateJob(Job); //deactivate job
}
}
else // UnattendedMode
{
//update job status to Design for the alternate design, and leave the current job active
WMISStatus wMISStatus = new WMISStatus();
wMISStatus.SetJobStatus(altJobIdentifier, "Design");
}
}
break;
case "CLOSUREPENDING":
//SQL = "update G3E_JOB set G3E_JOBSTATUS='ConstructionComplete' where G3E_IDENTIFIER=?";
if (InteractiveMode)
{
DialogResult ClosurePendingResult = MessageBox.Show("WR is marked for closure. Unmark?", "G/Technology", MessageBoxButtons.YesNo);
if (ClosurePendingResult == DialogResult.Yes)
{
//change job status to ConstructionComplete and leave job active
WMISStatus wMISStatus = new WMISStatus();
wMISStatus.SetJobStatus(Job, "ConstructionComplete");
}
if (ClosurePendingResult == DialogResult.No)
{
MessageBox.Show("Job is being deactivated and the WR will remain marked for closure.", "G/Technology", MessageBoxButtons.OK);
DeactivateJob(Job); //deactivate job
}
}
else // UnattendedMode
{
//change job status to ConstructionComplete and leave job active
WMISStatus wMISStatus = new WMISStatus();
wMISStatus.SetJobStatus(Job, "ConstructionComplete");
}
break;
default:
break;
}
}
CommonWorkPointDisplayQuery workPointDisplayQuery = new CommonWorkPointDisplayQuery(app);
workPointDisplayQuery.RedisplayWorkPoints();
}
RS.Close();
}
