Exemplo n.º 1
0
        /// <summary>
        /// Performs a scalar select and returns the value as a string
        /// </summary>
        /// <param name="qS"></param>
        /// <returns></returns>
        public static String ScalarString(String qS)
        {
            object returnValue = "";
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(qS, con);

            using (con)
            {
                if (con.State == ConnectionState.Open)
                {
                    returnValue = cmd.ExecuteScalar();
                    con.Close();
                }
                else
                {
                    con.Open();
                    returnValue = cmd.ExecuteScalar();
                    con.Close();
                }
            }

            if (returnValue == null)
            {
                return "";
            }
            else
                return returnValue.ToString();
        }
Exemplo n.º 2
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        Label1.Text = Server.HtmlEncode(this.elm1.Value);
        //Label1.Text = this.elm1.Value;

        //String QueryString = "INSERT INTO Article (Id, auteur, date, categorie) VALUES ('7', 'FCW', '', 'Informatique')";

        String QueryString = "INSERT INTO Article_Blog (ID, Auteur, Content, Tag) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "',  '" + this.elm1.Value + "','" + TextBox3.Text + "')";
        SqlConnection Cn = new SqlConnection("server=WAZZUP-PC\\SQLEXPRESS; initial catalog=TestDB; integrated security=true");

        try
        {

            Cn.Open();
            if (Cn != null)
            {
                Label1.Text = "CONNECTER";
                Console.WriteLine("CONNECTER");
            }

            SqlCommand Cmd = new SqlCommand();
            Cmd.CommandText = QueryString;
            //Cmd.CommandType = CommandType.Text;
            Cmd.Connection = Cn;

            Cmd.ExecuteNonQuery();

            Cn.Close();
        }
        catch (Exception Ex)
        {
            Label1.Text = Ex.ToString();
            Cn.Close();
        };
    }
Exemplo n.º 3
0
 public static DataTable ExecuteData(CommandType cmdCommandType, string cmdCommandString, params SqlParameter[] cmdParameters)
 {
     SqlCommand cmdCommand = new SqlCommand();
     SqlConnection connect = new SqlConnection(ConnectionString.Text);
     try
     {
         DataTable dattTopic = new DataTable();
         SqlDataAdapter dataTopic = new SqlDataAdapter(cmdCommand);
         PrepareCommand(cmdCommand, connect, null, cmdCommandType, cmdCommandString, cmdParameters);
         cmdCommand.ExecuteNonQuery();
         dataTopic.Fill(dattTopic);
         cmdCommand.Parameters.Clear();
         if (connect.State == ConnectionState.Open) connect.Close();
         return dattTopic;
     }
     catch (SqlException ex)
     {
         if (connect.State == ConnectionState.Open)
         {
             connect.Close();
             SqlConnection.ClearPool(connect);
         }
         throw ex;
     }
 }
Exemplo n.º 4
0
        public static Boolean delete_rams(string strConnDB, string txt_week)
        {
            SqlConnection objConn = new SqlConnection();
            objConn.ConnectionString = strConnDB;
            objConn.Open();
            try
            {
                SqlCommand cmd_rams_delete = new SqlCommand("usp_BH_RAMS_Delete", objConn);
                cmd_rams_delete.CommandTimeout = 0;
                cmd_rams_delete.CommandType = CommandType.StoredProcedure;
                cmd_rams_delete.Parameters.Add("@Week", SqlDbType.VarChar).Value = txt_week;
                cmd_rams_delete.ExecuteNonQuery();

                objConn.Close();
                objConn = null;
                return true;
            }
            catch (Exception ex)
            {
                objConn.Close();
                objConn = null;
                PrimaryHaul_WS.PH_ExceptionManager.WriteError(ex.Message);
                return false;
            }
        }
Exemplo n.º 5
0
        public string insertandUpdate( string commandString)
        {
            string result = null; ;
            string connectonString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
              //  string commandString = String.Format("SELECT username FROM t_user where username='******'", username);
            SqlConnection sqlConnection = new SqlConnection(connectonString);
            try
            {
                sqlConnection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = sqlConnection;
                command.CommandText = commandString;
                int num = command.ExecuteNonQuery();
                if (num>0)
                result = "true@执行成功";
                else
                    result = "false@未找到值";
                sqlConnection.Close();

            }
            catch (Exception e)
            {
                result = "false@" + e.Message;
            }
            finally
            {
                sqlConnection.Close();
            }

            return result;
        }
Exemplo n.º 6
0
 public List<ItemTarefa> buscaItensTarefa(int id)
 {
     List<ItemTarefa> listaItens = new List<ItemTarefa>();
     SqlConnection conexao = new SqlConnection();
     conexao.ConnectionString = StaticObjects.strConexao;
     SqlCommand comando = new SqlCommand();
     SqlDataReader leitor;
     try
     {
         conexao.Open();
         comando.CommandText = @"SELECT id,idTarefa,data,descricao FROM dbo.ItensTarefa WHERE idTarefa = " + id + " ";
         comando.Connection = conexao;
         leitor = comando.ExecuteReader();
         while (leitor.Read())
         {
             ItemTarefa it = new ItemTarefa();
             it.id = Convert.ToInt16(leitor["id"]);
             it.idTarefa = Convert.ToInt16(leitor["idTarefa"]);
             it.data = Convert.ToDateTime(leitor["data"]);
             it.descricao = leitor["descricao"].ToString();
             listaItens.Add(it);
         }
         conexao.Close();
         return listaItens;
     }
     catch (Exception)
     {
         conexao.Close();
         return null;
     }
 }
Exemplo n.º 7
0
 private void loginbutton_Click(object sender, EventArgs e)
 {
     string sqlconnstr = "Data Source=" + addersstextbox.Text + ";uid=" + accounttextbox.Text +
         ";pwd=" + pwdtextbox.Text + ";Initial Catalog="+DBNameBox.Text+";";
     SqlConnection SqlConn = new SqlConnection(sqlconnstr);
     if (SqlConn.State == ConnectionState.Open)
     {
         SqlConn.Close();
     }
     try
     {
         SqlConn.Open();
     }
     catch (Exception ex)
     {
         System.Windows.Forms.MessageBox.Show(ex.Message);
     }
     if (SqlConn.State == ConnectionState.Open)
     {
         SqlConn.Close();
         MainForm dm = new MainForm(SqlConn);
         dm.Show();
         this.Hide();
     }
 }
Exemplo n.º 8
0
        protected long Execute(string procName, SqlParameter[] param)
        {
            long retVal = -1;
            SqlConnection con = new SqlConnection(GetConnection());
            SqlCommand cmd = null;
            try
            {

                con.Open();
                cmd = new SqlCommand();
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(param);
                cmd.Connection = con;
                retVal = cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception)
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
            return retVal;
        }
Exemplo n.º 9
0
        protected void ButtonEnviar_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistroConnectionString"].ConnectionString);
                conn.Open();
                String contar_mensajes = "select count(*) from Mensaje_privado";
                SqlCommand command = new SqlCommand(contar_mensajes, conn);
                int contador = Convert.ToInt32(command.ExecuteScalar().ToString());
                conn.Close();

                conn.Open();
                String enviar_mensaje = "insert into Mensaje_privado (id_mensaje, id_remitente, id_buzon, leido, mensaje, asunto, fecha_de_envio) values (" + contador + ", " + Bandeja_Entrada.id_usuario + ", " + Bandeja_Entrada.id_destino + ", " + 0 + ", '" + TextBoxEM.Text + "', '" + LabelAsunto.Text + "', CURRENT_TIMESTAMP)";
                command = new SqlCommand(enviar_mensaje, conn);
                command.ExecuteNonQuery();
                Response.Write("Mensaje enviado!");
                Panel1.Visible = false;
                ButtonVolver.Visible = true;

                conn.Close();
            }
            catch (Exception ex)
            {
            }
        }
Exemplo n.º 10
0
        private void onload()
        {
            string strSQLconnection = @"Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aviation.mdf; Trusted_Connection=True;User Instance=True";
            SqlConnection sqlConnection = new SqlConnection(strSQLconnection);
            SqlCommand sqlCommand = new SqlCommand("SELECT p.username,p.nama,p.jawatan,p.bahagian,p.telefon_bimbit,p.telefon_pejabat,p.email FROM profil_pegawai p, supervisor s WHERE p.username = s.ic ORDER BY p.nama", sqlConnection);

                try
                {
                    if (sqlConnection.State.Equals(ConnectionState.Open))
                        sqlConnection.Close();

                    if (sqlConnection.State.Equals(ConnectionState.Closed))
                        sqlConnection.Open();

                    SqlDataAdapter mySqlAdapter = new SqlDataAdapter(sqlCommand);
                    DataSet myDataSet = new DataSet();
                    mySqlAdapter.Fill(myDataSet);

                    gridpenyelia.DataSource = myDataSet;
                    gridpenyelia.DataBind();

                    if (sqlConnection.State.Equals(ConnectionState.Open))
                        sqlConnection.Close();

                    if (sqlConnection.State.Equals(ConnectionState.Closed))
                        sqlConnection.Open();
                }
                catch (SqlException ex)
                {
                }
                finally
                {

                }
        }
Exemplo n.º 11
0
    protected void btnBookFlight_Click(object sender, EventArgs e)
    {
        SqlConnection objCon = new SqlConnection(strCon);

        try
        {
            objCon.ConnectionString = strCon;
            objCon.Open();
        }
        catch (Exception ex)
        {
            this.lblConnect.Text = "Unable to connect to database.";
        }

        if (objCon.State == System.Data.ConnectionState.Open)
        {
            InsertFlightInfo();
            InsertFlightOrder();

            objCon.Close();
            objCon.Dispose();
        }
        else
        {
            lblConnect.Text = "Unable to connect to database.";
            objCon.Close();
            objCon.Dispose();

        }
        objCon.Close();
        objCon.Dispose();

        redirectToNextPage();
    }
Exemplo n.º 12
0
    public string log(string uname, string pwd)
    {
        //if (uname == "admin" && pwd == "admin")
        //{
        //    return "success";
        //}
        //else
        //{
        //    return "fail";
        //}

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["dbString"].ConnectionString);
        conn.Open();
        SqlCommand cmd = new SqlCommand("Select * from Table2 where Userid='" + uname + "' AND Password = '******' ", conn);
        SqlDataReader data = null;
        data = cmd.ExecuteReader();
        if (data.HasRows)
        {
            data.Dispose();
            cmd.Dispose();
            conn.Close(); return "Y";
        }
        else
        {
            data.Dispose();
            cmd.Dispose();
            conn.Close(); return "N";
        }
    }
Exemplo n.º 13
0
        protected void Booking_DeleteCommand(object source, DataListCommandEventArgs e)
        {
            int bookingid;
            bookingid = Convert.ToInt32(e.CommandArgument);

            SqlConnection con = new SqlConnection(strconn);
            con.Open();

            SqlCommand cmd = new SqlCommand("Delete from schedule where id = '" + bookingid + "'", con);
            SqlCommand cmd2 = new SqlCommand("UPDATE  carOwner  SET   MyPoints =  MyPoints+200   where carOwner.ownerID = (Select schedule.OwnerID from schedule where id = '" + bookingid + "')", con);

            int res2 = cmd2.ExecuteNonQuery();
            int res = cmd.ExecuteNonQuery();
            if (res > 0 && res2 > 0)
            {
                Label5.Visible = true;
                Label5.Text = "Your booking has been cancelled and you have been refunded back with 200 points";
                con.Close();
            }
            else
            {
                Label5.Visible = true;
                Label5.Text = "Error in booking cancelling try again later";
                con.Close();
            }

            displayBooking();
        }
Exemplo n.º 14
0
        protected void Load_Grid()
        {
            string connectionString;
            connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();

            SqlConnection con = new SqlConnection(connectionString);

            try
            {
                con.Open();
            }
            catch (Exception)
            {
                con.Close();
                return;
                throw;
            }

            SqlCommand cmd = new SqlCommand("SELECT C.*, A.agent_name FROM Customers AS C INNER JOIN Users AS U ON C.user_id = U.user_id INNER JOIN Employees AS E ON E.user_id = U.user_id INNER JOIN Agents AS A ON A.agent_id = E.agent_id", con);
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable tablo = new DataTable();
            tablo.Load(dr);

            CustomerGrid.DataSource = tablo.DefaultView;
            CustomerGrid.DataBind();

            dr.Close();
            con.Close();
        }
Exemplo n.º 15
0
        private void TryDBConnect()
        {
            bool IsConnect = false;

            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);

            XLog.Write("正在连接数据库...");
            try
            {
                connection.Open();
                if (connection.State == ConnectionState.Open)
                {
                    IsConnect = true;
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                IsConnect = false;
                connection.Close();
            }

            if (IsConnect == false)
            {
                DialogResult result = MessageBox.Show("无法连接至数据库服务器,请确认配置是否正确,网络是否畅通?", "无法连接至数据库服务器...", MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning);
                if (result == DialogResult.Retry)
                {
                    TryDBConnect();
                }
            }
        }
Exemplo n.º 16
0
        public Task Send(Message[] messages)
        {
            if (messages == null || messages.Length == 0)
            {
                return TaskAsyncHelper.Empty;
            }

            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(_connectionString);
                connection.Open();
                using (var cmd = new SqlCommand(_insertSql, connection))
                {
                    cmd.Parameters.AddWithValue("Payload", JsonConvert.SerializeObject(messages));

                    return cmd.ExecuteNonQueryAsync()
                        .Then(() => connection.Close()) // close the connection if successful
                        .Catch(ex => connection.Close()); // close the connection if it explodes
                }
            }
            catch (SqlException)
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
                throw;
            }
        }
Exemplo n.º 17
0
 /// <summary>
 /// Panels the type identifier.
 /// </summary>
 /// <param name="panelTypeCode">The panel type code.</param>
 /// <returns></returns>
 
 public int PanelTypeId(string panelTypeCode)
 {
     var panelTypeId = 1;
     using (var con = new SqlConnection(Properties.Settings.Default.ConnectionToSQL))
     {
         try
         {
             con.Open();
             var sqlCommand =
                 new SqlCommand(@"SELECT *  FROM [AirVents].[PanelType] WHERE[PanelTypeCode]   = '" + panelTypeCode + "'", con);
             var sqlDataAdapter = new SqlDataAdapter(sqlCommand);
             var dataTable = new DataTable("panelTypeName");
             sqlDataAdapter.Fill(dataTable);
             panelTypeId =  Convert.ToInt32(dataTable.Rows[0]["PanelTypeID"]);
         }
         catch (Exception)
         {
             con.Close();
         }
         finally
         {
             con.Close();
         }
     }
     return panelTypeId;
 }
 public bool Eliminar(string empresaid, DataTable TablaTrab)
 {
     using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
     {
         bool xreturn = true;
         int lcont = 0;
         for (lcont = 0; lcont <= TablaTrab.Rows.Count - 1; lcont++)
         {
             using (SqlCommand cmd = new SqlCommand("gspTbPllaCargos_ELIMINAR", cnx))
             {
                 cmd.CommandTimeout = 0;
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add("@cencosid", SqlDbType.Char, 5).Value = TablaTrab.Rows[lcont]["cencosid"];
                 cmd.Parameters.Add("@cargoid", SqlDbType.Char, 3).Value = TablaTrab.Rows[lcont]["cargoid"];
                 try
                 {
                     cnx.Open();
                     if (cmd.ExecuteNonQuery() > 0)
                     { xreturn = true; }
                     else
                     { xreturn = false; }
                     cnx.Close();
                 }
                 catch (Exception ex)
                 {
                     Sql_Error = ex.Message;
                     //throw new Exception(ex.Message);
                     xreturn = false;
                     cnx.Close();
                 }
             }
         }
         return xreturn;
     }
 }
