Ejemplo n.º 1
0
    public bool DoLogin(string username, string password, bool IsAdmin)
    {
        OleDbCommand com = new OleDbCommand("", MySchool.SLConnection);
        bool ReturnMe = false;
        if (IsAdmin)
            com.CommandText = "select UID, UPass from LoginInfo where UName = '" + username + "' and UPass = '******'";
        else
            com.CommandText = "select UID, pass from UserInfo where UserName = '******' and Pass ='******'";

        com.Connection.Open();

        if (com.ExecuteScalar() != null)
        {
            if (IsAdmin)
            {
                TheSessions.EmploeeID = com.ExecuteScalar().ToString();
                TheSessions.EmploeeName = username;
                TheSessions.IsAdmin = true;
            }
            else
            {
                TheSessions.StudentID = com.ExecuteScalar().ToString();
                TheSessions.StudentName = username;
                TheSessions.IsAdmin = false;
            }
            ReturnMe = true;
        }
        else
            ReturnMe = false;

        com.Connection.Close();
        return ReturnMe;
    }
Ejemplo n.º 2
0
        internal static int GetApplicationID(OleDbConnection connection, string applicationName, bool createIfNeeded)
        {
            OleDbCommand lookupCommand = new OleDbCommand("SELECT ApplicationId FROM aspnet_Applications WHERE ApplicationName = @AppName", connection);
            lookupCommand.Parameters.Add(new OleDbParameter("@AppName", applicationName));

            object lookupResult = lookupCommand.ExecuteScalar();
            if ((lookupResult != null) && (lookupResult is int)) {
                return (int)lookupResult;
            }

            if (createIfNeeded) {
                OleDbCommand createCommand = new OleDbCommand("INSERT INTO aspnet_Applications (ApplicationName) VALUES (@AppName)",
                    connection);
                createCommand.Parameters.Add(new OleDbParameter("@AppName", applicationName));

                if (createCommand.ExecuteNonQuery() != 0) {
                    lookupResult = lookupCommand.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is int)) {
                        return (int)lookupResult;
                    }
                }
            }

            return 0;
        }
Ejemplo n.º 3
0
  public static bool FormsAuthenticate(string userName, string password)
  {
    bool authenticated = false;

    switch (Mode)
    {
      case AuthenticationMode.None:
        authenticated = FormsAuthentication.Authenticate(userName, password);
        break;

      case AuthenticationMode.Database:
        using (OleDbConnection connection = AppContext.GetDatabaseConnection())
        {
          string format = String.Format("select count(*) from {0}User where UserName = '******' and Password = '******' and Active = 1", AppSettings.ConfigurationTablePrefix, userName);

          using (OleDbCommand command = new OleDbCommand(String.Format(format, password), connection))
          {
            authenticated = Convert.ToInt32(command.ExecuteScalar()) > 0;

            if (!authenticated)
            {
              command.CommandText = String.Format(format, HashPassword(password));
              authenticated = Convert.ToInt32(command.ExecuteScalar()) > 0;
            }
          }
        }
        break;
    }

    return authenticated;
  }
Ejemplo n.º 4
0
    protected void Page_Load(object sender, EventArgs e)
    {
        //TheSessions.StudentID = "494108";

        DataTable DT = new DataTable("ReadedBooks");
        OleDbConnection AccessCon = MySchool.LibraryCodeConnection;
        OleDbCommand AccessCMD = new OleDbCommand("", AccessCon);
        try
        {
            if (TheSessions.StudentID != null)
            {
                OleDbDataAdapter AccessDA = new OleDbDataAdapter("SELECT motaraded.[number], book.name_book, motaraded.[date], book.code_printl, '' As name_printl, book.code_fari, '' As name_fari, book.code_tahasos, '' As name_tahasos, book.code_alnasher, '' As name_alnasher, (SELECT TOP 1 fm1 + ' ' + lm1 AS msl FROM masol WHERE ([number] = book.[number]) AND (k1 = 'مؤلف')) As masol FROM (motaraded INNER JOIN book ON motaraded.[number] = book.[number]) WHERE (motaraded.code_name = " + TheSessions.StudentID + ")", MySchool.LibraryConnection);
                AccessDA.Fill(DT);
            }

            AccessCon.Open();
            foreach (DataRow DR in DT.Rows)
            {
                if (DR["code_printl"].ToString() != string.Empty)
                {
                    AccessCMD.CommandText = "Select name_printl from printl Where code_printl = " + DR["code_printl"].ToString();
                    DR["name_printl"] = AccessCMD.ExecuteScalar();
                }
                else { DR["name_printl"] = string.Empty; }

                if (DR["code_fari"].ToString() != string.Empty)
                {
                    AccessCMD.CommandText = "Select name_fari from fari Where code_fari = " + DR["code_fari"].ToString();
                    DR["name_fari"] = AccessCMD.ExecuteScalar();
                }
                else
                { DR["name_fari"] = string.Empty; }
                if (DR["code_tahasos"].ToString() != string.Empty)
                {
                    AccessCMD.CommandText = "Select name_tahasos from tahasos Where code_tahasos = " + DR["code_tahasos"].ToString();
                    DR["name_tahasos"] = AccessCMD.ExecuteScalar();
                }
                else
                { DR["name_tahasos"] = string.Empty; }
                if (DR["code_alnasher"].ToString() != string.Empty)
                {
                    AccessCMD.CommandText = "Select name_alnasher from alnasher Where code_alnasher = " + DR["code_alnasher"].ToString();
                    DR["name_alnasher"] = AccessCMD.ExecuteScalar();
                }
                else
                { DR["name_alnasher"] = string.Empty; }
            }
            AccessCon.Close();

            GridViewReadedBooks.DataSource = DT;
            ViewState["ReadedBooksTable"] = DT;
            GridViewReadedBooks.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message.ToString());
        }
    }