Exemplo n.º 19
0
        /// <summary>
        /// 执行SQL语句返回影响的行数
        /// </summary>
        /// <param name="cmdText">SQL文本</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            int retVal = 0;
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand comm = new SqlCommand();

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                comm.Connection = conn;
                comm.CommandText = cmdText;
                comm.CommandTimeout = 600;
                retVal = comm.ExecuteNonQuery();
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

                return retVal;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
Exemplo n.º 20
0
        public bool checkMail(string email, int type)
        {
            string query = "";
            SqlConnection con = new SqlConnection(connectionString);
            try
            {
                con.Open();
                if (type == 1)
                    query = "SELECT teacher_email FROM Teachers WHERE teacher_email = '" + email + "';";
                else // student
                    query = "SELECT student_email FROM Students WHERE student_email = '" + email + "';";
                SqlCommand command = new SqlCommand(query, con);

                SqlDataReader reader = command.ExecuteReader();
                // check how many rows the sql query found
                if (reader.HasRows == true)
                {
                    return true;
                }
                con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return false;
        }
Exemplo n.º 21
0
    public static string ConfirmQrCode(int qrCode, string newUserOpenId)
    {
        string originalUserOpenId = "";
        SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["constr"].Trim());
        SqlCommand cmd = new SqlCommand(" select qr_invite_list_owner from qr_invite_list where qr_invite_list_id = " + qrCode.ToString(), conn);
        conn.Open();
        SqlDataReader drd = cmd.ExecuteReader();
        if (drd.Read())
        {
            originalUserOpenId = drd.GetString(0);
        }
        drd.Close();
        conn.Close();
        cmd.CommandText = " insert into qr_invite_list_detail ( qr_invite_list_detail_id , qr_invite_list_detail_openid ) "
            + " values ('" + qrCode.ToString() + "' , '" + newUserOpenId.Replace("'", "").Trim() + "' ) ";
        conn.Open();
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch
        {

        }
        conn.Close();
        return originalUserOpenId.Trim();
    }
 public requirements Insert(requirements id)
 {
     string ConnectionString = IDManager.connection();
     SqlConnection con = new SqlConnection(ConnectionString);
     try
     {
     con.Open();
     SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_REQUIREMENTS", con);
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc);
     cmd.ExecuteReader();
     con.Close();
     con.Open();
     cmd = new SqlCommand("SP_DMCS_GET_REQUIREMENTS", con);
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc);
     SqlDataReader rdr = cmd.ExecuteReader();
     if (rdr.HasRows)
     {
         rdr.Read();
         id.req_id = rdr.GetInt32(0);
     }
     }
     catch (Exception ex)
     {
     id.SetColumnDefaults();
     }
     finally
     {
     con.Close();
     }
     return id;
 }
Exemplo n.º 23
0
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string flag = context.Request["flag"].ToString();
            string struid = context.Session["ID"].ToString();
            string strSQL = "update dbo.tabUsers set ";
            string strDataConn = ConfigurationManager.ConnectionStrings["SQLDataConnStr"].ConnectionString;
            SqlConnection dataConn = new SqlConnection(strDataConn);

            if (flag == "1")
            {
                string strusername = context.Request["username"].ToString();
                string strutell = context.Request["utell"].ToString();
                string struemail = context.Request["uemail"].ToString();
                strSQL += "UserName = '******',"+" UserTel = '"+strutell+"',"+"UserEmail = '"+struemail+"' where ID = '"+struid+"'";
                SqlCommand command = new SqlCommand(strSQL, dataConn);
                dataConn.Open();
                command.ExecuteNonQuery();
                dataConn.Close();
                context.Response.Write("OK");
            }
            else if (flag == "2")
            {
                string strpw = context.Request["upassword"].ToString();
                strSQL +=  "UserPW = '" + strpw + "' where UserID = '" + struid + "'";
                SqlCommand command = new SqlCommand(strSQL, dataConn);
                dataConn.Open();
                command.ExecuteNonQuery();
                dataConn.Close();
                context.Response.Write("OK");
            }
        }
        public DataSet getData(SqlConnection conn, SqlDataAdapter da, DataSet ds)
        {
            //don't forget to escape slashes
            string connString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\PROG34998\\finalProject\\Project1\\Store.mdf;Integrated Security=True;User Instance=True";
            try
            {
                conn = new SqlConnection(connString);
                conn.Open();

                string sql = "SELECT * FROM [tCustomer]";
                da = new SqlDataAdapter(sql, conn);

                ds = new DataSet();
                da.Fill(ds, "tCustomer");
                conn.Close();

            }
            catch (SqlException ex)
            {
                if (conn != null)
                {
                    conn.Close();
                }
                MessageBox.Show(ex.Message, "Error Retrieving from DataBase");
            }

            return ds;
        }
        private void button11_Click(object sender, EventArgs e)
        {
            string con = "server=Crazyboy-PC;Initial Catalog=master;Integrated Security=SSPI";
            string sql = "select Cbill,Cstyle,Csum,Ctime,CID,Gno from cash where Ctime='" + dateTimePicker2.Text.ToString() + "'";
            SqlConnection mycon = new SqlConnection(con);
            SqlCommand cmd = new SqlCommand(sql, mycon);
            if (mycon.State == ConnectionState.Closed)
                mycon.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dataGridView1.DataSource = 0;
            if (dr.HasRows)
            {
                BindingSource bs = new BindingSource();
                bs.DataSource = dr;
                this.dataGridView1.DataSource = bs;
                dataGridView1.Columns[0].HeaderCell.Value = "账单号";
                dataGridView1.Columns[1].HeaderCell.Value = "消费类型";
                dataGridView1.Columns[2].HeaderCell.Value = "消费金额";
                dataGridView1.Columns[3].HeaderCell.Value = "消费时间";
                dataGridView1.Columns[4].HeaderCell.Value = "收银员";
                dataGridView1.Columns[5].HeaderCell.Value = "客房号";
            }

            mycon.Close();

            //关闭连接并释放资源
            if (ConnectionState.Open == mycon.State)
            {
                mycon.Close();
            }
            mycon.Dispose();
        }
Exemplo n.º 26
0
        public void ExecuteCommand(string sqlstr)
        {
            string connectionString;
            connectionString = ConfigurationManager.ConnectionStrings["conStr"].ToString();

            SqlConnection con = new SqlConnection(connectionString);

            try
            {
                con.Open();
            }
            catch (Exception)
            {
                con.Close();
                throw;
            }

            try
            {
                SqlCommand command = new SqlCommand(sqlstr, con);
                command.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception)
            {
                con.Close();
                throw;
            }
        }
Exemplo n.º 27
0
 public void Delete(DataGridView dataGridView1)
 {
     connect = DataBaseConnection.getInstance().getConnect();
     try
     {
         foreach (DataGridViewRow item in dataGridView1.Rows)
         {
             if ((bool)item.Cells[0].Value)
             {
                 connect.Open();
                 SqlCommand cmd = new SqlCommand("delete from [Table] where Name = '"+item.Cells[1].Value.ToString()+"'", connect);
                 cmd.ExecuteNonQuery();
                 connect.Close();
                // dataGridView1.Rows.Remove(item);
                 ShowMessages.PutMessageInContainer("Delete Operation  was succeeded", Color.Tomato);
             }
         }
         MessageBox.Show("SuccessFully Deleted ..!");
         dataGridView1.Rows.Clear();
         update(dataGridView1);
     }
     catch (Exception EX)
     {
         MessageBox.Show(EX.StackTrace.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
         ShowMessages.PutMessageInContainer("Error within Delete Operation ", Color.Red);
     }
     finally
     {
         connect.Close();
     }
 }
Exemplo n.º 28
0
 public bool AddProduct(ProductsDto productsDto)
 {
     var cn = new SqlConnection(GetConnection());
     SqlTransaction trx = null;
     var isInsert = false;
     try
     {
         cn.Open();
         trx = cn.BeginTransaction();
         string cmdText = " insert into Products(ProductName,ProductCategory,MemberAnaylst,OrganizationName,TenureId,StyleResearchId,StrategyId,FrequencyCall,FrequencyCallType,Logo,CreateUserId,CreateTimeStamp,ModifiedUserId,ModifiedTimeStamp) " +
                          "values('" + productsDto.ProductName + "'," + productsDto.ProductCategory + "," + productsDto.MemberAnaylst + ",'" + productsDto.OrganizationName + "'," + productsDto.TenureId + "," + productsDto.StyleResearchId + "," + productsDto.StrategyId + "," + productsDto.FrequencyCall + ",'" + productsDto.FrequencyCallType + "','" + productsDto.Logo + "'," + productsDto.CreateUserId + ", '" + DateTime.Now.ToString("yyyy-MM-dd") + "'," + productsDto.ModifiedUserId + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "') select Scope_Identity();";
         var cmd = new SqlCommand(cmdText, cn) { Transaction = trx };
         var productId = Convert.ToInt32(cmd.ExecuteScalar());
         foreach (var documents in productsDto.ProductDocumentDtos)
             (new SqlCommand("insert into ProductDocument(ProductId,FileName,DocumentName) values( " + productId + ",'" + documents.FileName + "','" + documents.DocumentName + "')", cn) { Transaction = trx }).ExecuteNonQuery();
         trx.Commit();
         isInsert = true;
         cn.Close();
     }
     catch (Exception)
     {
         if (trx != null) trx.Rollback();
         cn.Close();
     }
     return isInsert;
 }
Exemplo n.º 29
0
        public static object ReturnValueFromTable(string NomeTabella, string NomeColonna,string Where)
        {
            string sSQL = "SELECT " + NomeColonna + " FROM " + NomeTabella + " WHERE " + Where;
            SqlConnection objCN = new SqlConnection(cnnStringSettings);
            SqlDataReader dr = null;
            try
            {
                SqlCommand cmd = new SqlCommand(sSQL, objCN);
                objCN.Open();
                dr = cmd.ExecuteReader();
                object objValue = -1;
                while (dr.Read())
                {
                    objValue = dr[NomeColonna];
                }
                dr.Close();
                objCN.Close();
                return objValue;
            }
            catch
            {
                if (!dr.IsClosed)
                    dr.Close();
                objCN.Close();
                return null;
            }

        }
    private void LoadProviders()
    {
        Connection = new SqlConnection(ConnectionString);
        DataTable dt = new DataTable();
        try
        {
            command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.Connection = Connection;
            Connection.Open();
            command.CommandText = "SELECT * FROM tbl_API_Providers where provider_status=1";
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);
            Connection.Close();
        }
        catch (Exception ex)
        {
            //to do
            //Log Exception
        }
        finally
        {
            if (Connection.State == ConnectionState.Open)
                Connection.Close();
        }

        ddlProviders.DataSource = dt;
        ddlProviders.DataTextField = "Provider_Name";
        ddlProviders.DataValueField = "Provider_ID";
        ddlProviders.DataBind();
        ddlProviders.Items.Insert(0, new ListItem("Please select", "0"));
    }
Exemplo n.º 31
0
 public static void TryClose(this sql.SqlConnection connection)
 {
     if (connection?.State != System.Data.ConnectionState.Closed)
     {
         connection?.Close();
     }
 }
 void FinishOperation(System.Action callback)
 {
     try {
         callback();
     }
     catch {
         _commitableTransaction?.Rollback();
         throw;
     }
     finally {
         _connection?.Close();
         _disposed = true;
     }
 }
Exemplo n.º 33
0
    public void BindDropDown(DropDownList dd, string query)
    {
        string connnecstring = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnstr"].ConnectionString;

        System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(connnecstring);
        System.Data.SqlClient.SqlCommand    cmd = new System.Data.SqlClient.SqlCommand(query, cn);
        string value = ""; int i = 0;

        cn.Open();
        var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            value = value + reader[0] + ",";
        }
        reader.Close(); reader.Dispose(); cn.Close(); cn.Dispose();

        string[] split = Regex.Split(value, ",");
        while (i < (split.Length - 1))
        {
            dd.Items.Add(split[i]);
            i++;
        }
    }
Exemplo n.º 34
0
    private bool compareOne(string item, string table, string field)
    {
        int  result     = 0;
        bool compareOne = true;

        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = @"Server =Localhost ;Database=Lab2;Trusted_Connection=Yes;";
        System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
        insert.Connection = sc;
        sc.Open();
        insert.CommandText = "select Count(*) FROM [dbo].[" + table + "] WHERE UPPER(" + field + ") LIKE '" + item.ToUpper() + "'";
        result             = (int)insert.ExecuteScalar();
        sc.Close();
        if (result > 0)
        {
            compareOne = false;
            return(compareOne);
        }

        else
        {
            return(compareOne);
        }
    }
Exemplo n.º 35
0
    //Message Organization
    protected void Button3_Click1(object sender, EventArgs e)
    {
        // Stopped here before class. Need to get the query result from the database (the business email) and store that as a variable to pass
        // to the client script start up


        String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);



        sql.Open();
        System.Data.SqlClient.SqlCommand approveScholarship = new System.Data.SqlClient.SqlCommand();
        approveScholarship.Connection  = sql;
        approveScholarship.CommandText = "SELECT EmailAddress FROM  UserEntity where UserEntityID = " + Session["selectedjobID"];
        approveScholarship.ExecuteNonQuery();
        sql.Close();
        //UPDATE WITH QUERIES
        string email = "*****@*****.**";

        ClientScript.RegisterStartupScript(this.GetType(), "mailto", "parent.location='mailto:" + email + "'", true);
        Response.Redirect("~/ArchiveOpportunities.aspx");
    }
Exemplo n.º 36
0
    protected bool query(string stmt, string[] options)
    {
        try
        {
            localCN = new System.Data.SqlClient.SqlConnection(connectionString);
            localCN.Open();
        }
        catch (System.Exception ex)
        {
            GlobalShared.Log.Log((int)LogClass.logType.ErrorCondition, "Unable to open DB connection <" + localCN.ConnectionString + "> for query <" + stmt + ">: " + ex.Message);
            return(false);
        }
        try
        {
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection  = localCN;
            cmd.CommandText = prepareStatement(stmt, options);
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            GlobalShared.Log.Log((int)LogClass.logType.ErrorCondition, "Executing Query <" + stmt + ">: " + ex.Message);
            return(false);
        }
        finally
        {
            try
            {
                localCN.Close();
            }
            catch { }
        }

        return(true);
    }
    protected void SubmitForm(object sender, EventArgs e)
    {
        String uname            = String.Format("{0}", Request.Form["uname"]);
        String pword            = String.Format("{0}", Request.Form["pword"]);
        String connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename = " + "D:\\workSpaces\\studio2017\\repos\\Hostel Management System Rushi\\Hostel Management System Rushi\\App_Data\\Database.mdf" + "; Integrated Security = True";

        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString);
        con.Open();
        Console.WriteLine("connection opened successfully");
        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select * from Users where uname='" + uname + "' and pword='" + pword + "'", con);

        System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();

        if (reader.Read())
        {
            ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "Show", "alert('Login Successful');", true);
        }
        else
        {
            ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "Show", "alert('Login Failed');", true);
        }
        con.Close();
        Console.WriteLine("connection closed successfully");
    }
Exemplo n.º 38
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        System.Data.SqlClient.SqlCommand dbCommand;
        ConnectionStringSettings         cs;

        cs = ConfigurationManager.ConnectionStrings["ProjektdatabasConnectionString"];
        String connString = cs.ConnectionString;

        System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connString);
        dbConnection.Open();
        dbCommand = dbConnection.CreateCommand();

        if (TextBox1.Text != "" && TextBox2.Text != "" && TextBox3.Text != "" && TextBox4.Text != "" && TextBox5.Text != "")
        {
            string förnamn      = TextBox1.Text;
            string efternamn    = TextBox2.Text;
            string användarnamn = TextBox3.Text;
            string address      = TextBox4.Text;
            string nummer       = TextBox5.Text;

            dbCommand.CommandText = "INSERT INTO Kunder (Användarnamn, Förnamn, Efternamn, Address, Telefonnummer) Values (@anv, @fnamn, @enamn, @addr, @tel)";
            dbCommand.Parameters.Add("@anv", @användarnamn);
            dbCommand.Parameters.Add("@fnamn", @förnamn);
            dbCommand.Parameters.Add("@enamn", @efternamn);
            dbCommand.Parameters.Add("@addr", @address);
            dbCommand.Parameters.Add("@tel", @nummer);
            dbCommand.ExecuteNonQuery();
            dbConnection.Close();
            Response.Redirect("Kundregistrering_steg2.aspx");
        }
        else
        {
            Label1.Visible = true;
            Label1.Text    = "Du har glömt att fylla i ett eller flera av fälten ovan. Var vänlig fyll i alla fält.";
        }
    }
Exemplo n.º 39
0
    private void List()
    {
        if ("Act".RequestStr() != "List")
        {
            return;
        }
        string sqlwhere = " and status>0 and BGRQ between '" + "StartDate".RequestStr() + "' AND '" + DateTime.Parse("EndDate".RequestStr()).AddDays(1).ToString("yyyy-MM-dd") + "'";


        if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
        {
            sqlwhere += @" AND m.StatisticsCatlog IN (" + Server.UrlDecode("RPNAME".RequestStr()) + ") ";
        }

        if (!String.IsNullOrEmpty(Request.Params["bgmc"]))
        {
            sqlwhere += " and  m.name   LIKE '%" + Request.Params["bgmc"].Trim() + "%'";
        }
        if (!String.IsNullOrEmpty(Request.Params["wtbh"]))
        {
            sqlwhere += " and  WTBH LIKE '%" + Request.Params["wtbh"].Trim() + "%'";
        }
        if (!String.IsNullOrEmpty(Request.Params["bgbh"]))
        {
            sqlwhere += " and  BGBH LIKE '%" + Request.Params["bgbh"].Trim() + "%'";
        }
        if (!"NUM".RequestStr().IsNullOrEmpty())
        {
            sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
        }
        else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
        {
            sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
        }

        BLL_LoginLog BLL = new BLL_LoginLog();

        #region  使用脚本分页

        string Sql = @" 
                        DECLARE @Page int
                        DECLARE @PageSize int
                        SET @Page = {1}
                        SET @PageSize = {2}
                        SET NOCOUNT ON
                        DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        select d.ID from sys_document  d left outer join sys_module m on d.ModuleId = m.id
                        where 1=1  {0}   Order By  BGRQ DESC

                        SELECT     
                        sys_document.ID,
	                    BGBH ,
	                    WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
                        sys_tree.description SegmentName, t2.description   CompanyName,t1.description TestRoomName
	                    ,sys_module.DeviceType ,
	                    ModuleId,  
                        sys_module.name as MName
                        FROM sys_document  
                        left outer join sys_module on sys_document.ModuleId = sys_module.id
                        left outer join sys_tree on sys_document.SegmentCode = sys_tree.nodecode
                        left outer join sys_tree t1 on sys_document.TestRoomCode = t1.nodecode
                        left outer join sys_tree t2 on sys_document.CompanyCode = t2.nodecode 
 
                        INNER JOIN @TempTable t ON sys_document.ID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                        Order By  BGRQ DESC

                        DECLARE @C int
                        select @C= count(d.ID)  from sys_document d left outer join sys_module m on d.ModuleId = m.id  where 1=1  {0}  
                        select @C 
                        ";

        string PageIndex = Request["page"];
        string PageSize  = Request["rows"];

        Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);


        DataSet DSs = new DataSet();
        using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
        {
            Conn.Open();
            using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
            {
                using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
                {
                    Adp.Fill(DSs);
                }
            }
            Conn.Close();
        }


        int records = DSs.Tables[1].Rows[0][0].ToString().Toint();

        #endregion


        if (DSs.Tables[0] != null)
        {
            Response.Write(DSs.Tables[0].ToJsonForEasyUI(records, ""));
        }

        Response.End();
    }
Exemplo n.º 40
0
    private void insertIntoDB(Employee a)
    {
        //When the program runs initially it should delete all the info in the tables
        try
        {
            System.Data.SqlClient.SqlConnection sqlc = connectToDB();

            //Creates a new sql insert command to insert the data from the arrays into the Employee table
            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            insert.Connection = sqlc;

            /* Create the insert statement
             * if the user doesn't input data for the non-required fields
             * then NULL values are input into the database */
            insert.CommandText += "insert into [dbo].[EMPLOYEE] values (" + a.EmployeeID + ", '" + a.FirstName + "','" + a.LastName;
            if (a.MiddleName == "NULL")
            {
                insert.CommandText += "',NULL,'";
            }
            else
            {
                insert.CommandText += "','" + a.MiddleName + "','";
            }

            insert.CommandText += a.HouseNum + "','" + a.Street + "','" + a.County;
            if (a.State == "NULL")
            {
                insert.CommandText += "',NULL,'";
            }
            else
            {
                insert.CommandText += "','" + a.State + "','";
            }

            insert.CommandText += a.Country + "','" + a.Zip + "','" + a.DateOfBirth + "','" + a.HireDate;
            if (a.TerminationDate == DateTime.MinValue)
            {
                insert.CommandText += "',NULL,";
            }
            else
            {
                insert.CommandText += "','" + a.TerminationDate + "',";
            }

            insert.CommandText += a.Salary;

            if (a.ManagerID == -1)
            {
                insert.CommandText += ",NULL,'";
            }
            else
            {
                insert.CommandText += "," + a.ManagerID + ",'";
            }
            insert.CommandText += a.LastUpdatedBy + "','" + a.LastUpdated + "')";


            insert.ExecuteNonQuery();

            sqlc.Close();
        }
        catch (Exception c)
        {
            //Shows an error message if there is a problem connecting to the database
            resultMessage.Text += "Database Error 2";
            resultMessage.Text += c.Message;
        }
    }