Ejemplo n.º 5
0
        /// <summary>
        /// Generic method for issuing simple Insert commands to the current VWA4 database,
        /// based on provided connection.
        /// </summary>
        /// <param name="sql">SQL query string to execute.</param>
        /// <param name="conn">OleDbConnection to desired database.</param>
        /// <param name="trans"></param>
        /// <returns>PK/ID of inserted record.</returns>
        public static int Insert(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
        {
            // Connect up to the database
            int id = -1;

            try
            {
                // Tee up the command
                System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                //
                // Issue the command
                //
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                int iSqlStatus = cmd.ExecuteNonQuery();
                //Now check the status
                if (iSqlStatus <= 0)
                {
                    //DO your failed messaging here
                    MessageBox.Show(null, "VWA4.NET INSERT ERROR: Query Failed to insert with status: " + iSqlStatus, "Insert Error");
                }
                cmd.CommandText = "SELECT @@Identity";
                id = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show(null, "VWA4.NET INSERT Exception: Error " + ex.Message + "!", "Insert Error",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return(id);
        }
Ejemplo n.º 6
0
        /// <summary>取得Scalar物件</summary>
        /// <param name="strSQL">SQL 指令</param>
        /// <param name="OleDbConn">OleDbConnection 連線物件(不指定表使用預設連線)</param>
        /// <returns>Object</returns>
        /// <remarks></remarks>
        public static object getOleDbScalar(string strSQL, System.Data.OleDb.OleDbConnection OleDbConn)
        {
            System.Data.OleDb.OleDbCommand cmd = null;
            object objScalar = null;

            try
            {
                if (OleDbConn == null)
                {
                    OleDbConn = createOleDbConnection();
                }
                if (!(OleDbConn.State == ConnectionState.Open))
                {
                    OleDbConn.Open();
                }
                cmd       = new System.Data.OleDb.OleDbCommand(strSQL, OleDbConn);
                objScalar = cmd.ExecuteScalar();
            }
            catch (Exception Ex)
            {
                //Message.alertMessage("C0001", null, Ex.Message.ToString(), null);
                objScalar = null;
            }
            finally
            {
                if (!(OleDbConn.State == ConnectionState.Closed))
                {
                    OleDbConn.Close();
                }
            }
            return(objScalar);
        }
 public static int? isStudentRegistered(int? sessionID, int? year, int? term, int? stdID)
 {
     OleDbConnection connection = new OleDbConnection(global::ResultManagement.Properties.Settings.Default.ConnectionString);
     int? Registered_Session_ID = null;
     string cmdStr = @"SELECT Registered_Session.ID
                     FROM Registered_Session
                     WHERE
                     Std_ID = @Std_ID AND
                     Sess_ID = @Sess_ID AND
                     Reg_Year = @Reg_Year AND
                     Reg_Term = @Reg_Term";
     OleDbCommand cmd = new OleDbCommand(cmdStr, connection);
     cmd.Parameters.Add("@Std_ID", OleDbType.Integer).Value = stdID;
     cmd.Parameters.Add("@Sess_ID", OleDbType.Integer).Value = sessionID;
     cmd.Parameters.Add("@Reg_Year", OleDbType.Integer).Value = year;
     cmd.Parameters.Add("@Reg_Term", OleDbType.Integer).Value = term;
     try
     {
         connection.Open();
         Registered_Session_ID = (int?)cmd.ExecuteScalar();
     }
     finally
     {
         connection.Close();
     }
     return Registered_Session_ID;
 }
Ejemplo n.º 8
0
    public string LoadPage()
    {
        LblError.Visible = false;
        //Load data from file to RadEditor
        string Path = string.Empty;
        OleDbConnection Con = MySchool.QAConnection;
        OleDbCommand CMD = new OleDbCommand("SELECT Data_Path FROM QAMenu Where QAID = " + QAMainMenu.SelectedValue.ToString(), Con);
        try
        {
            Con.Open();
            Path = CMD.ExecuteScalar().ToString();

        }
        catch { }
        Con.Close();
        if (Path == string.Empty)
        {
            //HtmlEditorQA.Html = string.Empty;
            return string.Empty;
        }
        Path = MapPath(Path);
        if (!File.Exists(Path))
        {
            return string.Empty;
        }
        else
        {
            TextReader TR = new StreamReader(Path);
            return TR.ReadToEnd().ToString();
        }
    }
Ejemplo n.º 9
0
    public int EnterToSite(UserDetails userDetails)//בודק על פי האימייל והסיסמא האם המשתשמש שמנסה להיכנס לאתר קיים או לא
    {
        OleDbCommand myCmd = new OleDbCommand("CheckIfUserExist", myConnection);
        myCmd.CommandType = CommandType.StoredProcedure;

        OleDbParameter objParam;

        objParam = myCmd.Parameters.Add("@Email", OleDbType.BSTR);
        objParam.Direction = ParameterDirection.Input;
        objParam.Value = userDetails.email;

        objParam = myCmd.Parameters.Add("@Password", OleDbType.BSTR);
        objParam.Direction = ParameterDirection.Input;
        objParam.Value = userDetails.password;

        int x = 0;
        try
        {
            myConnection.Open();
            x = (int)myCmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            myConnection.Close();
        }
        return x;

    }
 //--function for returning a value queried by the user witht the help of an datareader--->
 public string  execute_scalar(string s)
 {   this.connect();
     OleDbCommand cmd = new OleDbCommand(s, this.con);
     return_result = Convert.ToString(cmd.ExecuteScalar());
     this.disconnect();
     return return_result;
  }
Ejemplo n.º 11
0
  private static string GetRole(OleDbConnection connection)
  {
    IPrincipal user = HttpContext.Current.User;
    string role = "public";

    if (user.Identity != null && user.Identity.IsAuthenticated)
    {
      if (AppAuthentication.Mode == AuthenticationMode.None)
      {
        role = "admin";
      }
      else
      {
        string sql = String.Format("select Role from {0}User where UserName = '******' and Role is not null",
            AppSettings.ConfigurationTablePrefix, user.Identity.Name);
        OleDbCommand command = new OleDbCommand(sql, connection);
        role = command.ExecuteScalar() as string;

        if (String.IsNullOrEmpty(role))
        {
          role = "private";
        }
      }
    }

    return role;
  }
Ejemplo n.º 12
0
        public static int Add(SachDto sach)
        {
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();

            string strSQL;
            strSQL = "Insert into Sach(TenSach,LoaiSach,TacGia,TrangThai,SoTrang,NgonNgu) values (?,?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@TenSach", OleDbType.WChar);
            cmd.Parameters.Add("@LoaiSach", OleDbType.Integer);
            cmd.Parameters.Add("@TacGia", OleDbType.WChar);
            cmd.Parameters.Add("@TrangThai", OleDbType.Boolean);
            cmd.Parameters.Add("@SoTrang", OleDbType.Integer);
            cmd.Parameters.Add("@NgonNgu", OleDbType.Integer);
            cmd.Parameters["@TenSach"].Value = sach.TenSach;
            cmd.Parameters["@LoaiSach"].Value = sach.LoaiSach;
            cmd.Parameters["@TacGia"].Value = sach.TacGia;
            cmd.Parameters["@TrangThai"].Value = sach.TrangThai;
            cmd.Parameters["@SoTrang"].Value = sach.SoTrang;
            cmd.Parameters["@NgonNgu"].Value = sach.NgonNgu;

            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            sach.MSach = (int)cmd.ExecuteScalar();
            cn.Close();
            return sach.MSach;
        }
Ejemplo n.º 13
0
    //返回一个单值,一般用select操作
    public string ExecScalar(OleDbCommand myCmd)
    {
        string strSql;
        try
        {
            if (myCmd.Connection.State != ConnectionState.Open)
            {
                myCmd.Connection.Open();
            }

            strSql=Convert.ToString(myCmd.ExecuteScalar());
            return strSql ;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);
        }
        finally
        {
            if (myCmd.Connection.State == ConnectionState.Open)
            {
                myCmd.Connection.Close();
            }
        }
    }
Ejemplo n.º 14
0
        public static string fcnDecrypt(string _strTextToDecrypt)
        {
            string strRes = "";
            string strPassphrase = "";

            using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
            {
                conDB.Open();

                string strSQL = "SELECT strEncPassPhrase " +
                                "FROM tblCampDefaults";

                using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
                    strPassphrase = cmdDB.ExecuteScalar().ToString();

                conDB.Close();
            }

            try { strRes = fcnDecrypt(_strTextToDecrypt, strPassphrase); }
            catch { }

            if (strRes == "") strRes = _strTextToDecrypt;

            return strRes;
        }
Ejemplo n.º 15
0
        public string ExecuteScalar(string sql)
        {
            try
            {
                using (OleDbConnection connection = new OleDbConnection(_ConnectionString))
                {

                    WriteLog(sql);
                    connection.Open();
                    var cmd = new OleDbCommand(sql, connection);
                    object o = cmd.ExecuteScalar();

                    string s = null;
                    if (o != null && Object.Equals(o, System.DBNull.Value) == false)
                    {
                        s = o.ToString();
                    }

                    WriteLog("查询结果:" + s);

                    return s;
                }
            }
            catch (Exception ex)
            {
                if (AfterExceptionThrow != null) AfterExceptionThrow(ex);
                else throw ex;
                return null;
            }
        }
    public string LoadContain()
    {
        if (Request.QueryString["CourseId"] == null)
        {
            return string.Empty;
        }

        string ThePath = string.Empty;
        string RetData = string.Empty;
        using (OleDbConnection Con = new OleDbConnection(constr))
        {
            OleDbCommand cmd = new OleDbCommand(String.Format("SELECT TOP 1 DataPath FROM CoursenotimeDataPath WHERE CourseId = {0}", Request.QueryString["CourseId"]), Con);
            try
            {
                Con.Open();
                ThePath = cmd.ExecuteScalar().ToString();
                //if (ThePath != string.Empty)
                //    ThePath = MapPath(DB.CourseNoTimeFileDir + ThePath);
                ThePath = DB.CourseNoTimeFileDir + ThePath;

                TextReader TR = new StreamReader(ThePath);
                RetData = TR.ReadToEnd();
                TR.Close();
                TR.Dispose();

            }
            catch (Exception ex)
            {
                RetData = ex.Message;
            }
            Con.Close();
        }

        return HttpUtility.HtmlDecode(RetData);
    }
Ejemplo n.º 17
0
        public BL.Domain.Ticket CreateTicket(BL.Domain.Ticket ticket)
        {
            string insertStatement = "INSERT INTO Ticket(AccountId, [Text],DateOpened, State, DeviceName) VALUES(@accountId, @text, @dateOpened, @state,@deviceName)";
            using (var connection = this.GetConnection())
            {
                OleDbCommand command = new OleDbCommand(insertStatement, connection);
                command.Parameters.AddWithValue("@accountId", ticket.AccountId);
                command.Parameters.AddWithValue("@text", ticket.Text);
                command.Parameters.AddWithValue("@dateOpened",
                ticket.DateOpened.ToString("yyyy-MM-dd HH:mm:ss"));
                command.Parameters.AddWithValue("@state", (byte)ticket.State);
                if (ticket is HardwareTicket)
                    command.Parameters.AddWithValue("@deviceName",
                    ((HardwareTicket)ticket).DeviceName);
                else
                    command.Parameters.AddWithValue("@deviceName", DBNull.Value);
                connection.Open();
                command.ExecuteNonQuery();
                string retrievalQuery = "Select @@Identity";
                OleDbCommand retrievalOfNewNbrCommand = new OleDbCommand(retrievalQuery, connection);
                ticket.TicketNumber = Convert.ToInt32(retrievalOfNewNbrCommand.ExecuteScalar());

                connection.Close();
            } return ticket;
        }
Ejemplo n.º 18
0
 public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)
 {
     try
     {
         string tem_sql = "";//定义字符串
         string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True";//记录连接Access的语句
         System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);//连接Access数据库
         System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类
         tem_conn.Open();//打开连接的Access数据库
         tem_sql = "select Count(*) From " + AccTable;//设置SQL语句,获取记录个数
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
         int RecordCount = (int)tem_comm.ExecuteScalar();//执行SQL语句,并返回结果
         //每个Sheet只能最多保存65536条记录。
         tem_sql = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目";//记录连接Excel的语句
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//实例化OleDbCommand类
         tem_comm.ExecuteNonQuery();//执行SQL语句,将数据表的内容导入到Excel中
         tem_conn.Close();//关闭连接
         tem_conn.Dispose();//释放资源
         tem_conn = null;
         MessageBox.Show("导入完成");
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.Message,"提示!");
     }
 }