Exemplo n.º 41
0
    /*
     * protected void getBinLocation_Click(object sender, EventArgs e)
     * {
     *  Boolean s = FindBinLocation(BinLocationItemCode.Text);
     *  if (s == false)
     *  {
     *      alert2.Text = " Item not found";
     *      alert2.Style.Add("color", "red");
     *      alert2.Visible = true;
     *  }
     *  else
     *  {
     *      alert2.Visible = false;
     *  }
     * }
     */
    public String EmployeePriceInfo(String EnteredSku)
    {
        try
        {
            String sResult = "";
            Grid.ID                            = "Grid";
            Grid.Caption                       = "Employee Price";
            Grid.AutoGenerateColumns           = false;
            Grid.AlternatingRowStyle.BackColor = System.Drawing.Color.White;
            Grid.ForeColor                     = System.Drawing.Color.Black;
            Grid.HeaderStyle.BackColor         = System.Drawing.Color.Gray;
            Grid.BorderColor                   = System.Drawing.Color.Black;
            Grid.Width                         = 650;
            Grid.RowStyle.BackColor            = System.Drawing.Color.Gainsboro;
            Grid.CellPadding                   = 4;
            Grid.CellSpacing                   = 4;

            Grid.ShowFooter = false;
            TemplateField tf = null;
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("Sku", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("Sku", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("Description", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("Description", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("QAV", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("QAV", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("Price", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("Price", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("Min", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("Min", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("Inner", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("Inner", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("Case", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("Case", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);
            tf = new TemplateField();
            tf.HeaderTemplate = new DynamicGridViewTextTemplate("PLine", DataControlRowType.Header);
            tf.ItemTemplate   = new DynamicGridViewTextTemplate("PLine", DataControlRowType.DataRow);
            Grid.Columns.Add(tf);

            if (ViewState["employeePriceTable"] != null)
            {
                employeePriceTable = (System.Data.DataTable)ViewState["employeePriceTable"];
            }

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CN_MAS_RDP"].ConnectionString))
            {
                System.Data.SqlClient.SqlDataReader reader2 = null;
                System.Data.SqlClient.SqlCommand    cmd2    = new System.Data.SqlClient.SqlCommand();

                String query = "SELECT ItemNumber, ItemDescription, QtyOnHand, QtyOnSalesOrder, AveCost, QuotedEach, salesUnit, MinSalesUnitInner, MinSalesUnitPurchase, ProductLine FROM vw_Item_Setup where ItemNumber=@Entered_Sku and active ='Y' and ItemNumber like '3%' and  (Productline<>'dc' or Productline<>'hold')";
                cmd2.CommandText = query;
                cmd2.CommandType = System.Data.CommandType.Text;
                cmd2.Connection  = conn;
                cmd2.Parameters.Add("@Entered_Sku", System.Data.SqlDbType.VarChar, 30);
                cmd2.Parameters["@Entered_Sku"].Value = EnteredSku;
                conn.Open();
                reader2 = cmd2.ExecuteReader();

                if (reader2.HasRows == true)
                {
                    while (reader2.Read())
                    {
                        employeePriceTable.Rows.Add();
                        if (!(reader2["ItemNumber"]).Equals(System.DBNull.Value))
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Sku"] = (String)(reader2["ItemNumber"]);
                        }
                        else
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["ItemNumber"] = "Not found";
                        }
                        if (!(reader2["ItemDescription"]).Equals(System.DBNull.Value))
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Description"] = (String)(reader2["ItemDescription"]);
                        }
                        else
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Description"] = "Not found";
                        }

                        if (!(reader2["QtyOnHand"]).Equals(System.DBNull.Value))
                        {
                            if (!(reader2["QtyOnSalesOrder"]).Equals(System.DBNull.Value))
                            {
                                employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["QAV"] = System.Convert.ToString(Math.Round((decimal)(reader2["QtyOnHand"])) - Math.Round((decimal)(reader2["QtyOnSalesOrder"])));
                            }
                            else
                            {
                                employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["QAV"] = System.Convert.ToString(Math.Round((decimal)(reader2["QtyOnHand"])));
                            }
                        }
                        else
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["QAV"] = "Not found";
                        }
                        if (!(reader2["AveCost"]).Equals(System.DBNull.Value))
                        {
                            if ((decimal)(reader2["AveCost"]) >= 0)
                            {
                                employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Price"] = System.Convert.ToString((Math.Round((decimal)(reader2["AveCost"]) * 1000) / 1000) * 1.1m);
                            }
                            else
                            {
                                if (!(reader2["QuotedEach"]).Equals(System.DBNull.Value))
                                {
                                    employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Price"] = System.Convert.ToString((Math.Round((decimal)(reader2["QuotedEach"]) * 1000) / 1000) * 1.1m);
                                }
                                else
                                {
                                    employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Price"] = "Not found";
                                }
                            }
                        }
                        if (!(reader2["salesUnit"]).Equals(System.DBNull.Value))
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Min"] = (String)(reader2["salesUnit"]);
                        }
                        else
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Min"] = "Not found";
                        }
                        if (!(reader2["MinSalesUnitInner"]).Equals(System.DBNull.Value))
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Inner"] = System.Convert.ToString(Math.Round((decimal)(reader2["MinSalesUnitInner"])));
                        }
                        else
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Inner"] = "Not found";
                        }
                        if (!(reader2["MinSalesUnitPurchase"]).Equals(System.DBNull.Value))
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Case"] = System.Convert.ToString(Math.Round((decimal)(reader2["MinSalesUnitPurchase"])));
                        }
                        else
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["Case"] = "Not found";
                        }
                        if (!(reader2["ProductLine"]).Equals(System.DBNull.Value))
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["PLine"] = (String)(reader2["ProductLine"]);
                        }
                        else
                        {
                            employeePriceTable.Rows[employeePriceTable.Rows.Count - 1]["PLine"] = "Not found";
                        }
                    }
                }
                else
                {
                    sResult = "false";
                }

                reader2.Close();
                cmd2.Parameters.Clear();
                cmd2.Cancel();
                cmd2.Dispose();
                conn.Close();

                Grid.DataSource = employeePriceTable;
                Grid.DataBind();
                GridHolder.Controls.Add(Grid);

                //An empty result string means we didn't fail.
                if (sResult == "")
                {
                    ViewState["employeePriceTable"] = employeePriceTable;
                    return(null);
                }
                else
                {
                    return("Sku not found.");
                }
            }
        }
        catch (Exception ex)
        {
            return(ex.Message);
        }
    }
Exemplo n.º 42
0
    protected void btnSet_Click(object sender, EventArgs e)
    {
        //Variables for Personality Characteristics
        int English;
        int Mandarin;
        int German;
        int Spanish;
        int Japanese;
        int French;
        int EarlyR;
        int Introvert;
        int Family;
        int Night;
        int Extrovert;
        int TechSavvy;
        int NonSmoker;

        //Shows Which Attributes Are Selected
        //English
        if (cbEnglish.Checked == true)
        {
            English = 1;
        }
        else
        {
            English = 0;
        }
        //Mandarin
        if (cbMandarin.Checked == true)
        {
            Mandarin = 1;
        }
        else
        {
            Mandarin = 0;
        }
        //German
        if (cbGerman.Checked == true)
        {
            German = 1;
        }
        else
        {
            German = 0;
        }
        //Spanish
        if (cbSpanish.Checked == true)
        {
            Spanish = 1;
        }
        else
        {
            Spanish = 0;
        }
        //Japanese
        if (cbJapanese.Checked == true)
        {
            Japanese = 1;
        }
        else
        {
            Japanese = 0;
        }
        //French
        if (cbFrench.Checked == true)
        {
            French = 1;
        }
        else
        {
            French = 0;
        }
        //Early Riser
        if (cbEarlyRiser.Checked == true)
        {
            EarlyR = 1;
        }
        else
        {
            EarlyR = 0;
        }
        //Introvert
        if (cbIntrovert.Checked == true)
        {
            Introvert = 1;
        }
        else
        {
            Introvert = 0;
        }
        //Family Oriented
        if (cbFamily.Checked == true)
        {
            Family = 1;
        }
        else
        {
            Family = 0;
        }
        //Night Owl
        if (cbNightOwl.Checked == true)
        {
            Night = 1;
        }
        else
        {
            Night = 0;
        }
        //Extrovert
        if (cbExtrovert.Checked == true)
        {
            Extrovert = 1;
        }
        else
        {
            Extrovert = 0;
        }
        //TechSavvy
        if (cbTechSavy.Checked == true)
        {
            TechSavvy = 1;
        }
        else
        {
            TechSavvy = 0;
        }
        //NonSmoking
        if (cbNonSmoker.Checked == true)
        {
            NonSmoker = 1;
        }
        else
        {
            NonSmoker = 0;
        }

        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = "server=aawnyfad9tm1sf.cqpnea2xsqc1.us-east-1.rds.amazonaws.com; database =roommagnetdb;uid=admin;password=Skylinejmu2019;";
        sc.Open();
        System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
        insert.Connection = sc;
        //Add Personality Characteristic Values Inserted
        insert.CommandText = "INSERT into Characteristics Values(" + Convert.ToInt32(HttpContext.Current.Session["AccountId"].ToString())
                             + ", " + Extrovert + ", " + Introvert + ", " + NonSmoker + ", " + EarlyR + ", " + Night + ", " + TechSavvy + ", " + Family +
                             ", " + English + ", " + Spanish + ", " + Mandarin + ", " + Japanese + ", " + German + ", " + French + ");";

        insert.ExecuteNonQuery();
        sc.Close();
        TenantImageUpdate();
        Response.Redirect("TenantDashboard.aspx");
    }
    public DataTable BuildTable()
    {
        StringBuilder sqlCommandString  = null;
        StringBuilder sqlSelectClause   = null;
        string        orderByColumnName = string.Empty;

        char[]        parms       = { ',' };
        List <string> tlist       = null;
        string        wherePhrase = string.Empty;

        databaseConnection.Open();

        if (databaseConnection.State == ConnectionState.Open)
        {
            databaseCommand = new System.Data.SqlClient.SqlCommand();
            sqlSelectClause = new StringBuilder();

            sqlSelectClause.Append("SELECT Count(*) FROM ");
            sqlSelectClause.Append(databaseTableName);

            databaseCommand.CommandText = sqlSelectClause.ToString();
            databaseCommand.Connection  = databaseConnection;
            _totalNumberOfTableRows     = (int)databaseCommand.ExecuteScalar();
            sqlSelectClause.Clear();

            _numberOfCompletePages = _totalNumberOfTableRows / NumberRowsToDisplay;

            sqlSelectClause.Append("SELECT ");

            foreach (DatabaseRowObject.DatabaseColumnObject column in columnDictionary.Values)
            {
                DataColumn dataColumn = new DataColumn();
                dataColumn.ColumnName = column.DataGridColumnName;

                if (column.DataType == MyDataTypes.INTEGER)
                {
                    dataColumn.DataType = System.Type.GetType("System.Int32");
                }

                if (column.DataType == MyDataTypes.STRING)
                {
                    dataColumn.DataType = System.Type.GetType("System.String");
                }

                if (column.DataType == MyDataTypes.GUID)
                {
                    dataColumn.DataType = System.Type.GetType("System.Guid");
                }

                dataGridTable.Columns.Add(column.DataGridColumnName, dataColumn.DataType);

                sqlSelectClause.Append(column.DataBaseTableColumnName);
                sqlSelectClause.Append(",");

                if (column.OrderByColumn)
                {
                    orderByColumnName = column.DataBaseTableColumnName;
                }
            }

            sqlCommandString = new StringBuilder();
            sqlCommandString.Append(sqlSelectClause.ToString().TrimEnd(parms));
            sqlCommandString.Append(" FROM ");
            sqlCommandString.Append(databaseTableName);

            if (whereClause.Length > 0)
            {
                wherePhrase = whereClause.ToString().TrimEnd(new char[] { ' ', 'A', 'N', 'D' });
                sqlCommandString.Append(" WHERE ");
                sqlCommandString.Append(wherePhrase);
            }

            if (String.IsNullOrEmpty(orderByColumnName) == false)
            {
                sqlCommandString.Append(" ORDER BY ");
                sqlCommandString.Append(orderByColumnName);
            }

            if (Sort == MyDataSort.ASC)
            {
                sqlCommandString.Append(" ASC ");
            }
            else
            {
                sqlCommandString.Append(" DESC ");
            }

            if (NumberRowsToDisplay > 0)
            {
                sqlCommandString.Append(" OFFSET ");
                sqlCommandString.Append(((PageNumber - 1) * NumberRowsToDisplay).ToString());
                sqlCommandString.Append(" ROWS ");
                sqlCommandString.Append(" FETCH NEXT ");
                sqlCommandString.Append(NumberRowsToDisplay.ToString());
                sqlCommandString.Append(" ROWS ONLY ");
            }


            //= "SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY";=
            databaseCommand.CommandText = sqlCommandString.ToString();
            databaseCommand.Connection  = databaseConnection;
            databaseReader = databaseCommand.ExecuteReader();
            string fieldData = string.Empty;
            tlist = new List <string>();

            DataRow  row       = null;
            object[] itemArray = null;
            int      itemCount = 0;

            if (databaseReader.HasRows)
            {
                while (databaseReader.Read())
                {
                    row       = dataGridTable.NewRow();
                    itemCount = 0;

                    itemArray = new object[columnDictionary.Count];

                    foreach (DatabaseRowObject.DatabaseColumnObject column in columnDictionary.Values)
                    {
                        switch (column.DataType)
                        {
                        case MyDataTypes.INTEGER:
                            itemArray[itemCount] = Utilities.ParseInt32(databaseReader, itemCount);
                            break;

                        case MyDataTypes.STRING:
                            itemArray[itemCount] = Utilities.ParseStr(databaseReader, itemCount);
                            break;

                        case MyDataTypes.GUID:
                            itemArray[itemCount] = Utilities.ParseGuid(databaseReader, itemCount);
                            break;
                        }

                        itemCount++;
                    }

                    row.ItemArray = itemArray;
                    dataGridTable.Rows.Add(row);
                }

                databaseReader.Close();
                databaseConnection.Close();
            }

            if (dataGridTable.Rows.Count < NumberRowsToDisplay)
            {
                while (NumberRowsToDisplay != dataGridTable.Rows.Count)
                {
                    itemArray     = new object[columnDictionary.Count];
                    row           = dataGridTable.NewRow();
                    row.ItemArray = itemArray;
                    dataGridTable.Rows.Add(row);
                }
            }
        }

        return(dataGridTable);
    }
Exemplo n.º 44
0
    private void List()
    {
        if ("Act".RequestStr() != "List")
        {
            return;
        }
        string sqlwhere = "and  ReportDate between '" + "StartDate".RequestStr() + "' AND  '" + DateTime.Parse("EndDate".RequestStr()).ToString("yyyy-MM-dd") + "' AND  F_InvalidItem NOT LIKE '%#%' AND AdditionalQualified=1  ";



        if (!"NUM".RequestStr().IsNullOrEmpty())
        {
            sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
        }
        else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
        {
            sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
        }

        if (!String.IsNullOrEmpty(Request.Params["sReportCode"]))
        {
            sqlwhere += " and  ReportNumber LIKE '%" + Request.Params["sReportCode"].Trim() + "%'";
        }
        if (!String.IsNullOrEmpty(Request.Params["sReportName"]))
        {
            sqlwhere += " and  ReportName LIKE '%" + Request.Params["sReportName"].Trim() + "%'";
        }


        #region  使用脚本分页

        string Sql = @" 
                        DECLARE @Page int
                        DECLARE @PageSize int
                        SET @Page = {1}
                        SET @PageSize = {2}
                        SET NOCOUNT ON
                        DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        select d.IndexID from v_invalid_document  d left outer join sys_module m on d.ModelIndex = m.id  

                        where 1=1  {0}   Order By  OrderID ASC

                        SELECT     
                        d.*,convert(varchar,d.ReportDate,23) as ReportDate1,
                        d.F_InvalidItem as F_InvalidItem1,d.F_InvalidItem as F_InvalidItem2
                        FROM v_invalid_document   d
                        left outer join sys_module on  d.ModelIndex = sys_module.id                        
                        INNER JOIN @TempTable t ON d.IndexID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                        Order By  OrderID ASC

                        DECLARE @C int
                        select @C= count(d.IndexID)  from v_invalid_document d left outer join sys_module m on d.ModelIndex = m.id  where 1=1  {0}  
                        select @C 
                        ";

        string PageIndex = Request["page"];
        string PageSize  = Request["rows"];

        Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);


        BLL_LoginLog BLL = new BLL_LoginLog();
        DataSet      DSs = new DataSet();
        using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
        {
            Conn.Open();
            using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
            {
                using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
                {
                    Adp.Fill(DSs);
                }
            }
            Conn.Close();
        }


        int records = DSs.Tables[1].Rows[0][0].ToString().Toint();

        #endregion


        if (DSs.Tables[0] != null)
        {
            Response.Write(DSs.Tables[0].ToJsonForEasyUI(records, ""));
        }

        Response.End();
    }
Exemplo n.º 45
0
    protected void btnChangePswd_Click(object sender, EventArgs e)
    {
        // need to check if entered password matches password in db then go ahead ahd change password in db

        string currentPswdEntered = txtCurrentPswd.Text;
        string newPassword        = txtNewPassword.Text;
        string confirmNewPswd     = txtConfirmNew.Text;
        string newPasswordHash;

        // get password has from the database
        string passwordHash = "";

        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
        con.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";
        con.Open();
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
        command.Connection  = con;
        command.CommandText = "select top 1 PasswordHash from dbo.login where EmployeeID = @EmployeeID";
        command.Parameters.AddWithValue("@EmployeeID", Session["EmployeeID"]);
        passwordHash = (string)command.ExecuteScalar();

        bool verify = Login_Class.VerifyHash(currentPswdEntered, "MD5", passwordHash);

        if (verify.ToString().Equals("True"))
        {
            // check if the new password matches the confirm new password
            if (confirmNewPswd.Equals(newPassword))
            {
                // change password in database
                // create new hash
                newPasswordHash = Login_Class.ComputeHash(confirmNewPswd, "MD5", null);

                // update table in database

                //Database Connection
                System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
                sc.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";
                System.Data.SqlClient.SqlCommand update = new System.Data.SqlClient.SqlCommand();
                update.Connection = sc;
                // UPDATE STATEMENT
                sc.Open();
                update.CommandText = "update Login set Password = @newPassword, PasswordHash = @passwordHash where EmployeeID = @CurrentEmpId";
                update.Parameters.AddWithValue("@newPassword", newPassword);
                update.Parameters.AddWithValue("@passwordHash", newPasswordHash);
                update.Parameters.AddWithValue("@CurrentEmpId", Session["EmployeeID"]);
                update.ExecuteNonQuery();
                sc.Close();

                //clear all textbozes and hide change password controls
                lblTitleChangePswd.Visible = false;
                lblNewPassword.Visible     = false;
                lblCurrentPswd.Visible     = false;
                lblConfirmNew.Visible      = false;
                btnCancel.Visible          = false;
                btnChangePswd.Visible      = false;
                lblChangePswdError.Text    = "";
                lblChangePswdError.Visible = false;
                txtNewPassword.Text        = "";
                txtCurrentPswd.Text        = "";
                txtConfirmNew.Text         = "";
                txtConfirmNew.Visible      = false;
                txtCurrentPswd.Visible     = false;
                txtNewPassword.Visible     = false;
            }
            else
            {
                lblChangePswdError.Text = "The Confirm New Password must match the New Password entry.";
            }
        }
        else
        {
            lblChangePswdError.Text = "Incorrect password.";
        }
    }
Exemplo n.º 46
0
    //open comment modal
    protected void moreInfoJobLinkBtn_Click(object sender, CommandEventArgs e)
    {
        // working here

        String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);

        int         rowIndex = Convert.ToInt32(((sender as LinkButton).NamingContainer as GridViewRow).RowIndex);
        GridViewRow row      = GridView1.Rows[rowIndex];


        int jobID = Convert.ToInt32(e.CommandArgument);

        Session["selectedLogID"] = jobID.ToString();

        sql.Open();
        System.Data.SqlClient.SqlCommand moreJobInfo = new System.Data.SqlClient.SqlCommand();
        moreJobInfo.Connection  = sql;
        moreJobInfo.CommandText = "SELECT StudentComment.Comment, OrganizationComment.Comment AS Expr1 FROM OrganizationComment INNER JOIN StudentComment ON OrganizationComment.LogID = StudentComment.LogID INNER JOIN LogHours ON OrganizationComment.LogID = LogHours.LogID where LogHours.LogID = " + Session["selectedLogID"];
        System.Data.SqlClient.SqlDataReader reader = moreJobInfo.ExecuteReader();

        while (reader.Read())
        {
            StudentComment.Text  = reader.GetString(0);
            BusinessComment.Text = reader.GetString(1);
        }

        sql.Close();



        ClientScript.RegisterStartupScript(this.GetType(), "Pop", "openEditJModal();", true);


        if (chkImage.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Image")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Image")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }

        if (chkGradeLevel.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Grade Level")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Grade Level")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }


        if (chkGPA.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "GPA")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "GPA")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }


        if (chkHoursWBL.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Hours of WBL")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Hours of WBL")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }


        if (chkJobType.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Job Type")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Job Type")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }
    }
Exemplo n.º 47
0
    protected void CreateUserClick(object sender, EventArgs e)
    {
        zipcode.Attributes.CssStyle.Remove("background-color");
        email.Attributes.CssStyle.Remove("background-color");
        password.Attributes.CssStyle.Remove("background-color");
        password2.Attributes.CssStyle.Remove("background-color");

        int zip;

        Label1.Text = "";
        Label2.Text = "";
        String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);

        sql.Open();
        System.Data.SqlClient.SqlCommand query = new System.Data.SqlClient.SqlCommand();
        query.Connection = sql;
        if (int.TryParse(zipcode.Value, out zip))
        {
            if (email.Value.Contains("@"))
            {
                if (password.Value.Equals(password2.Value))
                {
                    if (password.Value.Length >= 8)
                    {
                        //get our ID for future use (inserting into tables that use this key but not have it auto increment 1 person, password)
                        query.CommandText = "SELECT max(UserEntityID) FROM dbo.UserEntity";

                        System.Data.SqlClient.SqlDataReader reader = query.ExecuteReader();

                        int userID = 0;

                        while (reader.Read())
                        {
                            userID = reader.GetInt32(0);
                            //increment by one because this is our new BEID after we insert
                            userID++;
                        }
                        sql.Close();

                        sql.Open();
                        System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();

                        insert.Connection = sql;

                        //inserting into the BE table


                        //insert into the person table
                        insert.CommandText = "insert into dbo.userentity (UserName, EmailAddress, EntityType) " +
                                             "values (@username, @emailaddress, @entitytype)";
                        username.Value.Trim();
                        email.Value.Trim();
                        firstName.Value.Trim();
                        middleName.Value.Trim();
                        lastName.Value.Trim();

                        //Create user entity
                        UserEntity     user     = new UserEntity(username.Value.Trim(), email.Value.Trim(), role.SelectedItem.Value);
                        SchoolEmployee employee = new SchoolEmployee(firstName.Value.Trim(), lastName.Value.Trim(), middleName.Value.Trim(),
                                                                     address.Value.Trim(), "USA", city.Value.Trim(), "VA", zipcode.Value, user.getEntityType(),
                                                                     Convert.ToInt32(DropDownList2.SelectedItem.Value));


                        insert.Parameters.AddWithValue("@username", HttpUtility.HtmlEncode(user.getUserName()));
                        insert.Parameters.AddWithValue("@emailaddress", HttpUtility.HtmlEncode(user.getEmailAddress()));
                        insert.Parameters.AddWithValue("@entitytype", HttpUtility.HtmlEncode("SCHL"));



                        insert.ExecuteNonQuery();


                        //inserting into the password
                        insert.CommandText = "insert into dbo.Password (PasswordID, PasswordHash, passwordSalt, UserEntityID, lastupdated)  " +
                                             "values (@passwordID, @passwordHash, @passwordSalt, @userentityID, getDate())";



                        String hashedPass = PasswordHash.HashPassword(password.Value);
                        insert.Parameters.AddWithValue("@passwordID", userID);
                        insert.Parameters.AddWithValue("@passwordHash", hashedPass);

                        string test = PasswordHash.returnSalt(hashedPass);

                        //had to use only the substring and not the full salt value because there is a max length of 10 in the DB.
                        insert.Parameters.AddWithValue("@passwordSalt", test.Substring(0, 24));
                        insert.Parameters.AddWithValue("@userentityID", userID);
                        insert.ExecuteNonQuery();

                        insert.CommandText = "insert into dbo.schoolemployee (SchoolEmployeeEntityID, FirstName, LastName, MiddleName, StreetAddress, Country, City, State, Zipcode, SchoolEmployeeEntityType, SchoolEntityID) " +
                                             "values (@SchoolEmployeeEntityID, @FirstName, @LastName, @MiddleName, @StreetAddress, @Country, @City, @State, @Zipcode, @SchoolEmployeeEntityType, @SchoolEntityID)";


                        insert.Parameters.AddWithValue("@SchoolEmployeeEntityID", HttpUtility.HtmlEncode(userID));
                        insert.Parameters.AddWithValue("@FirstName", HttpUtility.HtmlEncode(employee.getFirstName()));
                        insert.Parameters.AddWithValue("@LastName", HttpUtility.HtmlEncode(employee.getLastName()));
                        insert.Parameters.AddWithValue("@MiddleName", HttpUtility.HtmlEncode(employee.getMiddleName()));
                        insert.Parameters.AddWithValue("@StreetAddress", HttpUtility.HtmlEncode(employee.getStreetAddress()));
                        insert.Parameters.AddWithValue("@Country", HttpUtility.HtmlEncode(employee.getCountry()));
                        insert.Parameters.AddWithValue("@City", HttpUtility.HtmlEncode(employee.getCity()));
                        insert.Parameters.AddWithValue("@State", HttpUtility.HtmlEncode("VA"));

                        if (int.TryParse(zipcode.Value, out zip))
                        {
                            insert.Parameters.AddWithValue("@Zipcode", HttpUtility.HtmlEncode(employee.getZipCode()));
                        }
                        else
                        {
                            insert.Parameters.AddWithValue("@Zipcode", 00000);
                            Label1.Text = "Enter a number for the zipcode";
                        }



                        insert.Parameters.AddWithValue("@SchoolEmployeeEntityType", employee.getSchoolEmployeeEntityType());
                        insert.Parameters.AddWithValue("@SchoolEntityID", employee.getSchoolEntityID());
                        insert.ExecuteNonQuery();


                        //empmty these fields out.
                        firstName.Value  = "";
                        lastName.Value   = "";
                        middleName.Value = "";
                        city.Value       = "";
                        zipcode.Value    = "";


                        address.Value               = "";
                        username.Value              = "";
                        password.Value              = "";
                        email.Value                 = "";
                        role.SelectedIndex          = 0;
                        DropDownList2.SelectedIndex = 0;
                        Label1.Text                 = "Account Created!";
                    }
                    else
                    {
                        Label2.Text = "The Password Must Be More Than 8 Characters";
                        password.Attributes.CssStyle.Add("background-color", "crimson");
                        password2.Attributes.CssStyle.Add("background-color", "crimson");
                    }
                }
                else
                {
                    Label2.Text = "Passwords do not match";
                    password.Attributes.CssStyle.Add("background-color", "crimson");
                    password2.Attributes.CssStyle.Add("background-color", "crimson");
                }
            }

            else
            {
                Label2.Text = "Please Enter a Valid Email";
                email.Value = "";
                email.Attributes.CssStyle.Add("background-color", "crimson");
            }
        }
        else
        {
            Label2.Text   = "Please Enter a Valid Zipcode";
            zipcode.Value = "";
            zipcode.Attributes.CssStyle.Add("background-color", "crimson");
        }
    }
Exemplo n.º 48
0
Arquivo: Main.cs Projeto: crowell/Door
    private static void Events_KeyboardUp(object sender, KeyboardEventArgs args)
    {
        if (args.Key == Key.KeypadEnter)
        {
            ResetScreen();
            DispSDLText(mVideoScreen, "HELLO WORLD", -1, 300);
            mVideoScreen.Update();
            SdlDotNet.Core.Timer.DelaySeconds(5); //delay so to show the animation
            ResetScreen();
            PrintWelcomeMessage();
        }
        else if (args.Key == Key.Escape)
        {
            System.Console.WriteLine("Escape pressed, Quitting");
            Environment.Exit(0);
        }
        else if (args.EventStruct.key.keysym.scancode != 0x24)
        {
            string str = args.KeyboardCharacter.ToString();
            mID2.Append(args.KeyboardCharacter.ToString());
        }

        else
        {
            string id = mID2.ToString();
            mID2 = new StringBuilder(); // zero out the mID2
            if (mStringReplace)
            {
                id = id.Substring(10, id.Length - 1);
                char[] idstr = id.ToCharArray();
                idstr[0] = 'u';
                id       = idstr.ToString();
            }

            System.Data.SqlClient.SqlConnection doorDB =
                new System.Data.SqlClient.SqlConnection("user id=uid;" +
                                                        "password=pwrod;server=url;Trusted_Connection=yes;" +
                                                        "database=database;connection timeout=30");
            try
            {
                doorDB.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            string query = String.Format("SELECT id, first FROM users where swipe=\"{0}\"", id);
            System.Data.SqlClient.SqlCommand    qCommand = new System.Data.SqlClient.SqlCommand(query, doorDB);
            System.Data.SqlClient.SqlDataReader qReader  = null;
            StringBuilder sb = new StringBuilder("Hello ");
            try
            {
                qReader = qCommand.ExecuteReader();
                while (qReader.Read())
                {
                    sb.Append(qReader[1].ToString());
                    query = String.Format("insert into log(user,time) VALUES(\"{0}\",NOW())", qReader[0]);
                    ResetScreen();
                    DispSDLText(mVideoScreen, sb.ToString(), -1, 100);
                    //now unlock the door
                    mArduino.Write("u\r");
                    mVideoScreen.Update();
                    SdlDotNet.Core.Timer.DelaySeconds(5); //delay so to show the animation
                    ResetScreen();
                    PrintWelcomeMessage();
                }
                try
                {
                    doorDB.Close();
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.ToString());
                }
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.ToString());
            }
        }
    }
    /// <summary>
    /// 人员
    /// </summary>
    /// <param name="proc"></param>
    /// <param name="ftype"></param>
    /// <param name="pageCount"></param>
    /// <param name="records"></param>
    /// <returns></returns>
    private String usersummary(String proc, int ftype, out int pageCount, out int records)
    {
        BLL_UserInfo BLL      = new BLL_UserInfo();
        string       sqlwhere = " AND 1=1 ";

        if (!"NUM".RequestStr().IsNullOrEmpty())
        {
            sqlwhere += " and 试验室编码 in ('" + "NUM".RequestStr() + "') ";
        }
        else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
        {
            sqlwhere += " and 试验室编码 in (" + SelectedTestRoomCodes + ") ";
        }

        //sqlwhere += "and CreatedTime>='" + StartDate + "' AND CreatedTime<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";



        #region For首页
        if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
        {
            switch ("RPNAME".RequestStr())
            {
            case "ADD":     //新增
                sqlwhere += " AND CreatedTime between '" + StartDate + "' and '" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND  Status>0 ";
                break;

            case "DEL":     //调减
                sqlwhere += " AND CreatedTime between '" + StartDate + "' and '" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND  Status=0 ";
                break;

            default:
                sqlwhere += " AND  Status>0 ";
                break;
            }
        }
        else
        {
            sqlwhere += " AND  Status>0 ";
        }
        #endregion

        #region  使用脚本分页

        string Sql = @" 
                        DECLARE @Page int
                        DECLARE @PageSize int
                        SET @Page = {1}
                        SET @PageSize = {2}
                        SET NOCOUNT ON
                        DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        SELECT ID
						FROM dbo.sys_document a JOIN dbo.v_bs_codeName b 
						ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'  
						{0}
						AND a.TestRoomCode=b.试验室编码  
						JOIN dbo.Sys_Tree c ON  LEFT(a.TestRoomCode,12)=c.NodeCode


					    SELECT ID,TestRoomCode,b.标段名称,b.单位名称, b.试验室名称  
						,Ext1 姓名,Ext2 性别,Ext3 年龄,Ext4 技术职称,Ext5 职务,Ext6 工作年限,Ext7 联系电话,Ext8 学历,Ext9 毕业学校,Ext10 专业,1   num 
						FROM dbo.sys_document a JOIN dbo.v_bs_codeName b 
						ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'  
						{0}
						AND a.TestRoomCode=b.试验室编码  
						JOIN dbo.Sys_Tree c ON  LEFT(a.TestRoomCode,12)=c.NodeCode
                        INNER JOIN @TempTable t ON a.ID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                        Order By  OrderID,TestRoomCode ASC

                        DECLARE @C int
                        select @C= count(ID)  from dbo.sys_document a JOIN dbo.v_bs_codeName b 
						ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'  
						{0}
						AND a.TestRoomCode=b.试验室编码  
						JOIN dbo.Sys_Tree c ON  LEFT(a.TestRoomCode,12)=c.NodeCode
  
                        select @C 
                        ";

        Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);

        //DataSet DS = BLL.GetDataSet(Sql);

        DataSet DSs = new DataSet();
        using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
        {
            Conn.Open();
            using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
            {
                using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
                {
                    Adp.Fill(DSs);
                }
            }
            Conn.Close();
        }

        decimal Tempc = Math.Round(decimal.Parse(DSs.Tables[1].Rows[0][0].ToString()) / decimal.Parse(PageSize.ToString()), 2);
        Tempc = Math.Ceiling(Tempc);

        records   = DSs.Tables[1].Rows[0][0].ToString().Toint();
        pageCount = Tempc.ToString().Toint();
        #endregion

        //未使用
        //DataTable dt = BLL.GetProcDataTable(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), SelectedTestRoomCodes, ftype, PageIndex, PageSize, OrderField, OrderType, out pageCount, out records);
        if (DSs.Tables[0] != null)
        {
            return(JsonConvert.SerializeObject(DSs.Tables[0]));
        }
        else
        {
            return("");
        }
    }