Ejemplo n.º 19
0
 public int getid()
 {
     int z=0;
     OleDbCommand cmd = new OleDbCommand("Select max(t_id) from [transaction]",con);
     try
     {
         con.Open();
         object a = cmd.ExecuteScalar();
         if (a == DBNull.Value)
         {
             z = 1;
         }
         else
         {
             z = (int)a + 1;
         }
     }
     catch (Exception)
     {
         return -1;
     }
     finally
     {
         con.Close();
     }
     return z;
 }
Ejemplo n.º 20
0
        public static object GetSingle(string SQLString, params System.Data.OleDb.OleDbParameter[] cmdParms)
        {
            object result;

            using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
            {
                using (System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand())
                {
                    try
                    {
                        DbHelperOleDb.PrepareCommand(oleDbCommand, oleDbConnection, null, SQLString, cmdParms);
                        object obj = oleDbCommand.ExecuteScalar();
                        oleDbCommand.Parameters.Clear();
                        if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                        {
                            result = null;
                        }
                        else
                        {
                            result = obj;
                        }
                    }
                    catch (System.Data.OleDb.OleDbException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
            return(result);
        }
Ejemplo n.º 21
0
        public static object GetSingle(string SQLString)
        {
            object result;

            using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
            {
                using (System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(SQLString, oleDbConnection))
                {
                    try
                    {
                        oleDbConnection.Open();
                        object obj = oleDbCommand.ExecuteScalar();
                        if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                        {
                            result = null;
                        }
                        else
                        {
                            result = obj;
                        }
                    }
                    catch (System.Data.OleDb.OleDbException ex)
                    {
                        oleDbConnection.Close();
                        throw new Exception(ex.Message);
                    }
                }
            }
            return(result);
        }
Ejemplo n.º 22
0
 public string LoadContain()
 {
     if (QAMainMenu.SelectedValue == "")
     {
         return "";
     }
     string ThePath = string.Empty;
     string RetData = string.Empty;
     OleDbConnection Con = MySchool.QAConnection;
     OleDbCommand CMD = new OleDbCommand("SELECT Data_Path FROM QAMenu WHERE (QAID = " + QAMainMenu.SelectedValue.ToString() + ")", Con);
     try
     {
         Con.Open();
         ThePath = CMD.ExecuteScalar().ToString();
         if (ThePath != string.Empty)
         {
             ThePath = MapPath(ThePath);
         }
         if (File.Exists(ThePath))
         {
             TextReader TR = new StreamReader(ThePath);
             RetData = TR.ReadToEnd();
         }
     }
     catch (Exception ex)
     {
         RetData = ex.Message;
     }
     Con.Close();
     return RetData;
 }
Ejemplo n.º 23
0
 public BL.Domain.TicketResponse CreateTicketResponse(BL.Domain.TicketResponse response)
 {
     if (response.Ticket != null)
     {
         string insertStatement = "INSERT INTO TicketResponse([Text], [Date], IsClientResponse"
         + ", Ticket_TicketNumber) VALUES (@text, @date"
         + ", @isClientResponse, @tickedNumber)";
         using (var connection = this.GetConnection())
          {
             OleDbCommand command = new OleDbCommand(insertStatement, connection);
             command.Parameters.AddWithValue("@text", response.Text);
             command.Parameters.AddWithValue("@date", response.Date.ToString("yyyy-MM-dd HH:mm:ss"));
             command.Parameters.AddWithValue("@isClientResponse", response.IsClientResponse);
             command.Parameters.AddWithValue("@tickedNumber", response.Ticket.TicketNumber);
             connection.Open();
             command.ExecuteNonQuery();
             // Retrieve primary key ‘Id' of inserted ticketresponse
             //When retrieving this data from Access, we must create and execute 2 commands
             string retrievalQuery = "Select @@Identity";
             OleDbCommand retrievalOfNewNbrCommand = new OleDbCommand(retrievalQuery, connection);
             response.Id = Convert.ToInt32(retrievalOfNewNbrCommand.ExecuteScalar());
             connection.Close(); // good practice!
          }
         return response;
     }
     else
         throw new ArgumentException("The ticketresponse has no ticket attached to it");
 }
Ejemplo n.º 24
0
 public string selectOne(string q)
 {
     OleDbCommand cmd = new OleDbCommand(q, connection);
     object o = cmd.ExecuteScalar();
     if (o == null) return "";
     else return o.ToString();
 }
        protected void LinkButton2_Click(object sender, EventArgs e)
        {
            try
            {
                connection.Open();
                string qry1 = "select Upload from FilesTable where Number=" + 2 + "";
                OleDbCommand cmd = new OleDbCommand(qry1, connection);
                cmd.ExecuteNonQuery();

                string fileName = cmd.ExecuteScalar().ToString();
                string fileExtension = ".txt/.jpg/.pdf/.docx/.xls";

                // Set Response.ContentType
                Response.ContentType = GetContentType(fileExtension);

                // Append header
                Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);

                // Write the file to the Response
                Response.TransmitFile(Server.MapPath("~/Files/" + fileName));
                Response.End();
            }
            catch
            { }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 26
0
        public static long fcnGetIRRegCount()
        {
            //get current count of web registrations

            string strSQL;

            long lngRes = 0;

            using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
            {
                conDB.Open();

                strSQL = "SELECT Count(lngRegistrationWebID) AS lngRegCount " +
                        "FROM tblWebIndRegistrations;";

                using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
                {
                    try { lngRes = Convert.ToInt32(cmdDB.ExecuteScalar()); }
                    catch { lngRes = 0; }
                }

                conDB.Close();
            }

            return lngRes;
        }
Ejemplo n.º 27
0
		public void run()
		{
			OleDbConnection con = null;
			OleDbTransaction txn;
			Exception exp = null;
			try
			{
				BeginCase("OleDbTransaction Rollback");

				//
				//prepare data
				base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);

				string Result = "";
				con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
				con.Open();
				txn = con.BeginTransaction();
				OleDbCommand cmd = new OleDbCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
				cmd.ExecuteNonQuery();
				txn.Rollback();

				//
				//
				cmd = new OleDbCommand("Select LastName From Employees Where EmployeeID = 100", con);
				Result = cmd.ExecuteScalar().ToString();
				Compare(Result,"Last100" );
				this.Log(Result);

			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();
		}
Ejemplo n.º 28
0
        public static int GetCount()
        {
            OleDbConnection dbConnection;
            OleDbCommand dbCommand;
            object obj;
            string buff;
            int result = 0;

            try
            {
                dbConnection = new OleDbConnection(ConnectionStringsManager.GetConnectionString());
                dbCommand = new OleDbCommand(_SELECT_COUNT, dbConnection);

                dbConnection.Open();

                try
                {
                    obj = dbCommand.ExecuteScalar();
                    buff = obj.ToString();

                    Int32.TryParse(buff, out result);
                }
                finally
                {
                    dbConnection.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return result;
        }
Ejemplo n.º 29
0
    public string LoadPage()
    {
        string Path = string.Empty;
        OleDbConnection Con = new OleDbConnection(constr);
        OleDbCommand CMD = new OleDbCommand("SELECT Data_Path FROM MenuItem Where ItemID = " + MainMenu.SelectedValue.ToString(), Con);
        try
        {
            Con.Open();
            Path = CMD.ExecuteScalar().ToString();

        }
        catch { }
        Con.Close();
        if (Path == string.Empty)
        {
            //HtmlEditorQA.Html = string.Empty;
            return string.Empty;
        }
        Path = MapPath(Path);
        if (!File.Exists(Path))
        {
            return string.Empty;
        }
        else
        {
            TextReader TR = new StreamReader(Path);
            string tt = TR.ReadToEnd().ToString();

            TR.Close();
            TR.Dispose();
            return tt;
        }
    }
Ejemplo n.º 30
0
 public void AccessGuideJoinExcel(string Access, string AccTable, string Excel)
 {
     try
     {
         string tem_sql = "";                                                                                        //定义字符串
         string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Access + ";Persist Security Info=True"; //记录连接Access的语句
         System.Data.OleDb.OleDbConnection tem_conn = new System.Data.OleDb.OleDbConnection(connstr);                //连接Access数据库
         System.Data.OleDb.OleDbCommand    tem_comm;                                                                 //定义OleDbCommand类
         tem_conn.Open();                                                                                            //打开连接的Access数据库
         tem_sql  = "select Count(*) From " + AccTable;                                                              //设置SQL语句,获取记录个数
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);                                           //实例化OleDbCommand类
         int RecordCount = (int)tem_comm.ExecuteScalar();                                                            //执行SQL语句,并返回结果
         //每个Sheet只能最多保存65536条记录。
         tem_sql  = @"select top 65535 * into [Excel 8.0;database=" + Excel + @".xls].[Sheet2] from 帐目";             //记录连接Excel的语句
         tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);                                           //实例化OleDbCommand类
         tem_comm.ExecuteNonQuery();                                                                                 //执行SQL语句,将数据表的内容导入到Excel中
         tem_conn.Close();                                                                                           //关闭连接
         tem_conn.Dispose();                                                                                         //释放资源
         tem_conn = null;
         MessageBox.Show("导入完成");
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message, "提示!");
     }
 }
Ejemplo n.º 31
0
 public static object ExecuteScalar(string sql)
 {
     if (myOleDbConnection == null)
         Connect();
     OleDbCommand myOleDbCommand = new OleDbCommand(sql, myOleDbConnection);
     return myOleDbCommand.ExecuteScalar();
 }
Ejemplo n.º 32
0
        /// <summary></summary>
        /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
        ///
        /// SELECT 语句
        /// <returns></returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用
        public object ExecuteScalar(string sql)
        {
            using (oleDbConnection = this.GetOleDbConnection())
            {
                if (oleDbConnection == null)
                {
                    return(null);
                }
                try
                {
                    if (oleDbConnection.State == System.Data.ConnectionState.Closed)
                    {
                        oleDbConnection.Open();
                    }
                    oleDbCommand = new OleDbCommand(sql, oleDbConnection);
                    return(oleDbCommand.ExecuteScalar());
                }
                catch (Exception ex)
                {
#if DEBUG
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
                    return(null);
                }
            }
        }
Ejemplo n.º 33
0
        public frmCollectRefundAmt(long _lngGGCCRegistrationWebID)
        {
            InitializeComponent();

            string strSQL = "";

            using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
            {
                conDB.Open();

                strSQL = "SELECT curDeposit " +
                        "FROM tblWebGGCCRegistrations " +
                        "WHERE lngGGCCRegistrationWebID=" + _lngGGCCRegistrationWebID.ToString();

                using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
                {
                    decimal decDeposit = 0;

                    try { decDeposit = Convert.ToDecimal(cmdDB.ExecuteScalar()); }
                    catch { decDeposit = 0; }

                    lblDeposit.Text = decDeposit.ToString("C");

                    txtAmt.Text = decDeposit.ToString();
                }

                conDB.Close();
            }
        }
Ejemplo n.º 34
0
        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                System.Data.OleDb.OleDbConnection conn = new
                       System.Data.OleDb.OleDbConnection();
                conn.ConnectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;" +
                                        @"Data source= C:\Users\Ahmed Kamel Taha\Documents\Visual Studio 2015\Projects\ASP Training\bns.accdb";
                conn.Open();
                string q = "select count(*) from users where name='" + TextBox1.Text + "' And password='******';";
                OleDbCommand comm = new OleDbCommand(q, conn);

                if (Convert.ToInt32(comm.ExecuteScalar().ToString()) == 1)
                {
                    Session["user"] = TextBox1.Text;


                    Response.Redirect("index.aspx");
   
                }
                else
                {
                    Response.Write("<script> alert(\"wrong pass or user\"); </script>");

                }
                conn.Close();

            }catch(Exception ex)
            {
                Response.Write(ex.ToString());
            }
        }