Exemplo n.º 50
0
    public DatabaseConfig GenerateDBType()
    {
        DatabaseConfig config = new DatabaseConfig();

        if (string.IsNullOrEmpty(MSSQLDatabase.Text))
        {
            throw new Exception("Database must be fill in.");
            //return null;
        }

        config.DBType = WebUtils.DBTypeEmun.MSSQL;

        System.Data.SqlClient.SqlConnectionStringBuilder connStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
        connStringBuilder.DataSource = MSSQLServerLocation.Text;
        connStringBuilder.InitialCatalog = MSSQLDatabase.Text;
        connStringBuilder.UserID = MSSQLUserID.Text;
        connStringBuilder.Password = MSSQLPassword.Text;
        config.ConnectionString = connStringBuilder.ConnectionString;

        if (config.TestConnection())
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStringBuilder.ConnectionString);
            conn.Open();
            DataTable table = conn.GetSchema("Tables");
            conn.Close();
            DataRow[] rows = table.Select("TABLE_NAME='SystemParameter'");
            if (rows.GetLength(0) == 0)
                if (chkCreateDatabase.Checked)
                    CreateSchema(config.ConnectionString);
                else
                    throw new Exception("Database does not contain table");
            return config;
        }
        else
        {
            if (config.TestServerConnectionWithoutDatabase())
            {
                if (chkCreateDatabase.Checked)
                {
                    try
                    {
                        string saUser = SAUserID.Text;
                        string saPassword = SAPassword.Text;

                        if (saUser.Trim() == string.Empty)
                        {
                            saUser = MSSQLUserID.Text;
                            saPassword = MSSQLPassword.Text;
                        }
                        connStringBuilder.InitialCatalog = string.Empty;
                        connStringBuilder.UserID = saUser;
                        connStringBuilder.Password = saPassword;
                        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStringBuilder.ConnectionString);

                        System.Data.SqlClient.SqlCommand command = conn.CreateCommand();
                        command.CommandType = System.Data.CommandType.Text;
                        command.CommandText = "Create Database " + MSSQLDatabase.Text + "\r\n";

                        command.Connection.Open();
                        command.ExecuteNonQuery();

                        if (MSSQLUserID.Text.Trim() != saUser.Trim())
                        {
                            command.CommandText = "USE " + MSSQLDatabase.Text + "\r\n"
                            + "CREATE USER " + MSSQLUserID.Text + " FOR LOGIN " + MSSQLUserID.Text + "\r\n"
                            + "EXEC sp_addrolemember N'db_owner', N'" + MSSQLUserID.Text + "'";

                            command.ExecuteNonQuery();
                        }
                        command.Connection.Close();

                        connStringBuilder.InitialCatalog = MSSQLDatabase.Text;
                        CreateSchema(connStringBuilder.ConnectionString);

                        return config;

                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Error on creating Database:\n" + ex.Message);
                    }
                }
                else
                    throw new Exception("Invalid Database name.");
            }
            else
            {
                throw new Exception("Fail to connect to server.");
            }
        }
        //return null;

    }
    private String report_Search(String name, String fileds, String sqlwhere, String key, int ftype, out int pageCount, out int records)
    {
        fileds = @"ID,
 BGBH ,
 WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
    '' SegmentName,      ''   CompanyName,'' TestRoomName, '' MName,'' DeviceType ,
ModuleId
            ";



        sqlwhere = " and status>0 and BGRQ>='" + StartDate + "' AND BGRQ<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";

        #region
        if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
        {
            switch ("RPNAME".RequestStr())
            {
            case "1":     //混凝土抗压
                sqlwhere += @" AND m.id IN ('A46D053A-0776-467F-9136-0AA45D2021D2',
                                            '90973417-6C68-4E90-849B-0B1AEB36A234',
                                            'E1E2A6EA-C092-4C8D-920D-170BE5128BC4',
                                            'D06E5471-EE69-49E4-9AF4-1BE8AB2E0310',
                                            '8A2B5231-8602-4519-8919-1FF649ED2E41',
                                            'FA0D1C8C-DE69-4C16-A301-3A861C6B11CD',
                                            'D1C56FBD-2EDC-40FF-956F-4DA7178F2DD3',
                                            '4500603A-5BE7-4574-BBBA-77B4626A3EA1',
                                            '05D0D71B-DEF3-42EE-A16A-79B34DE97E9B',
                                            'A14D0408-690C-420C-9D41-7A2FA515C371',
                                            'F21C9D4A-CB80-4705-AA7C-81A8BD17DB7D',
                                            'C9A1DD95-79BF-4543-924B-94362381E705',
                                            'E25F399F-A147-4663-B74A-98A46A39F121',
                                            '269EE291-F6E7-4AEA-B4C7-A7D02B7C59DE',
                                            '3AB86F48-7A73-46B1-8AA4-D1B55DE1EE8A',
                                            'F34C2B8B-DDBE-4C04-BD01-F08B0F479AE8',
                                            'A974B39B-EC88-4917-A1D5-F8FBBFBB1F7A',
                                            'C72E2DD8-EC76-4663-B400-FB1B7F8F8C2B',
                                            '7894EACD-DA8C-4659-9891-FB4D62EB9FF5') ";
                break;

            case "2":    //钢筋实验
                sqlwhere += @" AND m.id IN ('AE9F8E75-773F-4DEE-A3A8-3E871AC8598E',
                                            'A98902C1-BB72-4E79-9C74-4746D1709D3B',
                                            '68F05EBC-5D34-49C5-9B57-49B688DF24F7',
                                            '9B8BD64A-2D9C-4B67-B9DC-53FCF67FD361',
                                            '46622671-E829-412C-99E6-587525ED968F',
                                            'C7B2620E-7F4C-4586-AEBB-59855B54E522',
                                            '3B46BC3A-92DF-4AFC-AA85-AE74FC00F96D',
                                            '377A20DA-7E27-4CD3-B9E1-B3C7993CF6EA',
                                            'A12AD84C-A7D3-4B42-A9DF-6E80A3E3A0CF',
                                            '0A2F0365-D561-4504-B602-98FCC5C3AB94',
                                            '4C817CF9-E7F3-422D-975F-C8175E738382') ";
                break;

            case "3":     //混凝土原材
                sqlwhere += " AND m.id  IN (SELECT ID FROM dbo.sys_module WHERE CatlogCode LIKE '0001%') and d.status>0 and d.bgrq between '" + StartDate + "' and '" + DateTime.Parse(EndDate).ToString("yyyy-MM-dd") + "' ";
                break;

            case "4":     //其他
                sqlwhere += @" AND m.id not in (SELECT ID FROM dbo.sys_module WHERE CatlogCode LIKE '0001%' or ID in (
                                    'AE9F8E75-773F-4DEE-A3A8-3E871AC8598E',
                                    'A98902C1-BB72-4E79-9C74-4746D1709D3B',
                                    '68F05EBC-5D34-49C5-9B57-49B688DF24F7',
                                    '9B8BD64A-2D9C-4B67-B9DC-53FCF67FD361',
                                    '46622671-E829-412C-99E6-587525ED968F',
                                    'C7B2620E-7F4C-4586-AEBB-59855B54E522',
                                    '3B46BC3A-92DF-4AFC-AA85-AE74FC00F96D',
                                    '377A20DA-7E27-4CD3-B9E1-B3C7993CF6EA',
                                    'A12AD84C-A7D3-4B42-A9DF-6E80A3E3A0CF',
                                    '0A2F0365-D561-4504-B602-98FCC5C3AB94',
                                    '4C817CF9-E7F3-422D-975F-C8175E738382',
                                    'A46D053A-0776-467F-9136-0AA45D2021D2',
                                    '90973417-6C68-4E90-849B-0B1AEB36A234',
                                    'E1E2A6EA-C092-4C8D-920D-170BE5128BC4',
                                    'D06E5471-EE69-49E4-9AF4-1BE8AB2E0310',
                                    '8A2B5231-8602-4519-8919-1FF649ED2E41',
                                    'FA0D1C8C-DE69-4C16-A301-3A861C6B11CD',
                                    'D1C56FBD-2EDC-40FF-956F-4DA7178F2DD3',
                                    '4500603A-5BE7-4574-BBBA-77B4626A3EA1',
                                    '05D0D71B-DEF3-42EE-A16A-79B34DE97E9B',
                                    'A14D0408-690C-420C-9D41-7A2FA515C371',
                                    'F21C9D4A-CB80-4705-AA7C-81A8BD17DB7D',
                                    'C9A1DD95-79BF-4543-924B-94362381E705',
                                    'E25F399F-A147-4663-B74A-98A46A39F121',
                                    '269EE291-F6E7-4AEA-B4C7-A7D02B7C59DE',
                                    '3AB86F48-7A73-46B1-8AA4-D1B55DE1EE8A',
                                    'F34C2B8B-DDBE-4C04-BD01-F08B0F479AE8',
                                    'A974B39B-EC88-4917-A1D5-F8FBBFBB1F7A',
                                    'C72E2DD8-EC76-4663-B400-FB1B7F8F8C2B',
                                    '7894EACD-DA8C-4659-9891-FB4D62EB9FF5')
                                    )  ";
                break;
            }
        }

        #endregion

        if (!String.IsNullOrEmpty(Request.Params["bgmc"]))
        {
            sqlwhere += " and  m.name   LIKE '%" + Request.Params["bgmc"].Trim() + "%'";
        }
        if (!String.IsNullOrEmpty(Request.Params["wtbh"]))
        {
            sqlwhere += " and  WTBH LIKE '%" + Request.Params["wtbh"].Trim() + "%'";
        }
        if (!String.IsNullOrEmpty(Request.Params["bgbh"]))
        {
            sqlwhere += " and  BGBH LIKE '%" + Request.Params["bgbh"].Trim() + "%'";
        }
        if (!"NUM".RequestStr().IsNullOrEmpty())
        {
            sqlwhere += " and testroomcode in ('" + "NUM".RequestStr() + "') ";
        }
        else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
        {
            sqlwhere += " and testroomcode in (" + SelectedTestRoomCodes + ") ";
        }
        else
        {
            sqlwhere += "and 1=0";
        }
        BLL_LoginLog BLL = new BLL_LoginLog();
        // DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, "BGRQ", OrderType, PageIndex, PageSize, out pageCount, out records);

        #region  使用脚本分页

        string Sql = @" 
                        DECLARE @Page int
                        DECLARE @PageSize int
                        SET @Page = {1}
                        SET @PageSize = {2}
                        SET NOCOUNT ON
                        DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
                        INSERT INTO @TempTable
                        (
	                        _keyID
                        )
                        select d.ID from sys_document  d left outer join sys_module m on d.ModuleId = m.id
                        where 1=1  {0}   Order By  BGRQ DESC

                        SELECT     
                        sys_document.ID,
	                    BGBH ,
	                    WTBH,CONVERT(NVARCHAR(10),BGRQ,120) BGRQ ,CompanyCode,TestRoomCode,SegmentCode,
                        sys_tree.description SegmentName, t2.description   CompanyName,t1.description TestRoomName
	                    ,sys_module.DeviceType ,
	                    ModuleId,  
                        sys_module.name as MName
                        FROM sys_document  
                        left outer join sys_module on sys_document.ModuleId = sys_module.id
                        left outer join sys_tree on sys_document.SegmentCode = sys_tree.nodecode
                        left outer join sys_tree t1 on sys_document.TestRoomCode = t1.nodecode
                        left outer join sys_tree t2 on sys_document.CompanyCode = t2.nodecode 
 
                        INNER JOIN @TempTable t ON sys_document.ID = t._keyID
                        WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
                        Order By  BGRQ DESC

                        DECLARE @C int
                        select @C= count(d.ID)  from sys_document d left outer join sys_module m on d.ModuleId = m.id  where 1=1  {0}  
                        select @C 
                        ";

        Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);

        //DataSet DS = BLL.GetDataSet(Sql);

        DataSet DSs = new DataSet();
        using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
        {
            Conn.Open();
            using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
            {
                using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
                {
                    Adp.Fill(DSs);
                }
            }
            Conn.Close();
        }

        decimal Tempc = Math.Round(decimal.Parse(DSs.Tables[1].Rows[0][0].ToString()) / decimal.Parse(PageSize.ToString()), 2);
        Tempc = Math.Ceiling(Tempc);

        records   = DSs.Tables[1].Rows[0][0].ToString().Toint();
        pageCount = Tempc.ToString().Toint();
        #endregion

        #region 过滤 !未使用


        //DataTable Module = BLL.GetDataTable("select Id,Name,DeviceType from sys_module");

        //DataTable Temp;
        //foreach (DataRow Dr in dt.Rows)
        //{
        //    Module.DefaultView.RowFilter = " ID = '" + Dr["ModuleId"].ToString() + "' ";
        //    Temp = Module.DefaultView.ToTable();

        //    Dr["MName"] = Temp.Rows[0]["Name"].ToString();
        //    Dr["DeviceType"] = Temp.Rows[0]["DeviceType"].ToString();
        //    Temp.Clear();
        //    Temp = null;
        //}

        #endregion


        if (DSs.Tables[0] != null)
        {
            return(JsonConvert.SerializeObject(DSs.Tables[0]));
        }
        else
        {
            return("");
        }
    }
Exemplo n.º 52
0
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(HttpContext.Current.Session["AccountId"].ToString()))
        {
        }
        else
        {
            int accountID = Convert.ToInt16(HttpContext.Current.Session["AccountId"].ToString());
            //Select Statements for tenant and properties
            System.Data.SqlClient.SqlCommand select         = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlCommand selectProp     = new System.Data.SqlClient.SqlCommand();
            System.Data.SqlClient.SqlCommand favoriteTenant = new System.Data.SqlClient.SqlCommand();
            //Tenant Select
            select.CommandText = "SELECT FirstName,LastName FROM Account WHERE AccountID in " +
                                 "(SELECT TOP(5) tenantID FROM Lease WHERE HostID = " + accountID + ");";
            //Property Select
            selectProp.CommandText = "SELECT HouseNumber,Street FROM Property WHERE PropertyID in (SELECT propertyID FROM PropertyRoom WHERE " +
                                     "RoomID in (Select RoomID from Lease where HostID = " + accountID + "));";
            //Message Center Tenant Populating once they favorite the current host's property
            favoriteTenant.CommandText = "select firstName, LastName from account where AccountID in (select tenantID from tenant where TenantID in " +
                                         "(select tenantID from FavoritedProperties where PropertyID in (select PropertyID from property where hostID =" + accountID + ")));";
            //Connections
            select.Connection         = sc;
            selectProp.Connection     = sc;
            favoriteTenant.Connection = sc;
            sc.Open();

            //Populating Tenant Part of Host Dashboard
            System.Data.SqlClient.SqlDataReader reader = select.ExecuteReader();
            while (reader.Read())
            {
                String firstName = reader["FirstName"].ToString();
                String lastName  = reader["LastName"].ToString();

                StringBuilder myCard = new StringBuilder();
                myCard
                .Append("<li><a href=\"#\" class=\"tenantdashlist\">" + firstName + " " + lastName + "</a></li>");
                Card.Text += myCard.ToString();
            }
            reader.Close();
            //Populating Property Part of Host Dashboard
            System.Data.SqlClient.SqlDataReader rdr = selectProp.ExecuteReader();
            while (rdr.Read())
            {
                String HouseNum = rdr["HouseNumber"].ToString();
                String Street   = rdr["Street"].ToString();

                StringBuilder myCard2 = new StringBuilder();
                myCard2
                .Append("<li><a href=\"#\" class=\"tenantdashlist\">" + HouseNum + " " + Street + "</a></li>");
                Card2.Text += myCard2.ToString();
            }
            rdr.Close();
            //Populating Message Center Matches
            System.Data.SqlClient.SqlDataReader drd = favoriteTenant.ExecuteReader();
            while (drd.Read())
            {
                String firstName = drd["FirstName"].ToString();
                String lastName  = drd["LastName"].ToString();

                StringBuilder myCard = new StringBuilder();
                myCard
                .Append("<div class=\"chat-list\">")
                .Append("           <div class=\"chat-people\">")
                .Append("               <div class=\"chat-img\"> <img src = \"images/rebeccajames.png\" class=\"rounded-circle img-fluid\"></div>")
                .Append("                <div class=\"chat-ib\">")
                .Append("                    <h5>" + firstName + " " + lastName + "<span class=\"chat-date\"> Nov 12</span></h5>")
                .Append("                    <p>Hello, I'm interested in your property.</p>")
                .Append("                </div>")
                .Append("            </div>")
                .Append("        </div>")
                .Append("        </div>");

                Card3.Text += myCard.ToString();
            }
            drd.Close();
            sc.Close();
        }
    }
Exemplo n.º 53
0
    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(clsRPlus.sCN_SALES))
        {
            try
            {
                if (e.CommandName.Equals("New"))
                {
                    LinkButton  btnNew = e.CommandSource as LinkButton;
                    GridViewRow row    = btnNew.NamingContainer as GridViewRow;
                    //System.Data.SqlClient.SqlDataReader reader = null;
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                    if (row == null)
                    {
                        return;
                    }
                    TextBox      txtforecastYear     = GridView1.FooterRow.FindControl("forecastYearTextBoxNew") as TextBox;
                    DropDownList txtforecastCategory = GridView1.FooterRow.FindControl("forecastCategoryTextBoxEmpty") as DropDownList;
                    TextBox      txtJan = GridView1.FooterRow.FindControl("JanTextBoxNew") as TextBox;
                    TextBox      txtFeb = GridView1.FooterRow.FindControl("FebTextBoxNew") as TextBox;
                    TextBox      txtMar = GridView1.FooterRow.FindControl("MarTextBoxNew") as TextBox;
                    TextBox      txtApr = GridView1.FooterRow.FindControl("AprTextBoxNew") as TextBox;
                    TextBox      txtMay = GridView1.FooterRow.FindControl("MayTextBoxNew") as TextBox;
                    TextBox      txtJun = GridView1.FooterRow.FindControl("JunTextBoxNew") as TextBox;
                    TextBox      txtJul = GridView1.FooterRow.FindControl("JulTextBoxNew") as TextBox;
                    TextBox      txtAug = GridView1.FooterRow.FindControl("AugTextBoxNew") as TextBox;
                    TextBox      txtSep = GridView1.FooterRow.FindControl("SepTextBoxNew") as TextBox;
                    TextBox      txtOct = GridView1.FooterRow.FindControl("OctTextBoxNew") as TextBox;
                    TextBox      txtNov = GridView1.FooterRow.FindControl("NovTextBoxNew") as TextBox;
                    TextBox      txtDec = GridView1.FooterRow.FindControl("DecTextBoxNew") as TextBox;
                    String       query  = "INSERT INTO [SkupForecasts] ( [forecastYear], [forecastCategory], [Jan], [Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) VALUES (@forecastYear, @forecastCategory, @Jan, @Feb, @Mar, @Apr, @May, @Jun, @Jul, @Aug, @Sep, @Oct, @Nov, @Dec)";

                    cmd.CommandText = query;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection  = conn;

                    cmd.Parameters.AddWithValue("forecastYear", txtforecastYear.Text);
                    cmd.Parameters.AddWithValue("forecastCategory", txtforecastCategory.Text);
                    cmd.Parameters.AddWithValue("Jan", txtJan.Text);
                    cmd.Parameters.AddWithValue("Feb", txtFeb.Text);
                    cmd.Parameters.AddWithValue("Mar", txtMar.Text);
                    cmd.Parameters.AddWithValue("Apr", txtApr.Text);
                    cmd.Parameters.AddWithValue("May", txtMay.Text);
                    cmd.Parameters.AddWithValue("Jun", txtJun.Text);
                    cmd.Parameters.AddWithValue("Jul", txtJul.Text);
                    cmd.Parameters.AddWithValue("Aug", txtAug.Text);
                    cmd.Parameters.AddWithValue("Sep", txtSep.Text);
                    cmd.Parameters.AddWithValue("Oct", txtOct.Text);
                    cmd.Parameters.AddWithValue("Nov", txtNov.Text);
                    cmd.Parameters.AddWithValue("Dec", txtDec.Text);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.Cancel();
                    cmd.Dispose();
                    conn.Close();
                    Response.AppendHeader("Refresh", "0,URL=");
                }
                if (e.CommandName.Equals("EmptyNew"))
                {
                    LinkButton  btnNew = e.CommandSource as LinkButton;
                    GridViewRow row    = btnNew.NamingContainer as GridViewRow;
                    System.Data.SqlClient.SqlDataReader reader = null;
                    System.Data.SqlClient.SqlCommand    cmd    = new System.Data.SqlClient.SqlCommand();
                    if (row == null)
                    {
                        return;
                    }
                    TextBox      txtforecastYear     = GridView1.Controls[0].Controls[0].FindControl("forecastYearTextBoxEmpty") as TextBox;
                    DropDownList txtforecastCategory = GridView1.Controls[0].Controls[0].FindControl("forecastCategoryTextBoxEmpty") as DropDownList;
                    TextBox      txtJan = GridView1.Controls[0].Controls[0].FindControl("JanTextBoxEmpty") as TextBox;
                    TextBox      txtFeb = GridView1.Controls[0].Controls[0].FindControl("FebTextBoxEmpty") as TextBox;
                    TextBox      txtMar = GridView1.Controls[0].Controls[0].FindControl("MarTextBoxEmpty") as TextBox;
                    TextBox      txtApr = GridView1.Controls[0].Controls[0].FindControl("AprTextBoxEmpty") as TextBox;
                    TextBox      txtMay = GridView1.Controls[0].Controls[0].FindControl("MayTextBoxEmpty") as TextBox;
                    TextBox      txtJun = GridView1.Controls[0].Controls[0].FindControl("JunTextBoxEmpty") as TextBox;
                    TextBox      txtJul = GridView1.Controls[0].Controls[0].FindControl("JulTextBoxEmpty") as TextBox;
                    TextBox      txtAug = GridView1.Controls[0].Controls[0].FindControl("AugTextBoxEmpty") as TextBox;
                    TextBox      txtSep = GridView1.Controls[0].Controls[0].FindControl("SepTextBoxEmpty") as TextBox;
                    TextBox      txtOct = GridView1.Controls[0].Controls[0].FindControl("OctTextBoxEmpty") as TextBox;
                    TextBox      txtNov = GridView1.Controls[0].Controls[0].FindControl("NovTextBoxEmpty") as TextBox;
                    TextBox      txtDec = GridView1.Controls[0].Controls[0].FindControl("DecTextBoxEmpty") as TextBox;
                    String       query  = "INSERT INTO [SkupForecasts] ( [forecastYear], [forecastCategory], [Jan], [Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) VALUES (@forecastYear, @forecastCategory, @Jan, @Feb, @Mar, @Apr, @May, @Jun, @Jul, @Aug, @Sep, @Oct, @Nov, @Dec)";

                    cmd.CommandText = query;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection  = conn;

                    cmd.Parameters.AddWithValue("forecastYear", txtforecastYear.Text);
                    cmd.Parameters.AddWithValue("forecastCategory", txtforecastCategory.Text);
                    cmd.Parameters.AddWithValue("Jan", txtJan.Text);
                    cmd.Parameters.AddWithValue("Feb", txtFeb.Text);
                    cmd.Parameters.AddWithValue("Mar", txtMar.Text);
                    cmd.Parameters.AddWithValue("Apr", txtApr.Text);
                    cmd.Parameters.AddWithValue("May", txtMay.Text);
                    cmd.Parameters.AddWithValue("Jun", txtJun.Text);
                    cmd.Parameters.AddWithValue("Jul", txtJul.Text);
                    cmd.Parameters.AddWithValue("Aug", txtAug.Text);
                    cmd.Parameters.AddWithValue("Sep", txtSep.Text);
                    cmd.Parameters.AddWithValue("Oct", txtOct.Text);
                    cmd.Parameters.AddWithValue("Nov", txtNov.Text);
                    cmd.Parameters.AddWithValue("Dec", txtDec.Text);
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.Cancel();
                    cmd.Dispose();
                    conn.Close();
                    Response.AppendHeader("Refresh", "0,URL=");
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }
    }
Exemplo n.º 54
0
    //click approve in gridview- trigger modal to open - fill modal
    protected void approveJobLinkBtn_Click(object sender, CommandEventArgs e)
    {
        String connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection(connectionString);

        int         rowIndex = Convert.ToInt32(((sender as LinkButton).NamingContainer as GridViewRow).RowIndex);
        GridViewRow row      = GridView1.Rows[rowIndex];

        int jobID = Convert.ToInt32(e.CommandArgument);

        Session["selectedLogID"] = jobID.ToString();

        sql.Open();
        System.Data.SqlClient.SqlCommand moreHourInfo = new System.Data.SqlClient.SqlCommand();
        moreHourInfo.Connection  = sql;
        moreHourInfo.CommandText = "SELECT JobListing.JobTitle, LogHours.HoursRequested, CONCAT(Student.FirstName,' ', Student.LastName) FROM LogHours INNER JOIN Student ON LogHours.StudentEntityID = Student.StudentEntityID INNER JOIN JobListing ON LogHours.JobListingID = JobListing.JobListingID WHERE LogHours.LogID = " + Session["selectedLogID"];
        System.Data.SqlClient.SqlDataReader reader = moreHourInfo.ExecuteReader();

        while (reader.Read())
        {
            sublabelapprovemodal1.Text = reader.GetString(2);
            sublabelapprovemodal2.Text = reader.GetString(0);
            sublabelapprovemodal3.Text = "Hours: " + reader.GetInt32(1).ToString();
        }

        sql.Close();



        ClientScript.RegisterStartupScript(this.GetType(), "Pop", "openApproveXModal();", true);



        if (chkImage.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Image")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Image")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }

        if (chkGradeLevel.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Grade Level")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Grade Level")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }


        if (chkGPA.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "GPA")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "GPA")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }


        if (chkHoursWBL.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Hours of WBL")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Hours of WBL")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }


        if (chkJobType.Checked != true)
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Job Type")
                {
                    GridView1.Columns[i].Visible = false;
                }
            }
        }
        else
        {
            for (int i = 0; i < GridView1.Columns.Count; i++)
            {
                if (GridView1.Columns[i].HeaderText == "Job Type")
                {
                    GridView1.Columns[i].Visible = true;
                }
            }
        }
    }
Exemplo n.º 55
0
    protected void Page_Load(object sender, EventArgs e)
    {
        // verify that the user is logged in...if not redirect
        // the user to the login screen

        if (Session["loggedIn"] == null)
        {
            Response.Redirect("Default.aspx");
        }

        if (Session["loggedIn"].ToString() != "True")
        {
            Response.Redirect("Default.aspx");
        }



        // Hide all change password controls
        lblTitleChangePswd.Visible = false;
        lblNewPassword.Visible     = false;
        lblCurrentPswd.Visible     = false;
        lblConfirmNew.Visible      = false;
        btnCancel.Visible          = false;
        btnChangePswd.Visible      = false;
        lblChangePswdError.Visible = false;
        txtConfirmNew.Visible      = false;
        txtCurrentPswd.Visible     = false;
        txtNewPassword.Visible     = false;


        //Populate labels with the appropiate information
        int CurrentEmpId = (int)Session["EmployeeID"];

        if (CurrentEmpId == 1)
        {
            btnAdminArea.Visible = true;
        }
        else
        {
            btnAdminArea.Visible = false;
        }
        string SessionEmpId = CurrentEmpId.ToString();
        string firstName, MI, lastName, emailAddress;
        double rewardBalance = 0;

        // select name and email address from employee table where account id matches
        //Database Connection
        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";


        // SELECT STATEMENT
        sc.Open();

        System.Data.SqlClient.SqlCommand select = new System.Data.SqlClient.SqlCommand();
        select.Connection  = sc;
        select.CommandText = "SELECT [FirstName] FROM [dbo].[Employee] INNER JOIN [dbo].[Account] ON [dbo].[Employee].EmployeeID = [dbo].[Account].EmployeeID WHERE [dbo].[Account].EmployeeID = @CurrentEmpId"; //+ CurrentEmpId;
        select.Parameters.AddWithValue("@CurrentEmpId", CurrentEmpId);
        firstName = (string)select.ExecuteScalar();

        select.Connection  = sc;
        select.CommandText = "SELECT [LastName] FROM [dbo].[Employee] INNER JOIN [dbo].[Account] ON [dbo].[Employee].EmployeeID = [dbo].[Account].EmployeeID WHERE [dbo].[Account].EmployeeID = @CurrentEmpId"; //+ CurrentEmpId;
        lastName           = (string)select.ExecuteScalar();

        select.Connection  = sc;
        select.CommandText = "SELECT [Email] FROM [dbo].[Employee] INNER JOIN [dbo].[Account] ON [dbo].[Employee].EmployeeID = [dbo].[Account].EmployeeID WHERE [dbo].[Account].EmployeeID = @CurrentEmpId"; //+ CurrentEmpId;
        emailAddress       = (string)select.ExecuteScalar();

        select.Connection  = sc;
        select.CommandText = "SELECT [RewardBalance] FROM [dbo].[Account] WHERE [EmployeeID] = @CurrentEmpId"; // + CurrentEmpId;
        rewardBalance      = Convert.ToDouble(select.ExecuteScalar());


        sc.Close();

        lblChangePswdError.Visible = false;



        lblFirstName.Text     = firstName;
        lblLastName.Text      = lastName;
        lblEmail.Text         = emailAddress;
        lblRewardBalance.Text = "$ " + rewardBalance;



        // Datalist Stuff

        // Create a connection to the "pubs" SQL database located on the local computer.
        System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection();
        myConnection.ConnectionString = "Data Source=aaixxyrfluc2wz.ctt4oijc6ckc.us-east-1.rds.amazonaws.com;Initial Catalog=Lab4;User ID=Tweedljm;Password=Promise96!;";


        // SELECT STATEMENT
        System.Data.SqlClient.SqlDataAdapter myCommand = new System.Data.SqlClient.SqlDataAdapter("SELECT CONVERT(CHAR(10), RT.RewardDate, 101) AS Date, E.FirstName + ' ' + E.LastName AS Rewardee, S.FirstName + ' ' + S.LastName AS Rewarder, V.ValueName AS Value, RT.RewardType AS [Reward Category], RT.RewardDescription AS Description, CAST(RT.RewardAmount AS decimal(18 , 2)) AS [Reward Amount] FROM Reward_Transactions AS RT INNER JOIN Employee AS E ON RT.ReceiverID = E.EmployeeID INNER JOIN Employee AS S ON RT.SenderID = S.EmployeeID INNER JOIN Organization_Values AS V ON RT.ValueID = V.ValueID WHERE RT.ReceiverID = @CurrentEmpId OR RT.SenderID = @CurrentEmpId ORDER BY RT.RewardID DESC", myConnection);
        myCommand.SelectCommand.Parameters.AddWithValue("@CurrentEmpId", CurrentEmpId);

        // Connect to the SQL database using a SQL SELECT query to get all
        // the data from the "Titles" table.

        // Create and fill a DataSet.
        System.Data.DataSet ds = new System.Data.DataSet();
        myCommand.Fill(ds);
        // Bind MyDataList to the DataSet. MyDataList is the ID for
        // the DataList control in the HTML section of the page.
        DataList1.DataSource = ds;
        DataList1.DataBind();

        string currentUserPic = Session["UserPic"].ToString();

        if (currentUserPic == "")
        {
            Image1.ImageUrl = "http://www.cdn.innesvienna.net//Content/user-default.png";
        }
        else
        {
            Image1.ImageUrl = "~/ShowProfilePicture.ashx";
        }
    }
Exemplo n.º 56
0
    protected void btnLogin_Click(object sender, EventArgs e)
    {
        Boolean success  = false;
        int     id       = 0;
        String  usertype = "";

        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        sc.ConnectionString = @"Data Source=aay09edjn65sf6.cpcbbo8ggvx6.us-east-1.rds.amazonaws.com;Initial Catalog=RoomMagnet;Persist Security Info=True;User ID=fahrenheit;Password=cis484fall";
        sc.Open();
        System.Data.SqlClient.SqlCommand match = new System.Data.SqlClient.SqlCommand();
        match.Connection = sc;
        //match.CommandText = "select PasswordHash from [db_owner].[AdminPassword] where Email = @Email";
        match.CommandText = "select passwordhash from[db_owner].[AdminPassword] where Email = @Email " +
                            "union select passwordhash from[dbo].[HostPassword] where Email = @Email " +
                            "union select passwordhash from[dbo].[TenantPassword] where Email = @Email";

        match.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", tbEmail.Text));
        System.Data.SqlClient.SqlDataReader reader = match.ExecuteReader(); // create a reader

        if (reader.HasRows)
        {
            while (reader.Read())                                               // this will read the single record that matches the entered usename
            {
                string storedHash = reader["PasswordHash"].ToString();          // store the database password into this varable
                if (PasswordHash.ValidatePassword(tbPassword.Text, storedHash)) // if the entered password matches what is stored, it will show success
                {
                    Label1.Text = "Success!";
                    success     = true;
                }
                else
                {
                    Label1.Text = "Password is wrong.";
                }
            }
        }
        else // if the username does not exist, it will show failure.
        {
            Label1.Text = "Login failed";
        }
        sc.Close();
        if (success == true)
        {
            sc.Open();
            System.Data.SqlClient.SqlCommand matchID = new System.Data.SqlClient.SqlCommand();
            matchID.Connection = sc;
            //matchID.CommandText = "Select AdminID from [db_owner].[AdminPassword] where Email = @Email";
            matchID.CommandText = "select adminid from[db_owner].[AdminPassword] where Email = @Email " +
                                  "union select hostid from[dbo].[HostPassword] where Email = @Email " +
                                  "union select tenantid from[dbo].[TenantPassword] where Email = @Email";
            matchID.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", tbEmail.Text));
            id = (Int32)matchID.ExecuteScalar();
            Session["globalID"] = id;
            Label1.Text         = "Success! ID is: " + id;

            System.Data.SqlClient.SqlCommand type = new System.Data.SqlClient.SqlCommand();
            type.Connection = sc;

            type.CommandText = "select UserType from [dbo].[RMUser] where userid = " + id;
            usertype         = type.ExecuteScalar().ToString();

            switch (usertype)
            {
            case "t":
                Response.Redirect("MasterTenantDash.aspx");
                break;

            case "h":
                Response.Redirect("MasterHostDashboard.aspx");
                break;

            case "a":
                Response.Redirect("MasterAdminDashboard.aspx");
                break;
            }
        }
    }