Ejemplo n.º 35
0
    protected void Page_Load(object sender, EventArgs e)
    {
        string query = this.Request.QueryString.ToString();
        int index = query.IndexOf('=');
        query = query.Substring(index + 1);
        int FlatID = Convert.ToInt32(query);

        string UserName;

        OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["RealEstate"].ConnectionString);
        OleDbCommand cmd = new OleDbCommand(String.Format("SELECT UserName FROM Flats WHERE FlatID = {0}", FlatID), conn);
        cmd.Connection = conn;

        conn.Open();
        UserName = cmd.ExecuteScalar().ToString();
        conn.Close();

        if (this.User.Identity.Name == UserName || this.User.IsInRole("Manager") || this.User.IsInRole("Operator"))
        {
            this.ButtonDeleteRecord.Visible = true;
        }
        else
        {
            this.ButtonDeleteRecord.Visible = false;
        }
    }
Ejemplo n.º 36
0
 public static int InsertIntoOringalTransaction(BankAccount bankAccount, Transaction transaction, string categoryName)
 {
     string cmdText = "INSERT INTO tblOrginalTransaction(";
     cmdText += "Verified, TransactionID, TransactionDate, TransactionAmount, Merchant, BankMemo, BankAccountId, TransactionType";
     if (!string.IsNullOrEmpty(transaction.CheckNumber))
         cmdText += ", CheckNumber";
     if (categoryName != "")
         cmdText += ", CategoryName";
     cmdText += ") Values(";
     cmdText += "false";
     cmdText += ", '" + transaction.TransactionID + "'";
     cmdText += ", #" + Convert.ToString(transaction.TransactionDate) + "#";
     cmdText += ", " + Convert.ToString(transaction.TransactionAmount);
     cmdText += ", '" + transaction.MerchantName.Replace(@"'", "''") + "'";
     cmdText += ", '" + transaction.BankMemo.Replace(@"'", "''") + "'";
     cmdText += ", " + Convert.ToString(bankAccount.BankAccountID);
     cmdText += ", '" + transaction.TransactionType + "'";
     if (!string.IsNullOrEmpty(transaction.CheckNumber))
         cmdText += ", '" + transaction.CheckNumber + "'";
     if (categoryName != "")
         cmdText += ", '" + categoryName + "'";
     cmdText += ")";
     string identity = " SELECT @@Identity";
     int orginalTransactionID;
     using (OleDbConnection myConnection = new OleDbConnection(
     ConfigurationManager.ConnectionStrings["BeanCounterDB"].ToString()))
     {
         myConnection.Open();
         using (OleDbCommand myCommand = new OleDbCommand(cmdText, myConnection))
             myCommand.ExecuteNonQuery();
         using (OleDbCommand myCommand = new OleDbCommand(identity, myConnection))
             orginalTransactionID = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
     }
     return orginalTransactionID;
 }
Ejemplo n.º 37
0
    /// <summary>
    /// This function will run an SQL sentence and returns the first column of the first row returned by running it.
    /// </summary>
    /// <param name="strSQL">The SQL sentence to run</param>
    /// <returns>The first column of the first row returned by running it. Returned as object and should be converted to the appropriate type before running</returns>
    public object RunScalarSQL(string strSQL)
    {
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"provider=microsoft.jet.oledb.4.0;data source=" + this.path;

        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
        cmd.CommandText = strSQL;
        cmd.Connection  = conn;

        conn.Open();
        Object obj = cmd.ExecuteScalar();

        conn.Close();
        return(obj);
    }
Ejemplo n.º 38
0
    public string getASIN(string imageURL)
    {
        string asin;

        OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString);

        conn.Open();
        OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();

        cmd.Connection  = conn;
        cmd.CommandText = "Select ItemID From Item Where ImageURL = '" + imageURL + "'";
        asin            = (cmd.ExecuteScalar()).ToString();
        conn.Close();

        return(asin);
    }
Ejemplo n.º 39
0
    public void addItemToDatabase(string asin)
    {
        string   description     = getItemDescription(asin);
        string   price           = getCurrentPrice(asin);
        string   lastStoredPrice = "0";
        string   url             = getImageURL(asin);
        DateTime date            = DateTime.Now;

        OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["databaseString"].ConnectionString);

        conn.Open();
        OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();

        cmd.Connection  = conn;
        cmd.CommandText = "Insert Into User_Item (UserID, ItemID) values ('testuser', '" + asin + "')";
        cmd.ExecuteNonQuery();

        //only insert into price if price is different from current price
        try
        {
            cmd.CommandText = "Select Price From Price Where ItemID = '" + asin + "' Order By PriceDate DESC";
            lastStoredPrice = (cmd.ExecuteScalar()).ToString();
        }
        catch (Exception)
        {
            //do nothing if no record found in price
        }

        if (lastStoredPrice != price)
        {
            cmd.CommandText = "Insert Into Price(ItemID, Price, PriceDate) values ('" + asin + "', '" + price + "', '" + date + "')";
            cmd.ExecuteNonQuery();
        }

        try
        {
            cmd.CommandText = "Insert Into Item (ItemID, Description, ImageURL, CurPrice, CurDate) values ('" + asin + "', '" + description + "', '" + url + "', '" + price + "', '" + date + "')";
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {
            //do nothing if duplicate
        }
        conn.Close();
    }
        public bool update_history(string dev)
        {
            string sheet   = (dev.Replace('/', '-')).Replace(':', '@');
            string src     = "C:\\WoT Testbed\\" + sheet + ".xls";
            string headers = "[RecDateTime] datetime, [reading] int, [state] int";

            System.Data.OleDb.OleDbCommand    cmd;
            System.Data.OleDb.OleDbConnection ExcelCon_dev = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + src + "';Extended Properties=Excel 8.0;");

            if (!File.Exists(src))
            {
                cmd = new System.Data.OleDb.OleDbCommand("create table [" + sheet + "] (" + headers + " ) ", ExcelCon_dev);
                ExcelCon_dev.Open();
                cmd.ExecuteNonQuery();
                ExcelCon_dev.Close();
            }
            try
            {
                // check history size
                ExcelCon_dev.Open();
                cmd = new System.Data.OleDb.OleDbCommand("Select count([reading]) from [" + sheet + "] ", ExcelCon_dev);
                int history_count = int.Parse(cmd.ExecuteScalar().ToString());

                DateTime d = new DateTime();
                d = DateTime.Parse(DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute);
                // delete old rows
                string val = monitor(dev);
                if (history_count > 20)
                {
                    cmd = new OleDbCommand(" update  [" + sheet + "] set [RecDateTime]='" + d + "', [reading]=" + int.Parse(val) + " where [RecDateTime]=(select min([RecDateTime]) from [" + sheet + "]) ", ExcelCon_dev);
                }
                // insert new rows
                else
                {
                    cmd = new OleDbCommand(" insert into [" + sheet + "] ([RecDateTime], [reading]) values ('" + d + "','" + val + "')", ExcelCon_dev);
                }
                cmd.ExecuteNonQuery();
                ExcelCon_dev.Close();
            }
            catch { ExcelCon_dev.Close(); }


            return(true);
        }
Ejemplo n.º 41
0
 public static object ExecuteScalar(System.Data.OleDb.OleDbCommand sqlCmd)
 {
     try
     {
         sqlCmd.Connection = Connection;
         if (sqlCmd.Connection.State == ConnectionState.Closed)
         {
             sqlCmd.Connection.Open();
         }
         return(sqlCmd.ExecuteScalar());
     }
     catch (Exception ex)
     {
         MsgBoxX.Show(ex.Message);
         return(null);
     }
     finally
     {
         Cursor.Current = Cursors.Default;
         sqlCmd.Connection.Close();
     }
 }
Ejemplo n.º 42
0
        public int dbConnect()
        {
            System.Data.OleDb.OleDbConnection conn = new
                                                     System.Data.OleDb.OleDbConnection();
            // TODO: Modify the connection string and include any
            // additional required properties for your database.
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    @"Data source=" +
                                    @"USER_TABLE1.mdb";
            try
            {
                string queryString = "SELECT * FROM [USER_TABLE] WHERE ( USER_ID = '" + USERNAME.Text + "' and USER_PSW = '" + PSW.Text + "')";
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(queryString, conn);

                conn.Open();
                string str = command.ExecuteScalar().ToString();

                if (str == "")
                {
                    return(1);
                }
                else
                {
                    return(0);
                }
            }



            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source" + ex.ToString());
                return(1);
            }
            finally
            {
                conn.Close();
            }
        }