Exemplo n.º 57
0
    private void displayTable(object sender, EventArgs e, String s)
    {
        //initial set up...Counter for the number of rows, and
        int    countTotalJobs   = 0;
        String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(connectionString);
        sc.Open();

        System.Data.SqlClient.SqlCommand countJobPostings = new System.Data.SqlClient.SqlCommand();



        //This indicates that all interest groups were selected/default!
        if (s.Equals(" "))
        {
            //default
            countJobPostings.CommandText = "SELECT count( SchoolApproval.OpportunityEntityID) FROM OpportunityEntity INNER JOIN SchoolApproval ON " +
                                           "OpportunityEntity.OpportunityEntityID = SchoolApproval.OpportunityEntityID where OpportunityEntity.OpportunityType = 'JOB' and schoolApproval.approvedflag = 'Y'" +
                                           " and SchoolApproval.SchoolEntityID = " + Session["schoolID"];
        }
        //This indicates that there were some interest groups that were selected in the drop down menu
        else
        {
            //have to utilize distinct due to the nature of data duplication with interest groups. Cannot have two cards that are exactly the same
            countJobPostings.CommandText = "SELECT COUNT(DISTINCT OpportunityEntity.OpportunityEntityID) AS Expr1 FROM OpportunityEntity INNER JOIN " +
                                           "OpportunityInterestGroups ON OpportunityEntity.OpportunityEntityID = OpportunityInterestGroups.OpportunityEntityID INNER JOIN  " +
                                           "InterestGroups ON OpportunityInterestGroups.InterestGroupID = InterestGroups.InterestGroupID INNER JOIN " +
                                           "SchoolApproval ON OpportunityEntity.OpportunityEntityID = SchoolApproval.OpportunityEntityID WHERE(OpportunityEntity.OpportunityType = 'JOB') " +
                                           "AND(SchoolApproval.SchoolEntityID = 12) AND(SchoolApproval.ApprovedFlag = 'Y') and (" + s + ")";
        }

        String test = s;

        countJobPostings.Connection = sc;
        System.Data.SqlClient.SqlDataReader reader = countJobPostings.ExecuteReader();



        while (reader.Read())
        {
            countTotalJobs = reader.GetInt32(0);
        }

        sc.Close();



        sc.Open();


        System.Data.SqlClient.SqlCommand pullJobInfo = new System.Data.SqlClient.SqlCommand();

        if (s.Equals(" "))
        {
            pullJobInfo.CommandText = "SELECT  Organization.OrganizationName, JobListing.JobTitle, JobListing.JobDescription," +
                                      " Organization.Image, Organization.ExternalLink, JobListing.Location, JobListing.Deadline, JobListing.NumOfApplicants, Organization.OrganizationDescription," +
                                      " JobListing.JobListingID FROM SchoolApproval INNER JOIN OpportunityEntity ON SchoolApproval.OpportunityEntityID = OpportunityEntity.OpportunityEntityID INNER JOIN JobListing" +
                                      " ON OpportunityEntity.OpportunityEntityID = JobListing.JobListingID INNER JOIN Organization ON JobListing.OrganizationID = Organization.OrganizationEntityID " +
                                      "where SchoolApproval.ApprovedFlag = 'Y' and OpportunityEntity.OpportunityType = 'JOB' and SchoolApproval.SchoolEntityID = " + Session["schoolID"];
        }
        else
        {
            pullJobInfo.CommandText = "SELECT distinct Organization.OrganizationName, JobListing.JobTitle, JobListing.JobDescription, Organization.Image, " +
                                      "Organization.ExternalLink, JobListing.Location, JobListing.Deadline, JobListing.NumOfApplicants, Organization.OrganizationDescription, " +
                                      "JobListing.JobListingID FROM " +
                                      "SchoolApproval INNER JOIN OpportunityEntity ON SchoolApproval.OpportunityEntityID = OpportunityEntity.OpportunityEntityID INNER JOIN JobListing ON " +
                                      "OpportunityEntity.OpportunityEntityID = JobListing.JobListingID INNER JOIN Organization ON JobListing.OrganizationID = Organization.OrganizationEntityID INNER JOIN " +
                                      " OpportunityInterestGroups ON OpportunityEntity.OpportunityEntityID = OpportunityInterestGroups.OpportunityEntityID WHERE(SchoolApproval.ApprovedFlag = 'Y') AND OpportunityEntity.OpportunityType = 'JOB' and " +
                                      "(SchoolApproval.SchoolEntityID = 12) and (" + s + ")";
        }



        pullJobInfo.Connection = sc;



        reader = pullJobInfo.ExecuteReader();

        {
            //Make the list
            List <JobListing> jobs = new List <JobListing>();


            int      jobListingID;
            String   orgName;
            String   jobTitle;
            String   jobDescription;
            String   image;
            String   link;
            String   jobLocation;
            int      numOfApplicants;
            String   organizationDescription;
            DateTime deadline;


            int x = 0;
            while (reader.Read())
            {
                orgName                 = reader.GetString(0);
                jobTitle                = reader.GetString(1);
                jobDescription          = reader.GetString(2);
                image                   = reader.GetString(3);
                link                    = reader.GetString(4);
                jobLocation             = reader.GetString(5);
                deadline                = reader.GetDateTime(6);
                numOfApplicants         = reader.GetInt32(7);
                organizationDescription = reader.GetString(8);
                jobListingID            = reader.GetInt32(9);
                x++;

                JobListing job = new JobListing(jobTitle, jobDescription, jobLocation, deadline, numOfApplicants, orgName, organizationDescription,
                                                image, link);
                //Set this to be used later
                job.setID(jobListingID);
                //Make the object
                //Add to list
                jobs.Add(job);
            }
            sc.Close();
            double doubleRows = countTotalJobs / 3.0;
            int    numrows    = (int)(Math.Ceiling(doubleRows));
            int    numcells   = 3;
            int    count      = 0;
            for (int j = 0; j < numrows; j++)
            {
                TableRow r = new TableRow();


                for (int i = 0; i < numcells; i++)
                {
                    if (count == countTotalJobs)
                    {
                        break;
                    }
                    TableCell c = new TableCell();

                    LinkButton referralLink = new LinkButton();
                    referralLink.ID = "referralLink" + count;

                    referralLink.CssClass = "far fa-paper-plane";

                    referralLink.CommandArgument += jobs[count].getID();
                    referralLink.Command         += new CommandEventHandler(this.referralButton_Click);

                    c.Controls.Add(new LiteralControl("<div class='image-flip' ontouchstart='this.classList.toggle('hover');'>"));
                    c.Controls.Add(new LiteralControl("<div class='mainflip'>"));
                    c.Controls.Add(new LiteralControl("<div class='frontside'>"));
                    c.Controls.Add(new LiteralControl("<div class='card'>"));
                    c.Controls.Add(new LiteralControl("<div class='card-body text-center'>"));
                    c.Controls.Add(new LiteralControl("<p><img class='img-fluid' src='" + jobs[count].getOrgImage() + "' alt='card image'></p>"));
                    c.Controls.Add(new LiteralControl("<h4 class='card-title'>" + jobs[count].getOrgName() + "</h4>"));
                    c.Controls.Add(new LiteralControl("<p class='card-text'>" + jobs[count].getJobTitle() + "</p>"));
                    c.Controls.Add(new LiteralControl("<a href='#' class='btn btn-primary btn-sm'><i class='fa fa-plus'></i></a>"));
                    c.Controls.Add(new LiteralControl("</div>"));
                    c.Controls.Add(new LiteralControl("</div>"));
                    c.Controls.Add(new LiteralControl("</div>"));

                    c.Controls.Add(new LiteralControl("<div class='backside'>"));
                    c.Controls.Add(new LiteralControl("<div class='card'>"));
                    c.Controls.Add(new LiteralControl("<div class='card-body text-center'>"));
                    c.Controls.Add(new LiteralControl("<h4 class='card-title'>" + jobs[count].getOrgName() + "</h4>"));
                    c.Controls.Add(new LiteralControl("<p class='card-text'>" + jobs[count].getJobTitle() + "</p>"));
                    c.Controls.Add(new LiteralControl("<p class='card-text'> Location: " + jobs[count].getJobLocation() + "</p>"));
                    c.Controls.Add(new LiteralControl("<p class='card-text'>  Deadline: " + jobs[count].getJobDeadline().ToString() + "</p>"));
                    c.Controls.Add(new LiteralControl("<p class='card-text'>  Number of Applicants: " + jobs[count].getNumOfApplicants() + "</p>"));
                    c.Controls.Add(new LiteralControl("<ul class='list-inline'>"));
                    c.Controls.Add(new LiteralControl("<li class='list-inline-item'>"));
                    c.Controls.Add(new LiteralControl("<a class='social-icon text-xs-center' target='_blank' href='" + jobs[count].getOrgWebsite() + "'>"));
                    c.Controls.Add(new LiteralControl("<i class='fas fa-external-link-alt'></i>&nbsp;&nbsp;&nbsp;"));
                    //c.Controls.Add(referralLink);
                    c.Controls.Add(new LiteralControl("</a>"));
                    c.Controls.Add(new LiteralControl("</li>"));
                    c.Controls.Add(new LiteralControl("</ul>"));
                    c.Controls.Add(new LiteralControl("</div>"));
                    c.Controls.Add(new LiteralControl("</div>"));
                    c.Controls.Add(new LiteralControl("</div>"));
                    c.Controls.Add(new LiteralControl("</div>"));
                    c.Controls.Add(new LiteralControl("</div>"));

                    c.Style.Add("width", "33%");
                    r.Cells.Add(c);
                    count++;
                }
                jobPostingTable.Rows.Add(r);
            }
        }
    }
    protected void btnSet_Click(object sender, EventArgs e)
    {
        //Type of Personality Characterisics
        int English;
        int Mandarin;
        int German;
        int Spanish;
        int Japanese;
        int French;
        int EarlyR;
        int Introvert;
        int Family;
        int Night;
        int Extrovert;
        int TechSavvy;
        int NonSmoker;

        //Changing  the status in database if toggled on/ off
        if (cbEnglish.Checked == true)
        {
            English = 1;
        }
        else
        {
            English = 0;
        }
        if (cbMandarin.Checked == true)
        {
            Mandarin = 1;
        }
        else
        {
            Mandarin = 0;
        }
        if (cbGerman.Checked == true)
        {
            German = 1;
        }
        else
        {
            German = 0;
        }
        if (cbSpanish.Checked == true)
        {
            Spanish = 1;
        }
        else
        {
            Spanish = 0;
        }
        if (cbJapanese.Checked == true)
        {
            Japanese = 1;
        }
        else
        {
            Japanese = 0;
        }
        if (cbFrench.Checked == true)
        {
            French = 1;
        }
        else
        {
            French = 0;
        }
        if (cbEarlyRiser.Checked == true)
        {
            EarlyR = 1;
        }
        else
        {
            EarlyR = 0;
        }
        if (cbIntrovert.Checked == true)
        {
            Introvert = 1;
        }
        else
        {
            Introvert = 0;
        }
        if (cbFamily.Checked == true)
        {
            Family = 1;
        }
        else
        {
            Family = 0;
        }
        if (cbNightOwl.Checked == true)
        {
            Night = 1;
        }
        else
        {
            Night = 0;
        }
        if (cbExtrovert.Checked == true)
        {
            Extrovert = 1;
        }
        else
        {
            Extrovert = 0;
        }
        if (cbTechSavy.Checked == true)
        {
            TechSavvy = 1;
        }
        else
        {
            TechSavvy = 0;
        }
        if (cbNonSmoker.Checked == true)
        {
            NonSmoker = 1;
        }
        else
        {
            NonSmoker = 0;
        }

        //SQL Statement
        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();
        //SQL Connection
        sc.ConnectionString = "server=aawnyfad9tm1sf.cqpnea2xsqc1.us-east-1.rds.amazonaws.com; database =roommagnetdb;uid=admin;password=Skylinejmu2019;";
        sc.Open();
        System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
        insert.Connection = sc;
        //Inserting Values into Database for Personality Characterisitcs
        insert.CommandText = "INSERT into Characteristics Values(" + Convert.ToInt32(HttpContext.Current.Session["AccountId"].ToString())
                             + ", " + Extrovert + ", " + Introvert + ", " + NonSmoker + ", " + EarlyR + ", " + Night + ", " + TechSavvy + ", " + Family +
                             ", " + English + ", " + Spanish + ", " + Mandarin + ", " + Japanese + ", " + German + ", " + French + ");";
        //Execute and Close SQL
        insert.ExecuteNonQuery();
        sc.Close();
        HostImageUpdate();
        Response.Redirect("HostDashboard.aspx");
    }
Exemplo n.º 59
0
    public static void LogMessage(string content, bool message, bool warning, bool error)
    {
        content.Trim();
        if (content == null || content.Length == 0)
        {
            return;
        }
        if (!_logToConsole && !_logToFile && !_logToDatabase)
        {
            throw new Exception("Invalid configuration");
        }

        // if ((!_logError && !_logMessage && !_logWarning) || (!message && !warning && !error)) /* esta de mas realizar la comprobacion de los logs porque se usa mas arriba*/
        if (!message && !warning && !error)
        {
            throw new Exception("Error or Warning or Message must be specified");
        }

        System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
        connection.Open();

        int t = 0; /*inicializo la variable t*/

        if (message && _logMessage)
        {
            t = 1;
        }
        if (error && _logError)
        {
            t = 2;
        }
        if (warning && _logWarning)
        {
            t = 3;
        }
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("Insert into Log Values('" + message + "', " + t.ToString() + ")");
        command.ExecuteNonQuery();

        string l = "";                                                                                                                                                /*incializo la variable l*/

        if (System.IO.File.Exists(System.Configuration.ConfigurationManager.AppSettings["LogFileDirectory"] + "LogFile" + DateTime.Now.ToShortDateString() + ".txt")) /*Saqué la negación en el condicional que evalúa si existe el archivo log porque trataba de leer el contenido del archivo cuando no existe y eso no tiene sentido*/
        {
            l = System.IO.File.ReadAllText(System.Configuration.ConfigurationManager.AppSettings["LogFileDirectory"] + "LogFile" + DateTime.Now.ToShortDateString() + ".txt");
        }

        if (error && _logError)
        {
            l = l + DateTime.Now.ToShortDateString() + message;
        }
        if (warning && _logWarning)
        {
            l = l + DateTime.Now.ToShortDateString() + message;
        }
        if (message && _logMessage)
        {
            l = l + DateTime.Now.ToShortDateString() + message;
        }

        /*El problema de usar este método para escribir texto en el archivo es que si el archivo ya existe, se sobreescribe y creo que la idea de un log no es sobreescribir los log anteriores*/
        /*En todo caso usaría FileStream para escribir sobre el archivo y no sobreescribirlo*/
        System.IO.File.WriteAllText(System.Configuration.ConfigurationManager.AppSettings["LogFileDirectory"] + "LogFile" + DateTime.Now.ToShortDateString() + ".txt", l);

        if (error && _logError)
        {
            Console.ForegroundColor = ConsoleColor.Red;
        }
        if (warning && _logWarning)
        {
            Console.ForegroundColor = ConsoleColor.Yellow;
        }
        if (message && _logMessage)
        {
            Console.ForegroundColor = ConsoleColor.White;
        }

        Console.WriteLine(DateTime.Now.ToShortDateString() + message);
        connection.Close(); /*cierre de conexion a base de datos*/
    }
Exemplo n.º 60
0
 /// <summary>
 /// 關閉資料連結
 /// </summary>
 /// <param name="conn"></param>
 public void CloseConn(System.Data.SqlClient.SqlConnection conn)
 {
     conn.Close();
     conn.Dispose();
 }