Ejemplo n.º 43
0
        private void button1_Click(object sender, System.EventArgs e)
        {
            #region   处方发药明细
            string stext = this.Text.Trim();
            //上传发药明细表的数据
            //this.Cursor =Cursors.WaitCursor;
            this.Text = "正在上传处方发药明细.....";
            OleDbTransaction myTrans0;
            myTrans0 = DB.sConnect.BeginTransaction();
            try
            {
                int    err_code = -1;
                string err_text = "";
                System.Data.OleDb.OleDbCommand mySelCmd = new System.Data.OleDb.OleDbCommand();
                mySelCmd.Connection  = DB.sConnect;
                mySelCmd.Transaction = myTrans0;

                OleDbParameter parm;
                mySelCmd.Parameters.Add("@v_djrq", XcDate.ServerDateTime.ToShortDateString());
                mySelCmd.Parameters.Add("@V_DJSJ", XcDate.ServerDateTime.ToLongTimeString());
                mySelCmd.Parameters.Add("@V_DJY", _employeeID);
                mySelCmd.Parameters.Add("@v_deptid", _deptID);
                parm                 = mySelCmd.Parameters.Add("@err_code", OleDbType.Integer);
                parm.Direction       = ParameterDirection.Output;
                parm                 = mySelCmd.Parameters.Add("@err_text", OleDbType.VarChar, 250);
                parm.Direction       = ParameterDirection.Output;
                mySelCmd.CommandText = "SP_Yk_fymx_dj";
                mySelCmd.CommandType = System.Data.CommandType.StoredProcedure;
                mySelCmd.ExecuteScalar();
                err_code = Convert.ToInt32(mySelCmd.Parameters["@err_code"].Value);
                err_text = Convert.ToString(mySelCmd.Parameters["@err_text"].Value);
                if (err_code != 0)
                {
                    throw new System.Exception(err_text);
                }
                myTrans0.Commit();
                this.Text = stext;
            }
            catch (System.Exception err)
            {
                myTrans0.Rollback();
                MessageBox.Show("在上传发药明细时发生错误" + err.Message);
                return;
            }

            #endregion

            #region 系统对账
            //系统对账
            this.Text = "正在进行系统对账.....";
            try
            {
                ParameterEx[] parameters = new ParameterEx[2];
                parameters[0].Value = dtp1.Value.ToShortDateString();
                parameters[1].Value = _deptID;
                DataTable tb = DatabaseAccess.GetDataTable(DatabaseType.IbmDb2YP, "SP_Yk_XTDZ", parameters, 30);
                tb.TableName = "myTb";

                if (tb.Rows.Count > 0)
                {
                    this.Close();
                    Frmxtdzmx f = new Frmxtdzmx(_employeeID, _deptID);
                    f.MdiParent = _mdiParent;
                    f.Show();
                    f.FillData(tb);
                    return;
                }

                if (_functionName.Trim() == "Fxc_yk_xtdz")
                {
                    MessageBox.Show("系统对帐完成");
                    this.Text = stext;
                    this.Close();
                    return;
                }
            }
            catch (System.Exception err)
            {
                MessageBox.Show("出错" + err.Message);
                return;
            }

            #endregion

            #region 取消上次月结
            //取消上次月结
            if (_functionName.Trim() == "Fxc_yk_unymjz" && MessageBox.Show("您确定要取消上次月结吗 ?", "询问窗", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
            {
                this.Text = "正在取消上次月结.....";
                OleDbTransaction myTrans1;
                myTrans1 = DB.sConnect.BeginTransaction();
                try
                {
                    int    err_code = -1;
                    string err_text = "";
                    System.Data.OleDb.OleDbCommand mySelCmd = new System.Data.OleDb.OleDbCommand();
                    mySelCmd.Connection  = DB.sConnect;
                    mySelCmd.Transaction = myTrans1;

                    OleDbParameter parm;
                    mySelCmd.Parameters.Add("@deptid", _deptID);
                    mySelCmd.Parameters.Add("@djsj", XcDate.ServerDateTime);
                    mySelCmd.Parameters.Add("@djy", _employeeID);
                    parm                 = mySelCmd.Parameters.Add("@err_code", OleDbType.Integer);
                    parm.Direction       = ParameterDirection.Output;
                    parm                 = mySelCmd.Parameters.Add("@err_text", OleDbType.VarChar, 100);
                    parm.Direction       = ParameterDirection.Output;
                    mySelCmd.CommandText = "sp_Yk_unymjc";
                    mySelCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    mySelCmd.ExecuteScalar();
                    err_code = Convert.ToInt32(mySelCmd.Parameters["@err_code"].Value);
                    err_text = Convert.ToString(mySelCmd.Parameters["@err_text"].Value);
                    if (err_code != 0)
                    {
                        throw new System.Exception(err_text);
                    }
                    myTrans1.Commit();
                    this.Text = stext;
                    MessageBox.Show(err_text);
                    this.Close();
                    return;
                }
                catch (System.Exception err)
                {
                    myTrans1.Rollback();
                    this.butok.Enabled = true;
                    MessageBox.Show(err.Message);
                }
            }
            #endregion

            #region 月未结账

            if (_functionName.Trim() != "Fxc_yk_ymjz")
            {
                return;
            }
            //月未结帐
            this.Text = "正在进行月未结帐.....";
            OleDbTransaction myTrans;
            myTrans = DB.sConnect.BeginTransaction();
            try
            {
                int    err_code = -1;
                string err_text = "";
                System.Data.OleDb.OleDbCommand mySelCmd = new System.Data.OleDb.OleDbCommand();
                mySelCmd.Connection  = DB.sConnect;
                mySelCmd.Transaction = myTrans;

                OleDbParameter parm;
                mySelCmd.Parameters.Add("@year", _year);
                mySelCmd.Parameters.Add("@month", _month);
                mySelCmd.Parameters.Add("@deptid", _deptID);
                mySelCmd.Parameters.Add("@ksrq", dtp1.Value.ToShortDateString());
                mySelCmd.Parameters.Add("@jsrq", dtp2.Value.ToShortDateString());
                mySelCmd.Parameters.Add("@djsj", XcDate.ServerDateTime);
                mySelCmd.Parameters.Add("@djy", _employeeID);
                parm                 = mySelCmd.Parameters.Add("@err_code", OleDbType.Integer);
                parm.Direction       = ParameterDirection.Output;
                parm                 = mySelCmd.Parameters.Add("@err_text", OleDbType.VarChar, 100);
                parm.Direction       = ParameterDirection.Output;
                mySelCmd.CommandText = "sp_Yk_ymjc";
                mySelCmd.CommandType = System.Data.CommandType.StoredProcedure;
                mySelCmd.ExecuteScalar();
                err_code = Convert.ToInt32(mySelCmd.Parameters["@err_code"].Value);
                err_text = Convert.ToString(mySelCmd.Parameters["@err_text"].Value);
                if (err_code != 0)
                {
                    throw new System.Exception(err_text);
                }
                myTrans.Commit();
                this.Text = stext;
                MessageBox.Show(err_text);
                this.Close();
            }
            catch (System.Exception err)
            {
                myTrans.Rollback();
                this.butok.Enabled = true;
                MessageBox.Show(err.Message);
            }
            #endregion
        }
Ejemplo n.º 44
0
        public static bool checkForPriceChanges()
        {
            DateTime      date    = DateTime.Now;
            bool          changed = false;
            List <string> items   = new List <string>();
            string        lastStoredPrice;
            string        currentPrice;
            // count = (Int32)cmd.ExecuteScalar();
            OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conString);

            //first read unique items into a list
            try
            {
                conn.Open();
                OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
                cmd.Connection  = conn;
                cmd.CommandText = "Select ItemID From User_Item Where UserID = 'testuser'";
                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    items.Add(reader.GetString(0));
                }
            }

            catch (Exception)
            {
                //do nothing if no items found
            }
            conn.Close();

            //compare current price for each item to latest stored price
            foreach (string itemNum in items)
            {
                conn.Open();
                OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
                cmd.Connection = conn;
                currentPrice   = service.getCurrentPrice(itemNum);

                //wait 1.5 seconds for service
                System.Threading.Thread.Sleep(1500);

                cmd.CommandText = "Select Price From Price Where ItemID = '" + itemNum + "' Order By PriceDate DESC";
                lastStoredPrice = (cmd.ExecuteScalar()).ToString();
                conn.Close();

                //sometimes an actual price can't be retrieved because it's lower than a manufacturer's minimum advertised price. in this case "Too low to display" is returned.
                try
                {
                    if (Convert.ToDecimal(lastStoredPrice) != Convert.ToDecimal(currentPrice))
                    {
                        //update currentprice and currentdate in item table
                        conn.Open();
                        cmd.CommandText = "Update Item Set CurPrice = '" + Convert.ToDecimal(currentPrice) + "' Where ItemID = '" + itemNum + "'";
                        cmd.ExecuteNonQuery();
                        //write current date
                        cmd.CommandText = "Update Item Set CurDate = '" + date + "' Where ItemID = '" + itemNum + "'";
                        cmd.ExecuteNonQuery();
                        conn.Close();

                        //add price record to price table
                        conn.Open();
                        cmd.CommandText = "Insert Into Price (ItemID, Price, PriceDate) values ('" + itemNum + "', '" + Decimal.Parse(currentPrice) + "', '" + date + "')";
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        Console.WriteLine(itemNum + " change from $" + lastStoredPrice + " to $" + currentPrice);
                        changed = true;
                    }
                    else
                    {
                        Console.WriteLine(itemNum + " no price change.");
                    }
                }
                catch
                {
                    //do nothing if the price was too low to display
                }
            }

            //no changes
            return(changed);
        }
Ejemplo n.º 45
0
        protected void Button1_Click(object sender, EventArgs e)
        {
            string username = TextBox1.Text;
            string password = TextBox2.Text;

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                                    @"Data source= C:\Users\OK\Documents\Ecafe.accdb";
            conn.Open();

            String my_querry1 = "Select* From Users where Username = '******'and Password = '******'";

            System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand(my_querry1, conn);

            var dr1 = cmd1.ExecuteReader();

            if (dr1.Read())
            {
                string[] i     = (string[])Session["items"];
                string[] q     = (string[])Session["quants"];
                string[] p     = (string[])Session["prices"];
                string   ptime = (string)Session["picktime"];

                int total = 0;
                for (int j = 0; j < p.Length; ++j)
                {
                    Int32 tpr = Convert.ToInt32(p[j]);
                    total += tpr;
                }


                //MessageBox.Show(dr1[0].ToString());
                String uid          = dr1[0].ToString();
                String up           = dr1[7].ToString();
                String ua           = dr1[6].ToString();
                var    dateAndTime  = DateTime.Now;
                var    current_date = dateAndTime.Date;

                //cmd = new OleDbCommand("SELECT @@IDENTITY", con);
                //id = cmd.ExecuteNonQuery();
                String my_quer2 = "select * from Orders";
                String mq       = "SELECT COUNT(*) FROM Orders";
                System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand(my_quer2, conn);
                System.Data.OleDb.OleDbCommand cmd6 = new System.Data.OleDb.OleDbCommand(mq, conn);
                var   dr2 = cmd2.ExecuteReader();
                int   f   = (int)cmd6.ExecuteScalar();
                var   oid = "a"; //order id
                Int32 y   = 0;
                var   g   = 1;
                //MessageBox.Show(f.ToString());
                if (dr2.Read() == false)
                {
                    oid = "0";
                }
                while (dr2.Read())
                {
                    y = Convert.ToInt32(dr2[11]);
                    MessageBox.Show(y.ToString());
                    g++;
                    //if((g-1)==f)
                    //break;
                }
                // MessageBox.Show(f.ToString());

                Int32 ooid = y + 1;
                //Int32 ooid = Convert.ToInt32(oid);
                //ooid += 1;
                // MessageBox.Show(oid.ToString());
                //MessageBox.Show(ooid.ToString());

                DateTime currentTime  = DateTime.Now;
                DateTime x30MinsLater = currentTime.AddMinutes(30);
                // string pt = ptime.ToString("hh:mm tt");

                String dt = x30MinsLater.ToString("hh:mm tt");
                // DateTime dtime = Convert.ToDateTime(x30MinsLater);
                DateTime dtime = DateTime.Parse(dt, System.Globalization.CultureInfo.CurrentCulture);

                for (int j = 0; j < i.Length; ++j)
                {
                    String       my_quer = "select ID from Items where Item = '" + i[j] + "'";
                    OleDbCommand cmd3    = new OleDbCommand(@"select ID from Items where Item = @x1 ", conn);
                    cmd3.Parameters.AddWithValue("@x1", i[j]);
                    var dr3 = cmd3.ExecuteReader();
                    var iid = "a"; //item id

                    while (dr3.Read())
                    {
                        iid = dr3[0].ToString();
                    }
                    // MessageBox.Show(ptime);
                    if (string.IsNullOrWhiteSpace(ptime))
                    {
                        // MessageBox.Show("hello");
                        String sql = "insert into Orders ([OrderID],[UserID],[OrderType],[Status],[ItemID],[OrderDate],[TotalBill],[Address],[CustomerNumber],[Quantity],[DTime]) values ('" + ooid + "','" + uid + "','Delivery','Incomplete','" + iid + "','" + current_date + "','" + total + "','" + ua + "','" + up + "','" + q[j] + "','" + dtime + "')";
                        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                        var dr4 = cmd.ExecuteNonQuery();
                        MessageBox.Show("Your Order Has been Placed and it will be delivered around " + dtime.ToString("hh:mm tt"));
                    }
                    else
                    {
                        DateTime pitime = DateTime.Parse(ptime, System.Globalization.CultureInfo.CurrentCulture);
                        DateTime PTime  = DateTime.ParseExact(ptime, "HH:mm", CultureInfo.InvariantCulture);
                        //MessageBox.Show(ptime);
                        String sql = "insert into Orders ([OrderID],[UserID],[OrderType],[Status],[ItemID],[OrderDate],[TotalBill],[Address],[CustomerNumber],[Quantity],[PTime]) values ('" + ooid + "','" + uid + "','Pickup','Incomplete','" + iid + "','" + current_date + "','" + total + "','" + ua + "','" + up + "','" + q[j] + "','" + PTime + "')";
                        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
                        var dr4 = cmd.ExecuteNonQuery();
                        MessageBox.Show("Your Order Has been Placed. Please pick it up around " + PTime.ToString("HH:mm "));
                    }
                    Server.Transfer("WebForm1.aspx");
                }


// MessageBox.Show("Your Order Has been Placed and it will be delivered around "+ dtime.ToString("hh:mm tt"));
            }
            else
            {
                MessageBox.Show("Please Check Your Username and Password.");
                Server.Transfer("Login.aspx");
            }
        }
Ejemplo n.º 46
0
        public static void UpdateProfit()                            // Updates last record in DB. Calculates position profit, accumulated profit etc. Done in few queries
        {
            if (connect.State == System.Data.ConnectionState.Closed) // If no connection to DB
            {
                Console.WriteLine("No DB connection! Connecting");
                DBConnect();
            }

            // Get the value of account_balance from the previous record using nested query
            var command1 = new oledb.OleDbCommand("SELECT account_balance FROM [tfr_account_statement] WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement]) - 1");

            command1.Connection = connect;

            try             // Run sql command
            {
                command1.ExecuteNonQuery();
                Console.WriteLine("get previous record. " + Convert.ToDouble(command1.ExecuteScalar()));
            }
            catch (Exception err)
            { Console.WriteLine("Error while getting previous record " + err); }



            // profit_per_stock, profit_per_quantity
            var command2 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET profit_per_stock = trade_close_price - trade_open_price, profit_per_quantity = (trade_close_price - trade_open_price) * stock_quantity WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");

            command2.Connection = connect;

            try             // Run sql command
            {
                command2.ExecuteNonQuery();
                Console.WriteLine("Update record in DB. Calculate frofit 1st query");
            }
            catch (Exception err)
            { Console.WriteLine("Error while updating record. Calculate profit 1st query " + err); }



            // account_balance = Previous account_balance + profit_per_quantity
            var command3 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET account_balance = '" + Convert.ToDouble(command1.ExecuteScalar()) + "' + profit_per_quantity WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");

            command3.Connection = connect;

            try             // Run sql command
            {
                command3.ExecuteNonQuery();
                Console.WriteLine("Update record in DB. Calculate profit 2nd query");
            }
            catch (Exception err)
            { Console.WriteLine("Error while updating record. Calculate profit 2nd query " + err); }



            // Update trade_profit_prcnt
            var command4 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET trade_profit_prcnt = 100 * profit_per_quantity / (stock_quantity * trade_open_price) WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");

            command4.Connection = connect;

            try             // Run sql command
            {
                command4.ExecuteNonQuery();
                Console.WriteLine("Update record in DB. Calculate profit 3rd query");
            }
            catch (Exception err)
            { Console.WriteLine("Error while updating record. Calculate profit 3rd query " + err); }



            // Get the value of accumulated_sum_prcnt from the previous record then it will be used in a second query
            var command5 = new oledb.OleDbCommand("SELECT accumulated_sum_prcnt FROM [tfr_account_statement] WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement]) - 1");

            command5.Connection = connect;
            try             // Run sql command
            {
                command5.ExecuteNonQuery();
                Console.WriteLine("Get the value of accumulated_sum_prcnt from the previous record. " + Convert.ToDouble(command5.ExecuteScalar()));
            }
            catch (Exception err)
            { Console.WriteLine("Error while getting the value of accumulated_sum_prcnt from the previous record " + err); }



            // accumulated_sum_prcnt = Previous accumulated_sum_prcnt + trade_profit_prcnt
            var command6 = new oledb.OleDbCommand("UPDATE [tfr_account_statement] SET accumulated_sum_prcnt = '" + Convert.ToDouble(command5.ExecuteScalar()) + "' + trade_profit_prcnt WHERE [id] = (SELECT COUNT (*) FROM [tfr_account_statement])");

            command6.Connection = connect;

            try             // Run sql command
            {
                command6.ExecuteNonQuery();
                Console.WriteLine("Update record in DB. accumulated_sum_prcnt query");
            }
            catch (Exception err)
            { Console.WriteLine("Error while updating record. accumulated_sum_prcnt query " + err); }
        }
Ejemplo n.º 47
0
    protected void Page_Load(object sender, EventArgs e)
    {
        //Custom Client Names.
        string clientName = ConfigurationManager.AppSettings["ClientName"];

        Page.Title = clientName + " LOGIN PAGE";
        messageContainer.Visible = false;

        //PersistCookie.Checked = false;
        //PersistCookie.Visible = false;

        Session["INTERNAL_IP"] = false;
        System.Data.OleDb.OleDbConnection IPDBConnection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["_InternalIPDB"].ConnectionString);

        //check session to prevent logs from being created twice [1st on page load, 2nd when user click login button]
        if (Session["SID"] == null)
        {
            string sHost = "";
            try
            {
                sHost = System.Net.Dns.GetHostEntry(Request.Params["REMOTE_ADDR"]).HostName.ToString().ToUpper();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Session["sHOST"] = sHost.ToUpper();
            }
            System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["_UserDatabase"].ConnectionString);
            string strCommand = string.Format("Insert into [LOG] (IP_ADDRESS,COMPUTER_NAME, TIME_START) values ('{0}','{1}','{2}')", Request.Params["REMOTE_ADDR"], sHost, DateTime.Now.ToString());
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(strCommand, myConnection);
            myCommand.Connection.Open();
            myCommand.ExecuteNonQuery();
            System.Data.OleDb.OleDbCommand cmdGetidentity = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", myConnection);
            long lngNewID = Convert.ToInt64(cmdGetidentity.ExecuteScalar());
            Session["SID"] = lngNewID;
            myCommand.Connection.Close();
        }

        if (Page.Request["NoByPass"] == "TRUE")
        {
            if ((Session["CurrentEditor"] != null))
            //user has already authenticated through one of the ASP edit forms
            {
                FormsAuthentication.RedirectFromLoginPage(Session["CurrentEditor"].ToString(), false);
            }
            return;
        }

        if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["No_Internal_IP_L123_Bypass"]) && bool.Parse(ConfigurationManager.AppSettings["No_Internal_IP_L123_Bypass"]) && Request["Token"] == null)
        {
            return;
        }

        if ((Session["sHost"].ToString().IndexOf("GIS1") == 0 || Session["sHost"].ToString().IndexOf("PROD3") == 0) && Request["Token"] == null)
        {
            //logged in from WS PC
            DeveloperBypass();
            return;
        }
        else if (Request.UserHostAddress == "127.0.0.1" || Request.UserHostAddress == "::1")
        {
            //logged in from server using localhost in browser address
            DeveloperBypass_InitiallyReadOnly("ADMIN", "L3 Bypass (Debug)"); //will be prompted for password if trying to edit
            return;
        }
        else
        {
            //check ip address
            DataSet myData = new DataSet();

            //Level II bypass - GIS PC
            string           strGISPCQuery = string.Format("select * from [IP_List_GIS] where [PCNAME] like '{0}'", Session["sHost"].ToString());
            OleDbDataAdapter dsGISCmd      = new OleDbDataAdapter(strGISPCQuery, IPDBConnection);
            dsGISCmd.Fill(myData, "GIS_PC");
            DataTable pTableGIS = myData.Tables["GIS_PC"];
            if (pTableGIS.Rows.Count > 0)
            {
                DeveloperBypass_InitiallyReadOnly(pTableGIS.Rows[0]["LOGIN"].ToString(), string.Format("L2 Bypass ({0})", pTableGIS.Rows[0]["LOGIN"].ToString())); //will be prompted for password if trying to edit
                return;
            }

            //Level I bypass - Wendel Intranet
            bool             bIpOk      = false;
            string           strIPQuery = string.Format("select * from [IP_List] where [IP] = '{0}'", Request.UserHostAddress);
            OleDbDataAdapter dsCmd      = new OleDbDataAdapter(strIPQuery, IPDBConnection);
            dsCmd.Fill(myData, "IP_Exact");
            DataTable pTable = myData.Tables["IP_Exact"];
            if (pTable.Rows.Count > 0)
            {
                //Exact match to IP address
                bIpOk = true;
                //
            }
            else
            {
                strIPQuery = "select * from [IP_List] where instr(1,[IP],'*')";
                dsCmd      = new OleDbDataAdapter(strIPQuery, IPDBConnection);
                dsCmd.Fill(myData, "IP_SubNet");
                pTable = myData.Tables["IP_SubNet"];
                System.Data.DataRow pRow = null;
                foreach (DataRow pRow_loopVariable in pTable.Rows)
                {
                    pRow = pRow_loopVariable;
                    Wildcard wildcard = new Wildcard(pRow["IP"].ToString(), RegexOptions.IgnoreCase);
                    if (wildcard.IsMatch(Request.UserHostAddress) || wildcard.IsMatch(Session["sHOST"].ToString()))
                    {
                        bIpOk = true;
                        break; // TODO: might not be correct. Was : Exit For
                    }
                }
            }

            //'Level 1 DNS lookup bypass
            //'disable DNS lookup. it's slow
            //If Not bIpOk Then
            //    strIPQuery = "select * from [IP_List] where [DNSLookup] = true"
            //    dsCmd = New OleDbDataAdapter(strIPQuery, IPDBConnection)
            //    dsCmd.Fill(myData, "DNS_SubNet")
            //    pTable = myData.Tables("DNS_SubNet")
            //    Dim pRow As System.Data.DataRow
            //    For Each pRow In pTable.Rows
            //        Try
            //            If Request.UserHostAddress = System.Net.Dns.GetHostEntry(pRow.Item("IP").ToString).AddressList(0).ToString Then
            //                bIpOk = True
            //                Exit For
            //            End If
            //        Catch ex As Exception

            //        End Try
            //    Next
            //End If

            //token in URL
            if (Request["Token"] != null)
            {
                String[] userInfo = AESUtil.DecryptString(Request["Token"]).Split(new String[] { "|" }, StringSplitOptions.None);
                //if ip matched the token - allow regardless of ip address
                if (userInfo[0].ToUpper() == "WARIT" && userInfo[1] == Request.UserHostAddress)
                {
                    //token valid
                    DeveloperBypass();
                    return;
                }
                //developer bypass - allow if coming from any of the recognized ip and token contains any of the recognized ip
                else if (bIpOk)
                {
                    String           strTokenIPQuery = String.Format("select * from [IP_List] where [IP] = '{0}'", userInfo[1]);
                    OleDbDataAdapter dsTokenIPCmd    = new OleDbDataAdapter(strTokenIPQuery, IPDBConnection);
                    dsCmd.Fill(myData, "Token_IP_Exact");
                    DataTable pTokenIPTable = myData.Tables["Token_IP_Exact"];
                    if (pTable.Rows.Count > 0)
                    {
                        //token valid
                        DeveloperBypass();
                        return;
                    }
                }
            }

            if (bIpOk)
            {
                Session["INTERNAL_IP"] = true;
                if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["No_Internal_IP_Bypass"]) && bool.Parse(ConfigurationManager.AppSettings["No_Internal_IP_Bypass"]))
                {
                    messageContainer.Visible = true;
                    lblResults.Text          = "Internal IP bypass is disabled in this application. You need to login.";
                    lblResults.Font.Bold     = true;
                }
                else
                {
                    UpdateLogUser("ADMIN", "L1 Bypass");
                    //Level I bypass
                    //Internal Access - bypass login screen
                    Session["AllowEdit"] = false;
                    FormsAuthentication.RedirectFromLoginPage("ADMIN", false);
                    return;
                }
            }
        }

        //Restrict_IP_Address
        if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["Restrict_IP_Address"]) && bool.Parse(ConfigurationManager.AppSettings["Restrict_IP_Address"]))
        {
            bool             bUserIPOK          = false;
            OleDbConnection  myUserIPConnection = new OleDbConnection(ConfigurationManager.ConnectionStrings["_UserDatabase"].ConnectionString);
            string           strUserIPQuery     = "select * from [IP_Allow_List] where [IP] = '" + Request.UserHostAddress + "'";
            OleDbDataAdapter dsUserIPCmd        = new OleDbDataAdapter(strUserIPQuery, myUserIPConnection);
            DataSet          myUserIPData       = new DataSet();
            dsUserIPCmd.Fill(myUserIPData, "IP_Exact");
            DataTable pUserIPTable = myUserIPData.Tables["IP_Exact"];
            if (pUserIPTable.Rows.Count > 0)
            {
                //Exact match to IP address
                bUserIPOK = true;
            }
            else
            {
                strUserIPQuery = "select * from [IP_Allow_List] where instr(1,[IP],'*')";
                dsUserIPCmd    = new OleDbDataAdapter(strUserIPQuery, myUserIPConnection);
                dsUserIPCmd.Fill(myUserIPData, "IP_SubNet");
                pUserIPTable = myUserIPData.Tables["IP_SubNet"];
                System.Data.DataRow pRow = null;
                foreach (DataRow pRow_loopVariable in pUserIPTable.Rows)
                {
                    pRow = pRow_loopVariable;
                    if (Request.UserHostAddress.Length >= pRow["IP"].ToString().Length)
                    {
                        if (Request.UserHostAddress.Substring(0, pRow["IP"].ToString().Length - 1) == pRow["IP"].ToString().Substring(0, pRow["IP"].ToString().Length - 1))
                        {
                            bUserIPOK = true;
                            break; // TODO: might not be correct. Was : Exit For
                        }
                    }
                }
            }

            if (!bUserIPOK)
            {
                UserName.Disabled        = true;
                UserPass.Disabled        = true;
                cmdLogin.Enabled         = false;
                messageContainer.Visible = true;
                lblResults.Text          = "Sorry, access is not permitted from your IP Address. Please contact Wendel GIS department for assistance.";
                lblResults.Font.Bold     = true;
            }
        }
    }