public RequestsProcessedByEnum GetLeaveStatus(int leaveRequestId)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = DALHelper.CreateSqlDbConnection();
                cmd = new SqlCommand("usp_GetLeaveStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@LeaveRequestId", leaveRequestId);

                if (cmd.ExecuteScalar() != DBNull.Value)
                {
                    return (RequestsProcessedByEnum)Convert.ToInt32(cmd.ExecuteScalar());
                }
                else
                {
                    return RequestsProcessedByEnum.NotDefined;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
            }
        }
Example #2
1
        protected void Tmbtn_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("select MAX(wjh) from wj", cn);
            cn.Open();
            cmd.ExecuteScalar();
            //cn.Close();
            SqlCommand com = new SqlCommand("INSERT INTO Tm(Wjh,Tm,Tixing) VALUES (" +cmd.ExecuteScalar()+ ",'" + Tmtxt.Text.Trim() + "','" + TXDropDownList.Text.Trim() + "')", cn);
            //com.ExecuteNonQuery();
            //cn.Close();
            try
            {
                //cn.Open();
                int val = com.ExecuteNonQuery();
                cn.Close();
                this.NRListBox.Items.Add(this.Tmtxt.Text);
                if (val <= 0)
                    ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败!')");
                else
                    ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据成功!')");

            }
            //捕获异常
            catch (Exception exp)
            {
                //处理异常.......
                ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败! 详情:" + exp.Message + "')");

            }
        }
    protected void Button3_Click(object sender, EventArgs e)
    {
        String s = ConfigurationManager.ConnectionStrings["MediDB"].ConnectionString;
        SqlConnection con = new SqlConnection(s);
        SqlCommand cmd = new SqlCommand(" Select Count(*) from Admin where name='" + TextBox1.Text + "' and Password='******'", con);
        con.Open();
        int m = (int)cmd.ExecuteScalar();
        if (m == 1)
        {
            Button1.Enabled = true ;
            Button2.Enabled = true ;
            Button4.Enabled = true ;
            Button5.Enabled = true ;
        }
        else
        {
            Response.Write("<Script>alert('Name or the password entered by you is incorrect. please try again!!')</Script>");
        }

        cmd.ExecuteScalar();

        con.Close();
        TextBox1.Text = "";
        TextBox2.Text = "";

    }
 public List<TableInfo> LoadTableList(string databaseConnectionString)
 {
     List<TableInfo> tables;
     using (var conn = new SqlConnection(databaseConnectionString))
     {
         conn.Open();
         var cmd = new SqlCommand(SqlQueries.QUERY_ALL_TABLES, conn);
         using (var reader = cmd.ExecuteReader())
         {
             tables = new List<TableInfo>();
             while (reader.Read())
             {
                 tables.Add(new TableInfo(reader.GetString(0), reader.GetString(1)));
             }
         }
         int tableCount = 0;
         foreach (var tableInfo in tables)
         {
             cmd = new SqlCommand(string.Format(SqlQueries.QUERY_DATA_SIZE, tableInfo.Name, tableInfo.Schema), conn);
             cmd.CommandTimeout = 60000;
             tableInfo.DataSizeBytes = (double) cmd.ExecuteScalar();
             cmd.CommandText = string.Format(SqlQueries.QUERY_INDEX_SIZE, tableInfo.Name, tableInfo.Schema);
             tableInfo.IndexSizeBytes = (double) cmd.ExecuteScalar();
             cmd.CommandText = string.Format(SqlQueries.QUERY_ROW_COUNT, tableInfo.Name, tableInfo.Schema);
             tableInfo.RowCount = (long) cmd.ExecuteScalar();
             OnTableLoadProgressChanged(new ProgressEventArgs(tableCount++*100 / tables.Count));
         }
     }
     return tables;
 }
        public string generatestudentcode()
        {
            string regcode = "";
            string query = "select RegistrationCode from StudentPersonalInformation where ID =(select Max(ID) from StudentPersonalInformation)";
            try
            {
                SqlCommand cmd = new SqlCommand(query, con);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                if (cmd.ExecuteScalar() != null)
                {
                    //regcode = cmd.ExecuteScalar().ToString();
                    //regcode= regcode.Substring(0,regcode.LastIndexOf('/')-1) + Convert.ToInt32(regcode.Substring(regcode.LastIndexOf('/'), (regcode.Length - (regcode.LastIndexOf('/'))) + 1)) +1;
                    regcode = (Convert.ToInt32(cmd.ExecuteScalar()) + 1).ToString();
                }
                else
                    regcode = "1";
            //                    regcode = "ASTM/" + DropDownListCenter.SelectedValue + "/" + DateTime.Now.Year + "/1";

                //                regcode = "ASTM/" + DropDownListCenter.Items[0].Text + "/" + DropDownListCourse.SelectedValue + "/" + DateTime.Now.Year + "/1";

            }
            catch (Exception ex)
            { }
            finally
            {
                con.Close();
            }
            return regcode;
        }
Example #6
1
        public static int? getMax()
        {
            int? id = null;
            string conexionCadena = ConfigurationManager.ConnectionStrings["ConexionComplejo"].ConnectionString;
            SqlConnection con = new SqlConnection();
            try
            {
                con.ConnectionString = conexionCadena;
                con.Open();
                string sql = "SELECT MAX(id_fact) from facturas";
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sql;
                cmd.Connection = con;
                if(!cmd.ExecuteScalar().Equals(null))
                {
                    id =(int) cmd.ExecuteScalar();
                }
                else
                {
                    id = null;
                }

            }
            catch (SqlException ex)
            {
                throw new ApplicationException("Error al traer max id cliente" + ex.Message);

            }
            finally
            {
                con.Close();
            }
            return id;
        }
        /// <summary>
        /// Authenticates the user
        /// </summary>
        /// <param name="user">user from login form</param>
        /// <param name="password">password from login form</param>
        public static bool Authenticate(String user,String password)
        {
            var positiveIntRegex = new Regex(@"^\w+$");
            if (!positiveIntRegex.IsMatch(user))
            {
                return false;
            }
            if (!positiveIntRegex.IsMatch(password))
            {
                return false;
            }

            String encryptedPass = Encrypt(password);
            string constr = Settings.Default.UserDbConnectionString;
            SqlConnection con = new SqlConnection(constr);
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.Parameters.AddWithValue("@Username", user);
            command.CommandText = "SELECT Password FROM Users WHERE Name = @Username";
            command.CommandType = CommandType.Text;

            con.Open();
            string _password = "";
            if (command.ExecuteScalar() != null)
                _password = command.ExecuteScalar().ToString();
            else
                return false;
            con.Close();
            if (encryptedPass.Equals(_password))
            {
                return true;
            }
            return false;
        }
        protected void TxtAccountName_TextChanged(object sender, EventArgs e)
        {
            try
            {
                System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
                con.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select ACODE from ACCOUNTS where ANAME = '" + TxtAccountName.Text + "' and COMP_CODE = " + Session["COMP_CODE"].ToString(), con);
                int x = Convert.ToInt32(cmd.ExecuteScalar());
                if (x == 0)
                {
                    TxtAccountName.BackColor = Color.Red;
                }
                else
                {
                    HfACODE.Value            = cmd.ExecuteScalar().ToString();
                    TxtAccountName.BackColor = Color.White;

                    con.Close();

                    getAccountGroupName();
                    FillProductDetailsGrid();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        protected void login_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("server=(localdb)\\v11.0;Initial Catalog=WebApplication2;Integrated Security=true");
            con.Open();
            string str = "select count(*) from Users where userName='******'";
            SqlCommand command = new SqlCommand(str, con);
            int temp = Convert.ToInt32(command.ExecuteScalar().ToString());
            if (temp == 1)
            {
                string str2 = "select password from Users where userName='******'";

                SqlCommand command2 = new SqlCommand(str2, con);
                string tempPass = command2.ExecuteScalar().ToString().ToLower().Trim();

                if (tempPass==pass.Text.ToLower().Trim())
                {
                    string str3 = "select user_id from Users where userName='******'";
                    SqlCommand command3 = new SqlCommand(str3, con);
                    temp = Convert.ToInt32(command.ExecuteScalar().ToString());
                    con.Close();
                    Session["new"] = temp;
                    Response.Redirect("Home.aspx");
                }
                else
                {
                    Label1.Visible = true;
                    Label1.Text = "Wrong password!";
                }
            }
            else
            {
                Label1.Visible = true;
                Label1.Text = "Invalid user name!";
            }
        }
Example #10
0
        public static string ExecuteScalar(string strSql)
        {
            try
            {
                iniCon();

                SqlConnection sCon=new SqlConnection(m_strCon);
                sCon.Open();

                SqlCommand sCmd=new SqlCommand();
                sCmd.Connection =sCon ;
                sCmd.CommandText =strSql;
                if(sCmd.ExecuteScalar()!=null)
                {
                    string strRet=sCmd.ExecuteScalar().ToString();
                    sCon.Close();
                    return strRet;
                }
                else
                {
                    sCon.Close();
                    return "";
                }
            }catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
                Application.Exit();
                //MessageBox.Show(ex.ToString());
                return "";
            }
        }
    void GetCurrentMembership()
    {
        string userID = Session["UserID"].ToString();

        if (CheckMembership())
        {
            using (SqlConnection con = new SqlConnection(Helper.GetCon()))
            using (SqlCommand cmd = new SqlCommand())
            {
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = "SELECT SUM(Length) FROM Memberships INNER JOIN Payments ON " +
                                  "Memberships.MembershipID=Payments.MembershipID WHERE UserID=@UserID " +
                                  "AND MembershipStatus='Active' AND PaymentStatus='Paid'";
                cmd.Parameters.AddWithValue("@UserID", userID);
                int totalYears = (int)cmd.ExecuteScalar();

                cmd.CommandText = "SELECT TOP 1 StartDate FROM Memberships INNER JOIN Payments ON " +
                                  "Memberships.MembershipID=Payments.MembershipID WHERE UserID=@UserID " +
                                  "AND MembershipStatus='Active' AND PaymentStatus='Paid'";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@UserID", userID);
                DateTime endDate = (DateTime)cmd.ExecuteScalar();
                DateTime totalEndDate = endDate.AddYears(totalYears);
                txtEndDate.Text = totalEndDate.ToString("D");
            }
        }
        else
        {
            txtEndDate.Text = "N/A";
            nomem.Visible = true;
            btnDisable.Visible = false;
            btnEnable.Visible = false;
        }
    }
        /// <summary>
        /// Check Employee whether exist
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public bool IsEmployeeIdExist(string employeeId)
        {
            using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = @"select EmployeeId
                                    from dbo.Employee
                                    where EmployeeId = @EmployeeId";
                cmd.Parameters.AddWithValue("@EmployeeId", employeeId);
                bool result = false;
                try
                {
                    conn.Open();
                    cmd.ExecuteScalar();
                    if (cmd.ExecuteScalar() != null)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
                return result;
            }
        }
Example #13
0
 /// <summary>
 /// This method Returns the object after executing the execute scalar method
 /// </summary>
 /// <param name="cmdClass"></param>
 /// <param name="objExp"></param>
 public int ExecuteScalar(SqlCommand cmdClass, out Object obj, out Exception objExp)
 {
     int intStatus = 0;
     obj = null;
     objExp = null;
     try
     {
         SqlConnection conClass = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
         cmdClass.Connection = conClass;
         if (conClass.State == ConnectionState.Closed)
             conClass.Open();
         if (cmdClass.ExecuteScalar() != DBNull.Value)
             obj = cmdClass.ExecuteScalar();
         else
             obj = null;
         conClass.Close();
         conClass.Dispose();
         cmdClass.Dispose();
         if (obj != null)
             intStatus = 1;
     }
     catch (Exception exp)
     {
         objExp = exp;
     }
     return (intStatus);
 }
Example #14
0
        public CanUploadStatus CanUpload(string filename, int size)
        {
            bool extAllowed = FileExtensionAllowed(filename);
            if (!extAllowed)
                return CanUploadStatus.FileTypeNotAllowed;
            else
            {
                int uploaded = 0;
                using (var dbContext = _dbService.GetDatabaseContext(false))
                {
                    SqlCommand cmd = new SqlCommand(@"
            select SUM(f.Size)
            from [File] f
            inner join [File_User_Access] as fua ON fua.lid = f.Id
            inner join [User] u on u.Id = fua.rid
            where u.Id = @userId", dbContext.Connection);
                    cmd.Parameters.AddWithValue("userId", _securityService.CurrentUser.Id);
                    var raw = cmd.ExecuteScalar();
                    if (raw != DBNull.Value)
                        uploaded = (int)cmd.ExecuteScalar();
                    else
                        uploaded = 0;
                }

                if (uploaded > _securityService.CurrentUser.GetData<int>("DiskUsageLimit"))
                    return CanUploadStatus.DiskUsageLimitExceeded;
                else
                    return CanUploadStatus.Yes;
            }
        }
Example #15
0
 public int GetInformationAssistantCount(string employeeId)
 {
     using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection()))
     {
         SqlCommand cmd = new SqlCommand();
         int result = 0;
         cmd.Connection = conn;
         cmd.CommandText = @"select count(*) as InformationAssistantCount,
                             e.Name as EmployeeName
                             from Employee e ,InformationAssistant i
                             where e.EmployeeId = i.EmployeeId
                             and e.EmployeeId = @EmployeeId
                             and CONVERT(varchar(100), i.RecordDate, 111)=CONVERT(varchar(100), getdate(), 111)
                             group by e.Name";
         cmd.Parameters.AddWithValue("@EmployeeId", employeeId);
         try
         {
             conn.Open();
             result = DBNull.Value == cmd.ExecuteScalar() ? 0 : Convert.ToInt32(cmd.ExecuteScalar());
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             conn.Close();
             cmd.Dispose();
         }
         return result;
     }
 }
Example #16
0
    public static bool Grabar(Entidades.Distrito pEntidad)
    {
      using (var cn = new SqlConnection(conexion.LeerCC))
      {
        // Contamos cuantos distritos existen segun el coddistrito o nomdistrito
        using (var cmd = new SqlCommand(@"select isnull(count(coddistrito),0) from distritos where coddistrito=@cod or nomdistrito=@nom", cn))
        {
          cmd.Parameters.AddWithValue("cod", pEntidad.coddistrito);
          cmd.Parameters.AddWithValue("nom", pEntidad.nomdistrito);

          cn.Open();
          // Ejecutamos el comando y verificamos si el resultado es mayor a cero actualizar, caso contrario insertar
          if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
          {
            // Si es mayor a cero, quiere decir que existe al menos un registro con los datos ingresados
            // Entonces antes de actualizar, hacer las siguientes comprobaciones
            if (pEntidad.coddistrito == 0)
              throw new Exception("El distrito ya esta registrado en el sistema, verifique los datos por favor!...");

            // Verifica si ya existe un registro con el mismo nombre del distrito
            cmd.CommandText = @"select isnull(count(coddistrito),0) from distritos where coddistrito<>@cod and nomdistrito=@nom";
            if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
              throw new Exception("No se puede grabar un valor duplicado, verifique los datos por favor!...");

            // Si las comprobaciones anteriores resultaron ser falsa, entonces actualizar
            cmd.CommandText = @"update distritos set nomdistrito=@nom where coddistrito=@cod";
          }
          else
            cmd.CommandText = @"insert into distritos (nomdistrito) values (@nom)";

          // Ejecutamos el comando que puede ser para update o insert
          return Convert.ToBoolean(cmd.ExecuteNonQuery());
        }
      }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlCommand cmd=new SqlCommand("select password from consumer where email='"+TextBox1.Text+"'",con);
        con.Open();

        if (cmd.ExecuteScalar()!=null)
        {
            string s = cmd.ExecuteScalar().ToString();

            MailMessage mail = new MailMessage();
            mail.To.Add(TextBox1.Text);
            mail.From = new MailAddress("*****@*****.**");
            mail.Subject = "Remember Mail";
            string Body = "Password is " + s;
            mail.Body = Body;
            SmtpClient smtp = new SmtpClient();
            smtp.Host = "smtp.gmail.com";
            smtp.Port = 587;
            smtp.UseDefaultCredentials = false;
            smtp.Credentials = new System.Net.NetworkCredential("*****@*****.**", "9232663223");
            smtp.EnableSsl = true;
            smtp.Send(mail);
            Label1.Text = "PASSWORD SENT TO YOUR EMAIL ADDRESS";
            con.Close();
        }
        else
            Label1.Text = "No such email exists ";
    }
Example #18
0
 protected void btnEdit_Click(object sender, EventArgs e)
 {
     string connectionstring = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString;
     SqlConnection con = new SqlConnection(connectionstring);
     try
     {
         con.Open();
         SqlCommand cmd = new SqlCommand("exec getUserControlledPupilId @lg", con);
         cmd.Parameters.Add("@lg", login);
         string pId = ((int)cmd.ExecuteScalar()).ToString();
         cmd.CommandText = "exec getUserAccess @lg";
         int access = (int)cmd.ExecuteScalar();
         if (access >= 2)
         {
             cmd.CommandText = "exec getPupilFLF @pid";
             cmd.Parameters.Add("@pid", pId);
             string flf = (string)cmd.ExecuteScalar();
             con.Close();
             Session["SCSTitle"] = flf;
             Session["SCSParameter1"] = pId;
             Response.Redirect("Redactor.aspx");
         }
         else
         {
             Response.Write("<div align=center>Вашего уровня допуска недостаточно для этой операции <BR> <a href='ChangeAccess.aspx'>Как изменить уровень допуска?</A> " +
                     "<BR>Ваш текущий уровень допуска : " + access.ToString() + " Требуемый : 2</div>");
         }
     }
     catch (Exception err)
     {
         Response.Write(err.Message);
     }
 }
Example #19
0
        /// <summary>
        /// Attempts to purchase a movie for a user
        /// </summary>
        /// <param name="movieId">The movie to purchase</param>
        /// <param name="userId">The user purchasing the movie</param>
        /// <returns>true if the movie is bought, false if the movie could not be bought (f.ex. due to insufficient funds)</returns>
        public bool PurchaseMovie(int movieId, int userId) {
            //Ensure that the id is valid for a movie
            SqlCommand command = new SqlCommand("SELECT id FROM Movie WHERE id=" + movieId, connection);
            if (command.ExecuteScalar() == null) return false;

            //Get the price of the movie
            command.CommandText = "SELECT buyPrice FROM Files WHERE id =" + movieId;
            Object pric = command.ExecuteScalar();
            if (pric == null) return false;
            int price = (int)pric;
            //Get the balance of the user
            command.CommandText = "SELECT balance FROM Users WHERE id =" + userId;
            Object bal = command.ExecuteScalar();
            if (bal == null) return false;
            int balance = (int)bal;
            if (balance - price >= 0) {
                //Withdraw the amount from the users balance and only continue if it is successful
                command.CommandText = "UPDATE Users " +
                                      "SET balance = balance - " + price +
                                      "WHERE id = " + userId;
                if (command.ExecuteNonQuery() > 0) {
                    command.CommandText = "INSERT INTO UserFile " +
                                          "VALUES(" + userId +
                                          ", " + movieId +
                                          ", '" + DateTime.MaxValue.ToString("yyyy-MM-dd HH:mm:ss") + "' )";
                    return command.ExecuteNonQuery() > 0;
                }
            }
            return false;
        }
    protected void Button1_Click(object sender, EventArgs e)
    {
        if(TextBox3.Text==TextBox4.Text && TextBox1.Text!=""&&TextBox2.Text!=""&&TextBox5.Text!="")
        {

        cmd=new SqlCommand("select did from cities where city=@city",con);
        con.Open();
        cmd.Parameters.AddWithValue("@city",TextBox5.Text);

        if(cmd.ExecuteScalar()==null)
        Label2.Text="City not available";

        else
        {
           int did=int.Parse(cmd.ExecuteScalar().ToString());
           cmd=new SqlCommand("insert into distributorapplication values(@d,@did,@u,@p,@c)",con);
           cmd.Parameters.AddWithValue("@d",TextBox1.Text);
           cmd.Parameters.AddWithValue("@did",did);
           cmd.Parameters.AddWithValue("@u",TextBox2.Text);
            cmd.Parameters.AddWithValue("@p",TextBox3.Text);
            cmd.Parameters.AddWithValue("@c", TextBox5.Text);
            cmd.ExecuteNonQuery();
            Label2.Text="Request for registration sent to administrator. You will be notified within few dayd";
            TextBox1.Text = TextBox2.Text = TextBox5.Text = "";
        }
        con.Close();
        }
        else if(TextBox3.Text!=TextBox4.Text)
        Label2.Text="Password doesn't match";
        else
        {
        Label2.Text="Please fill in all the details";
        }
    }
Example #21
0
 public int GetCustomerId()
 {
     using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection()))
     {
         int result = 0;
         SqlCommand cmd = new SqlCommand();
         cmd.Connection = conn;
         cmd.CommandText = @"select max(CustomerId) from Customer";
         try
         {
             conn.Open();
             result = cmd.ExecuteScalar() == DBNull.Value ? 0 : Convert.ToInt32(cmd.ExecuteScalar());
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             conn.Close();
             cmd.Dispose();
         }
         return result + 1;
     }
 }
Example #22
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();
        }
Example #23
0
        //-----------------------------------
        public void favoritecontrol()
        {
            try
            {
                if (Session["userid"] != null)
                {
                    myconnection.Open();
                    SqlCommand com = new SqlCommand("SPfavoritecontrol", myconnection);
                    com.CommandType = CommandType.StoredProcedure;

                    com.Parameters.Add("@movieid", SqlDbType.Int);
                    com.Parameters["@movieid"].Value = Convert.ToInt32(Request.QueryString["movieid"]);

                    com.Parameters.Add("@userid", SqlDbType.Int);
                    com.Parameters["@userid"].Value = Convert.ToInt32(Session["userid"]);

                    if (com.ExecuteScalar() != null)
                    {
                        if (Convert.ToInt32(com.ExecuteScalar().ToString()) != 0)
                        {
                            LinkButtonaddtofavorites.Text = "Added to favorites";
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                Labelerror.Text = "error in controlling favorites" + ex.Message;
            }
            finally
            {
                myconnection.Close();
            }
        }
        /// <inheritdoc/>
        public bool CorrelativeConfigurationExists(string facilityId, string correlativeId)
        {
            if (string.IsNullOrWhiteSpace(facilityId)) return false;
            if (string.IsNullOrWhiteSpace(correlativeId)) return false;
            if (string.IsNullOrWhiteSpace(_connectionString)) return false;

            int count;

            using (var conexionSp = new SqlConnection(_connectionString))
            {
                const string query = "SELECT COUNT(PaqueteId) FROM Comunes.CorrelativosPaquetes " +
                                     "WHERE PaqueteId = @correlativeId AND PlantaId = @facilityId";

                using (var comandoSp = new SqlCommand(query, conexionSp))
                {
                    comandoSp.CommandType = CommandType.Text;
                    comandoSp.Parameters.AddWithValue("@correlativeId", correlativeId).Direction = ParameterDirection.Input;
                    comandoSp.Parameters.AddWithValue("@facilityId", facilityId).Direction = ParameterDirection.Input;
                    conexionSp.Open();
                    count = comandoSp.ExecuteScalar() is int ? (int)comandoSp.ExecuteScalar() : 0;
                    conexionSp.Close();
                }
            }
            return count > 0;
        }
 protected void btnAccept_Click(object sender, EventArgs e)
 {
     string constr = WebConfigurationManager.ConnectionStrings["SCSDataBaseAdminLogin"].ConnectionString;
     constr = constr.Replace("ID=admin;Password=;", "ID=SCSAdmin;Password="******";");
     SqlConnection con = new SqlConnection(constr);
     try
     {
         con.Open();
         SqlCommand cmd = new SqlCommand("exec ChangeAccess @lg, @na", con);
         cmd.Parameters.Add("@lg", lblLogin.Text);
         cmd.Parameters.Add("@na", Convert.ToInt32(lblNeedAccess.Text));
         cmd.ExecuteScalar();
         cmd.CommandText = "exec CreateNewMessage @lgself, @lg, 'Запрос выполнен'";
         cmd.Parameters.Add("@lgself", login);
         cmd.ExecuteScalar();
         con.Close();
         Response.Redirect("Cabinet.aspx");
     }
     catch (Exception err)
     {
         //Response.Clear();
         Response.Write(err.Message);
     }
     finally
     {
         con.Close();
     }
 }
Example #26
0
        public static void insertarReserva(Reserva res, DetalleReserva det)
        {
            string connStr = ConfigurationManager.ConnectionStrings["ConexionComplejo"].ConnectionString;

            SqlConnection cn = new SqlConnection();
            SqlTransaction tran = null;

            try
            {
                cn.ConnectionString = connStr;
                cn.Open();
                tran = cn.BeginTransaction();

                string sql = "INSERT INTO RESERVAS (fecha_res,cli_id,estado,monto) values (@FechaRes,@Cliente,@Estado,@Monto); SELECT @@Identity as ID;";
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = sql;
                cmd.Connection = cn;
                cmd.Transaction = tran;

                cmd.Parameters.AddWithValue("@FechaRes",res.Fecha);
                cmd.Parameters.AddWithValue("@Cliente", res.Cli.IdCliente);
                cmd.Parameters.AddWithValue("@Estado", res.Estado);
                cmd.Parameters.AddWithValue("@Monto", res.Monto);

                res.Id = Convert.ToInt32(cmd.ExecuteScalar());
                //int idCabania = Convert.ToInt32(cmd.ExecuteScalar()); //Recupero Id de a cabania insertada
                det.Res.Id = res.Id;

                string sql2 = "INSERT INTO DETALLE_RESERVA (res_id,cab_id,precio,subtotal,fecha_desde,fecha_hasta,cant_dias,facturada) values (@ResId, @CabId,@Precio,@Subtotal,@FechaDesde,@FechaHasta,@CantDias,@Facturada); SELECT @@Identity as ID;";
                cmd.CommandText = sql2;

                cmd.Parameters.AddWithValue("@ResId", det.Res.Id);
                cmd.Parameters.AddWithValue("@CabId", det.Cab.IdCabania);
                cmd.Parameters.AddWithValue("@Precio", det.Precio);
                cmd.Parameters.AddWithValue("@Subtotal", det.Subtotal);
                cmd.Parameters.AddWithValue("@FechaDesde", det.FechaDesde);
                cmd.Parameters.AddWithValue("@FechaHasta", det.FechaHasta);
                cmd.Parameters.AddWithValue("@CantDias", det.CantDias);
                cmd.Parameters.AddWithValue("@Facturada", det.Facturada);

                det.Id = Convert.ToInt32(cmd.ExecuteScalar());

                tran.Commit();

                //cabania.IdCabania = idCabania; //Seteo Id de la cabania insertada
            }
            catch (SqlException ex)
            {
                if (cn.State == ConnectionState.Open)
                    tran.Rollback(); //Vuelvo atras los cambios
                throw new ApplicationException("Error al guardar la cabaña." + ex.Message);

            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }
Example #27
0
        public string Scalar(string sqlstr)
        {
            string msg    = "";
            object result = "";

            try
            {
                if (CONN.State != ConnectionState.Open)
                {
                    CONN.Open();
                }
                CMD.Connection  = CONN;
                CMD.CommandText = sqlstr;
                CMD.CommandType = CommandType.Text;
                result          = CMD.ExecuteScalar();

                CONN.Close();
            }
            catch (Exception ex)
            {
                result = "";
                msg    = ex.Message;
            }
            finally
            {
                if (CONN.State == ConnectionState.Open)
                {
                    CONN.Close();
                }
            }

            try  //log用另外的try防止檔案被咬
            {
                if (msg != "")
                {
                    WriteCMD();
                    WriteLog("[" + DateTime.Now.ToLongTimeString() + "][Main.Scalar.Error]:" + msg + sqlstr);
                }
                else
                {
                    if (LogStat >= 1)
                    {
                        WriteCMD();
                    }
                    WriteLogselect(sqlstr);
                }
            }
            catch { }

            if (result == null)
            {
                return("");
            }
            else
            {
                return(result.ToString());
            }
        }
Example #28
0
        private void CreateCapacities(int id, int userId, System.Data.SqlClient.SqlCommand command)
        {
            List <int> capacities = new List <int>();

            // get capacities to create that not already exists
            string sql = "SELECT PORProductClassCapacity.ProductCapacityId as CapacityId " +
                         "FROM     PORProductClassCapacity WITH (NOLOCK)  LEFT OUTER JOIN " +
                         "PORCapacity WITH (NOLOCK)  ON PORProductClassCapacity.PORId = PORCapacity.PORId AND PORProductClassCapacity.ProductCapacityId = PORCapacity.CapacityId " +
                         "WHERE  (PORCapacity.CapacityId IS NULL) AND (PORProductClassCapacity.PORId = " + id.ToString() + ")";

            command.CommandText = sql;
            System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                capacities.Add(reader.GetInt32(0));
            }

            reader.Close();

            foreach (int capacity in capacities)
            {
                sql = "insert into PORCapacity (PORId, CapacityID) values (" + id + ", " + capacity + "); SELECT IDENT_CURRENT('[PORCapacity]') AS ID";
                command.CommandText = sql;
                int PORCapacityId = Convert.ToInt32(command.ExecuteScalar());


                sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_PORCapacity', '" + PORCapacityId + "', 1, " + userId + ")";
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = "insert into PMM (PORCapacityId) values (" + PORCapacityId + "); SELECT IDENT_CURRENT('[PMM]') AS ID";
                command.CommandText = sql;
                int PMMId = Convert.ToInt32(command.ExecuteScalar());

                sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_SamplesRequest', '" + PMMId + "', 1, " + userId + ")";
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = "insert into PLM (PORCapacityId,PLMBEStatusId) values (" + PORCapacityId + ",5); SELECT IDENT_CURRENT('[PLM]') AS ID";
                command.CommandText = sql;
                int PLMId = Convert.ToInt32(command.ExecuteScalar());

                sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_PLM', '" + PLMId + "', 1, " + userId + ")";
                command.CommandText = sql;
                command.ExecuteNonQuery();

                CreatePLMParameterModes(PLMId, userId, command);

                sql = "insert into Test (PORCapacity) values (" + PORCapacityId + "); SELECT IDENT_CURRENT('[Test]') AS ID";
                command.CommandText = sql;
                int TestId = Convert.ToInt32(command.ExecuteScalar());

                sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_TEST', '" + TestId + "', 1, " + userId + ")";
                command.CommandText = sql;
                command.ExecuteNonQuery();
            }
        }
    protected void BtnNext_Click(object sender, EventArgs e)
    {
        try
        {
            con = new SqlConnection("integrated security=sspi;Server=.;Database=Jobs");

            con.Open();
            if (txtUname.Text == "" || txtPwd.Text=="")
            {
                lblmsg.Text = "sorry pls enter the required fileds";
            }
            else
            {
                cmd = new SqlCommand("select count(*) from users where uname = @uname", con);
                cmd.Parameters.Add("@uname", SqlDbType.VarChar, 10).Value = txtUname.Text;

                int cnt = (int)cmd.ExecuteScalar();
                if (cnt == 1)
                {
                    lblmsg.Text = "Sorry! Username is already present. Try another name!";
                    return;
                }
                cmd.CommandText = "select count(*) from users where email = @email";
                cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = txtEmail.Text;

                cnt = (int)cmd.ExecuteScalar();
                if (cnt == 1)
                {
                    lblmsg.Text = "Sorry! Email address is already present.";
                    return;
                }

                //cmd = new SqlCommand("insert into Users values(@p1,@p2,@p3,@p4,@p5,@p6,@p7)", con);
                //cmd.Parameters.Add(new SqlParameter("@p1", txtUname.Text));
                //cmd.Parameters.Add(new SqlParameter("@p2", txtPwd.Text));
                //cmd.Parameters.Add(new SqlParameter("@p4", dt));
                //cmd.Parameters.Add(new SqlParameter("@p3", txtEmail.Text));
                //cmd.Parameters.Add(new SqlParameter("@p6", txtAddress.Text));
                //cmd.Parameters.Add(new SqlParameter("@p7", txtPhone.Text));
                //cmd.Parameters.Add(new SqlParameter("@p5", ddlUtype.SelectedItem.Text));
                //cmd.ExecuteNonQuery();
                //con.Close();
                //hPwd.Text = txtPwd.Text;
                hPwd.Text = txtPwd.Text;
                MultiView1.ActiveViewIndex = ddlUtype.SelectedIndex + 1;
            }
        }

        catch (Exception ex)
        {
            lblmsg.Text = "Error :" + ex.Message;
        }
        finally
        {
            con.Close();
        }
    }
Example #30
0
        private void button1_Click(object sender, EventArgs e)
        {
            //必要信息填写验证
            if (textBoxXINGZHI.Text == "" || textBoxBUMEN.Text == "" || textBoxWEITUOREN.Text == "" )
            {
                MessageBox.Show("必要信息填写不完全");
            }
            else
            {
                try
                {

                    //申请估价编号算法,从新写,你妈的
                    SqlConnection conn = new SqlConnection(Jingtai.ConnectionString);
                    conn.Open();//打开链接
                    //创建命令对象
                    SqlCommand cmd = null;
                    //创建语句字符串
                    string sql = null;

                    //到 table_ku 中获取指定年份的报告编号最大的数字和ID
                    int baogaohao;

                    sql = @"select MAX(报告编号) from Table_Ku where 报告年份='" + textBoxNIAN.Text + "'";
                    cmd = new SqlCommand(sql, conn);
                    label3.Text = cmd.ExecuteScalar().ToString();
                    baogaohao = int.Parse(cmd.ExecuteScalar().ToString());
                    //得到最大的报告号
                    baogaohao++;
                    sql = @"INSERT INTO Table_Ku (报告编号申请人,报告性质,相关部门,委托人,报告年份,报告编号,备注,编号申请时间) VALUES ('" + label2.Text +
                    "','" + textBoxXINGZHI.Text +
                    "','" + textBoxBUMEN.Text +
                    "','" + textBoxWEITUOREN.Text +
                    "','" + textBoxNIAN.Text +
                    "','" + baogaohao +
                    "','" + textBoxBEIZHU.Text +
                    "','" + DateTime.Now + "')";
                    cmd = new SqlCommand(sql, conn);
                    cmd.ExecuteNonQuery();

                    sql = @"select id from Table_Ku where 报告年份='" + textBoxNIAN.Text + "' and 报告编号='" + baogaohao.ToString() + "'";
                    cmd = new SqlCommand(sql, conn);
                    int myid = int.Parse(cmd.ExecuteScalar().ToString());
                    Jingtai.myid = myid;

                    conn.Close();//关闭连接
                    Close();
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    MessageBox.Show("fenpeiyichang");

                }
            }
        }
Example #31
0
        static void Main(string[] args)
        {
            var time= new DateTime();
            string connectionString = GetConnectionString();
            // Open a connection to the AdventureWorks database.
            using (SqlConnection connection =
                       new SqlConnection(connectionString))
            {
                connection.Open();

                // Perform an initial count on the destination table.
                SqlCommand commandRowCount = new SqlCommand(
                    "SELECT COUNT(*) FROM " +
                    "dbo.BulkCopyDemoMatchingColumns;",
                    connection);
                long countStart = System.Convert.ToInt32(
                    commandRowCount.ExecuteScalar());
                Console.WriteLine("Starting row count = {0}", countStart);

                // Create a table with some rows.
                DataTable newProducts = MakeTable();

                // Create the SqlBulkCopy object.
                // Note that the column positions in the source DataTable
                // match the column positions in the destination table so
                // there is no need to map columns.
                using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.BulkCopyDemoMatchingColumns";

                    try
                    {
                        // Write from the source to the destination.
                        var b  = DateTime.Now.Second;
                        bulkCopy.WriteToServer(newProducts);
                        var a = DateTime.Now.Second;
                        Console.WriteLine("bat dau:"+b);
                        Console.WriteLine("Tổng thời gian Lưu:"+a);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }

                // Perform a final count on the destination
                // table to see how many rows were added.
                long countEnd = System.Convert.ToInt32(
                    commandRowCount.ExecuteScalar());
                Console.WriteLine("Ending row count = {0}", countEnd);
                Console.WriteLine("{0} rows were added.", countEnd - countStart);
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
        }
 public int ktra(string sql)
 {
     SqlConnection conn = new SqlConnection(kn);
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        cmd.ExecuteScalar();
        int i =(int)cmd.ExecuteScalar();
        conn.Close();
        return i;
 }
Example #33
0
 protected void Page_Load(object sender, EventArgs e)
 {
     if ((Session["SCSLogin"] != null) & (Session["SCSDate"] != null))
     {
         if ((DateTime)Session["SCSDate"] <= DateTime.Now)
             Response.Redirect("Default.aspx");
         login = (string)Session["SCSLogin"];
         expDate = ((DateTime)Session["SCSDate"]).ToString();
         Response.Write("<div align=center> В системе как: " + login + "<BR> В системе до: " + expDate + "<BR></div>");
         Session["SCSDate"] = DateTime.Now.AddMinutes(10);
         Title = Title + " : " + login;
     }
     else Response.Redirect("Default.aspx");
     string constr = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString;
     SqlConnection con = new SqlConnection(constr);
     try
     {
         con.Open();
         SqlCommand cmd = new SqlCommand("exec ShowNewMessages @lg", con);
         cmd.Parameters.Add("@lg", login);
         SqlDataReader dr = cmd.ExecuteReader();
         gvMessageViewer.DataSource = dr;
         gvMessageViewer.DataBind();
         dr.Close();
         for (int i = 0; i < gvMessageViewer.Rows.Count; i++)
         {
             string mes = gvMessageViewer.Rows[i].Cells[2].Text;
             if (mes.StartsWith("[REQUEST MESSAGE]") == true)
             {
                 gvMessageViewer.Rows[i].Cells[2].Text = "[запрос]";
                 mes = mes.Replace("[REQUEST MESSAGE]", "");
                 cmd.CommandText = "exec getUserAccess @lg";
                 int acc = (int)cmd.ExecuteScalar();
                 if (acc == 4)
                 {
                     Response.Write("<div align=center>");
                     Response.Write("<a href=" + mes + "> подтвердить  " + gvMessageViewer.Rows[i].Cells[1].Text + "</a>");
                     Response.Write("<BR>");
                     Response.Write("</div>");
                 }
             }
         }
         cmd.CommandText = "exec NewMessagesReadedAlready @lg";
         cmd.ExecuteScalar();
     }
     catch (Exception err)
     {
         Response.Write(err.Message);
     }
     finally
     {
         con.Close();
     }
 }
Example #34
0
    public static string fnGetValue(string sSql, string sConn)
    {
        string sValue = null;

        System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(fnGetConStr(sConn));
        sqlConn.Open();
        System.Data.SqlClient.SqlCommand sqlComm = null;
        sqlComm = new System.Data.SqlClient.SqlCommand(sSql, sqlConn);
        sValue  = (sqlComm.ExecuteScalar() == null) ? " " : sqlComm.ExecuteScalar().ToString();
        sqlConn.Close();
        return(sValue);
    }
Example #35
0
 public int Ejecutar_Sentencia(string strSQL)
 {
     _cmd.Connection  = _cn;
     _cmd.CommandText = strSQL;
     _cmd.ExecuteNonQuery();
     if (strSQL.ToLower().Contains("INSERT"))
     {
         _cmd.CommandText = "SELECT @@IDENTITY";
         return(int.Parse(_cmd.ExecuteScalar().ToString()));
     }
     return(0);
 }
Example #36
0
    protected void appStatus()
    {
        System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
        insert.Connection = dbConnection;

        System.Data.SqlClient.SqlCommand getstatus = new System.Data.SqlClient.SqlCommand();
        getstatus.Connection = dbConnection;

        System.Data.SqlClient.SqlCommand getaddress = new System.Data.SqlClient.SqlCommand();
        getaddress.Connection = dbConnection;

        try { dbConnection.Open(); }
        catch { }
        try
        {
            foreach (RepeaterItem item in rptrTenant.Items)
            {
                var tenid  = ((Label)item.FindControl("userid")).Text;
                int number = int.Parse(tenid);

                getaddress.CommandText = "select accomodationid from application where tenantid = " + number;
                int address = Int32.Parse(getaddress.ExecuteScalar().ToString());

                getstatus.CommandText = "select status from application where tenantid = " + number + " and accomodationid = " + address;
                int id = Int32.Parse(getaddress.ExecuteScalar().ToString());

                bool status = (bool)getstatus.ExecuteScalar();


                if (status)
                {
                    Label lblstat = (Label)item.FindControl("lblstatus");
                    lblstat.Text = "Accepted";

                    DropDownList ddl = (DropDownList)item.FindControl("ddlStatus");
                    ddl.SelectedValue = "1";
                }
                else
                {
                    Label lblstat = (Label)item.FindControl("lblstatus");
                    lblstat.Text = "Rejected";

                    DropDownList ddl = (DropDownList)item.FindControl("ddlStatus");
                    ddl.SelectedValue = "0";
                }
            }
        }
        catch { }
        finally
        {
            dbConnection.Close();
        }
    }
Example #37
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
            sc.Close();
            sc.Open();
            System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand();
            insert.Connection = sc;

            email      = txtEmail.Text;
            documentID = Convert.ToInt32(ddlRecommendation.SelectedItem.Value);// get document ID

            insert.CommandText = "SELECT accountID from LoginInfo where email ='" + email + "'";
            int receiverAccountID = Convert.ToInt32(insert.ExecuteScalar());// get receiver ID

            insert.CommandText = "SELECT username,accountID from LoginInfo where email = '" + Session["email"] + "'";
            insert.ExecuteNonQuery();

            SqlDataReader reader = insert.ExecuteReader();
            while (reader.Read())
            {
                string user = reader["username"].ToString();
                Session["user"] = user;
                string ID = reader["accountID"].ToString();
                Session["accountID"] = ID;
            }
            reader.Close();

            System.Data.SqlClient.SqlCommand test = new System.Data.SqlClient.SqlCommand();
            test.Connection  = sc;
            test.CommandText = "INSERT INTO [Recommendations] VALUES('" + documentID + "','"
                               + receiverAccountID + "','"
                               + Session["user"] + "','"
                               + DateTime.Now.ToString() + "','"
                               + DateTime.Now.ToString() + "','"
                               + Convert.ToString(Session["user"]) + "')";
            test.ExecuteNonQuery();

            txtEmail.Text = " ";

            insert.CommandText = "SELECT accountID from [dbo].[LoginInfo] where accountID = '" + Session["accountID"] + "'";
            int accountID = Convert.ToInt32(insert.ExecuteScalar());// get receiver ID
            TextBox1.Text = accountID.ToString();


            //TextBox1.Text = Session["accountID"].ToString();
            //int accountID = Convert.ToInt32(TextBox1.Text);
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (localDB.State != ConnectionState.Open)
        {
            localDB.Open();
        }
        System.Data.SqlClient.SqlCommand getApplicants = new System.Data.SqlClient.SqlCommand();
        getApplicants.Connection  = localDB;
        getApplicants.CommandText = "Select count(AppID) From App where(Opened like '*')";
        string app = getApplicants.ExecuteScalar().ToString();

        lblapps.Text       = app;
        lblApplicants.Text = app;

        System.Data.SqlClient.SqlCommand getSch = new System.Data.SqlClient.SqlCommand();
        getSch.Connection  = localDB;
        getSch.CommandText = "Select count(PostID) From Scholarship where(Opened like '*')";
        lblsch.Text        = getSch.ExecuteScalar().ToString();
        string sch = getSch.ExecuteScalar().ToString();

        lblScholarship.Text = sch;

        System.Data.SqlClient.SqlCommand getEvents = new System.Data.SqlClient.SqlCommand();
        getEvents.Connection  = localDB;
        getEvents.CommandText = "Select count(PostID) From Event where(Opened like '*')";
        string even = getEvents.ExecuteScalar().ToString();

        lblEvents.Text = even;
        lblEv.Text     = even;

        int not = Convert.ToInt32(app) + Convert.ToInt32(sch) + Convert.ToInt32(even);

        if (not == 0)
        {
            lblNotification.Text = "";
        }
        else
        {
            lblNotification.Text = not.ToString();
        }

        //System.Data.SqlClient.SqlCommand getMess = new System.Data.SqlClient.SqlCommand();
        //getMess.Connection = localDB;
        //getMess.CommandText = "Select count(MessageID) From Messages1 where(HasSeen like '*')";
        //string messages = getMess.ExecuteScalar().ToString();
        //lblMessage.Text = messages;
        //localDB.Close();
    }
Example #39
0
    protected void btnSelect3_Click(object sender, EventArgs e)
    {
        string subscription = "Monthly Subscription";

        localDB.Open();
        System.Data.SqlClient.SqlCommand getUserID = new System.Data.SqlClient.SqlCommand();
        getUserID.Connection = localDB;

        //Find UserID for most recently entered User
        getUserID.CommandText = "select MAX(UserID) from USERS";
        string returnID = getUserID.ExecuteScalar().ToString();
        int    idUser   = Int32.Parse(returnID);

        getUserID.ExecuteNonQuery();

        //Update user table
        SqlCommand update = new SqlCommand("Update Users SET Subscription=@Subscription WHERE UserId=@MaxID;", localDB);

        update.Parameters.Add(new SqlParameter("@Subscription", subscription));
        update.Parameters.Add(new SqlParameter("@MaxID", idUser));
        update.ExecuteNonQuery();


        localDB.Close();

        Response.Redirect("Registration4.aspx");
    }
Example #40
0
        /// <summary>
        /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
        /// </summary>
        /// <param name="sql">SELECT 语句</param>
        /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用</returns>
        public object ExecuteScalar(string sql)
        {
            using (sqlConnection = this.GetSqlConnection())
            {
                if (sqlConnection == null)
                {
                    return(null);
                }
                try
                {
                    if (sqlConnection.State == System.Data.ConnectionState.Closed)
                    {
                        sqlConnection.Open();
                    }
                    sqlCommand = new SqlCommand(sql, sqlConnection);
                    return(sqlCommand.ExecuteScalar());
                }
                catch (Exception ex)
                {
#if DEBUG
                    System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
                    return(null);
                }
            }
        }
Example #41
0
        //Команда SELECT для суммирования ячеек
        public decimal SelectSum(String col, String tablename)
        {
            SqlConnection conn = DBUtils.GetDBConnection();

            conn.Open();
            decimal ret = 0;

            try
            {
                string sql = "Select sum(" + col + ") from " + tablename;

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                cmd.Connection  = conn;
                cmd.CommandText = sql;
                ret             = ((decimal)cmd.ExecuteScalar());
            }
            catch (Exception e)
            {
                MessageBox.Show("Error: " + e);
                MessageBox.Show(e.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            Console.Read();
            return(ret);
        }
Example #42
0
    //Loads the Employer's summary onto the page with the most recent description
    protected void ShowSummary(object sender, EventArgs e)
    {
        sc.Open();
        System.Data.SqlClient.SqlCommand getdbPersonID = new System.Data.SqlClient.SqlCommand();
        getdbPersonID.Connection = sc;
        //Gets the personid for the username
        getdbPersonID.CommandText = "SELECT PersonID from Account where Username = '******'";
        int accountID = (int)getdbPersonID.ExecuteScalar();


        ProfileSummary.Visible = true;
        subheader.Visible      = true;
        BtnEdit.Visible        = true;


        System.Data.SqlClient.SqlCommand getEmpSummary = new System.Data.SqlClient.SqlCommand();
        getEmpSummary.Connection  = sc;
        getEmpSummary.CommandText = "Select EmployerSummary from Employer where PersonID = @SummaryPersonID";
        getEmpSummary.Parameters.Add(new SqlParameter("@SummaryPersonID", accountID));
        String EmpSum = (String)getEmpSummary.ExecuteScalar();

        ProfileSummary.InnerText = EmpSum;

        sc.Close();
    }
        private static MemoryStream GetStreamFromDB(string typeName)
        {
            //SqlConnection conn = new SqlConnection(MySpace.Configuration.ConnectionStringProvider.ConnectionStrings["TestAssistants"].ConnectionString);
            SqlConnection conn = new SqlConnection(@"server=devsrv\sql2005;database=TestAssistants;uid=ASPADONet;pwd=12345;Connect Timeout=20;Integrated Security=false;");

            System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
            sqlCommand.Connection  = conn;
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.CommandText = "getSerializedObject";

            SqlParameter paramClassName = new SqlParameter("@className", SqlDbType.NVarChar, 100);

            paramClassName.Value = typeName;
            sqlCommand.Parameters.Add(paramClassName);


            object returnValue;

            try
            {
                conn.Open();
                returnValue = sqlCommand.ExecuteScalar();
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            MemoryStream stream = new MemoryStream((byte[])returnValue);

            return(stream);
        }
        /// <summary>
        /// Determines whether [is A member of group] [the specified group type].
        /// </summary>
        /// <param name="groupType">if set to <c>true</c> [group type].</param>
        /// <param name="GroupSid">The group sid.</param>
        /// <param name="netSqlAzManMode">if set to <c>true</c> [net SQL az man mode].</param>
        /// <param name="rootDsePath">The root dse path.</param>
        /// <param name="token">The token.</param>
        /// <param name="userGroupsCount">The user groups count.</param>
        /// <returns>
        ///     <c>true</c> if [is A member of group] [the specified group type]; otherwise, <c>false</c>.
        /// </returns>
        internal bool isAMemberOfGroup(bool groupType, byte[] GroupSid, bool netSqlAzManMode, string rootDsePath, byte[] token, int userGroupsCount)
        {
            SqlConnection conn = new SqlConnection(this.store.Storage.ConnectionString);

            conn.Open();
            bool result = false;

            try
            {
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("dbo.netsqlazman_IsAMemberOfGroup", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@GROUPTYPE", groupType);
                cmd.Parameters.AddWithValue("@GROUPOBJECTSID", GroupSid);
                cmd.Parameters.AddWithValue("@NETSQLAZMANMODE", netSqlAzManMode);
                cmd.Parameters.AddWithValue("@LDAPPATH", rootDsePath);
                cmd.Parameters.AddWithValue("@TOKEN", token);
                cmd.Parameters.AddWithValue("@USERGROUPSCOUNT", userGroupsCount);
                result = (bool)cmd.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
            return(result);
        }
Example #45
0
    protected void ddlProgramType_SelectedIndexChanged(object sender, EventArgs e)
    {
        System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection();

        // sc.ConnectionString = @"Server=localhost;Database=WildTek;Trusted_Connection=Yes;";

        String cs = ConfigurationManager.ConnectionStrings["WildTekConnectionString"].ConnectionString;

        sc.ConnectionString = cs;
        sc.Open();
        System.Data.SqlClient.SqlCommand selectOrganization = new System.Data.SqlClient.SqlCommand();
        selectOrganization.Connection = sc;

        selectOrganization.Parameters.Clear();

        selectOrganization.CommandText = "SELECT OrgID From Program WHERE ProgramID = @progamID";
        selectOrganization.Parameters.AddWithValue("@programID", ddlProgramType.SelectedItem.Value);
        String tempOrg = (String)selectOrganization.ExecuteScalar();

        ddlOrganization.ClearSelection();
        for (int i = 0; i < ddlOrganization.Items.Count; i++)
        {
            if (ddlOrganization.Items[i].Value == tempOrg)
            {
                ddlOrganization.Items[i].Selected = true;
            }
        }
    }
Example #46
0
    public static object RetrieveSingleValue(string Sql)
    {
        System.Data.SqlClient.SqlCommand objCom = new System.Data.SqlClient.SqlCommand();
        object ld_Result;

        if (!OpenConnection())
        {
            return(0);
        }

        try
        {
            objCom.Connection     = lo_Connection;
            objCom.CommandTimeout = CommandTimeOutSeconds;
            objCom.CommandText    = Sql;
            ld_Result             = objCom.ExecuteScalar();

            return(ld_Result);
        }
        catch
        {
            return(0);
        }
        finally
        {
            lo_Connection.Close();
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["UserName"] == null || HttpContext.Current.Request.UrlReferrer == null)
        {
            Response.Redirect("Login.aspx");
        }

        localDB.Open();
        System.Data.SqlClient.SqlCommand getApplicants = new System.Data.SqlClient.SqlCommand();
        getApplicants.Connection  = localDB;
        getApplicants.CommandText = "Select count(AppID) From App";
        lblApplicants.Text        = getApplicants.ExecuteScalar().ToString();

        System.Data.SqlClient.SqlCommand getjobs = new System.Data.SqlClient.SqlCommand();
        getjobs.Connection  = localDB;
        getjobs.CommandText = "Select count(PostID) From Job";
        lblJobs.Text        = getjobs.ExecuteScalar().ToString();

        System.Data.SqlClient.SqlCommand getscholarships = new System.Data.SqlClient.SqlCommand();
        getscholarships.Connection  = localDB;
        getscholarships.CommandText = "Select count(PostID) From Scholarship";
        lblScholarships.InnerText   = getscholarships.ExecuteScalar().ToString();

        System.Data.SqlClient.SqlCommand getEvent = new System.Data.SqlClient.SqlCommand();
        getEvent.Connection  = localDB;
        getEvent.CommandText = "Select count(PostID) From Event";
        lblEvent.InnerText   = getEvent.ExecuteScalar().ToString();

        localDB.Close();
    }
Example #48
0
        public bool UserExists(string slack_id)
        {
            QC.SqlCommand exists = new QC.SqlCommand();
            exists.CommandType = System.Data.CommandType.Text;
            exists.Connection  = this.conn;
            exists.CommandText = @"SELECT * FROM users WHERE slack_id = @User";

            var param1 = new QC.SqlParameter("User", slack_id);

            exists.Parameters.Add(param1);

            try
            {
                int id = (int)exists.ExecuteScalar();
                if (id > 0)
                {
                    return(true);
                }
            }
            catch (Exception e)
            {
                return(false);
            }

            return(false);
        }
        public bool seachingIfSameValueExists(string whichColumnToCheck, string givenValue)
        {
            bool          doesExist = true;
            SqlConnection con       = new SqlConnection(connectionString);

            try
            {
                con.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection  = con;
                cmd.Parameters.AddWithValue("@" + whichColumnToCheck, givenValue);
                cmd.CommandText = "SELECT isActive FROM [ManagementToolDatabase].[dbo].[UserRegistrationTable]" +
                                  " WHERE " + whichColumnToCheck + " = @" + whichColumnToCheck;
                try
                {
                    string isActive = cmd.ExecuteScalar().ToString();
                    if (isActive.Equals(true) || isActive.Equals(false))
                    {
                        doesExist = true;
                    }
                }
                catch (Exception)
                {
                    doesExist = false;
                }
                con.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("Unable to open database");
            }
            return(doesExist);
        }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["UserName"] == null || HttpContext.Current.Request.UrlReferrer == null)
        {
            Response.Redirect("Login.aspx");
        }

        sendHide.Visible        = false;
        responseHide.Visible    = false;
        noMessagesAlert.Visible = false;

        localDB.Open();

        //Select User's school name
        System.Data.SqlClient.SqlCommand selectSchoolName = new System.Data.SqlClient.SqlCommand();
        selectSchoolName.Connection  = localDB;
        selectSchoolName.CommandText = "select schoolName from school s inner join schoolemployee se on s.schoolID = se.schoolID";
        string schoolName = selectSchoolName.ExecuteScalar().ToString();

        mainViewUserName.InnerHtml = schoolName;

        sidebarContactName1.InnerText = schoolName;

        //Select User's First and Last Name
        System.Data.SqlClient.SqlCommand selectUserName = new System.Data.SqlClient.SqlCommand();
        selectUserName.Connection  = localDB;
        selectUserName.CommandText = "select concat(firstname, ' ', lastname) as userName from users inner join messages on userID = messagetoid AND messageid = (SELECT MAX(messageID) FROM messages)";
        string userName = selectUserName.ExecuteScalar().ToString();

        mainViewSpeakingWith.InnerHtml = "Speaking with: " + userName;

        //Display sidebar users
    }
Example #51
0
 public override object ConsultaDato(string SQL)
 {
     //Datos.connectionString.
     try
     {
         using (System.Data.SqlClient.SqlConnection Cnn = new System.Data.SqlClient.SqlConnection(connectionString))
         {
             using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(SQL, Cnn))
             {
                 try
                 {
                     Cmd.CommandType = CommandType.Text;
                     Cmd.CommandText = SQL;
                     Cnn.Open();
                     return(Cmd.ExecuteScalar());
                 }
                 catch (Exception ex)
                 {
                     throw new Exception("Error de acceso a datos, Error: " + ex.ToString());
                 }
                 finally
                 {
                     if (Cnn.State == ConnectionState.Open)
                     {
                         Cnn.Close();
                     }
                 }
             }
         }
     }
     catch (Exception ex)
     {
         throw new Exception("Error de acceso a datos, Error: " + ex.ToString());
     }
 }
Example #52
0
        public int Insert(System.Data.SqlClient.SqlConnection sqlConnection, System.Data.SqlClient.SqlTransaction sqlTran)
        {
            System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand("Insert into ASBLOB(FileContent,FileName,FileLength,FileType,FileExtName,AddUserNo,AddDate,IPAddress) Values(@FileContent,@FileName,@FileLength,@FileType,@FileExtName,@AddUserNo,@AddDate,@IPAddress);", sqlConnection);
            sqlcmd.Transaction = sqlTran;
            List <SqlParameter> _p = new List <SqlParameter>();

            _p.Add(new SqlParameter("@FileContent", this.FileContent));
            _p.Add(new SqlParameter("@FileName", this.FileName));
            _p.Add(new SqlParameter("@FileLength", this.FileLength));
            _p.Add(new SqlParameter("@FileType", this.FileType));
            _p.Add(new SqlParameter("@FileExtName", this.FileExtension));
            _p.Add(new SqlParameter("@AddUserNo", this.AddUser));
            _p.Add(new SqlParameter("@AddDate", this.AddDate));
            _p.Add(new SqlParameter("@IPAddress", this.IPAddress));

            sqlcmd.Parameters.AddRange(_p.ToArray());

            try
            {
                sqlcmd.ExecuteNonQuery();
                sqlcmd.CommandText = "select @@IDENTITY";
                int blobId = Convert.ToInt32(sqlcmd.ExecuteScalar());

                return(blobId);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
Example #53
0
 public int Add(Person person)
 {
     try
     {
         _connection.Open();
         using (System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand())
         {
             sqlcommand.CommandText = @"
                        INSERT INTO [dbo].[Person] ([FirstName] ,[LastName] ,[Phone] ,[Email] ,[CreateStamp] ,[ModifStamp])
                        OUTPUT INSERTED.ID
                         VALUES (
                         '" + person.FirstName + @"',
                         '" + person.LastName + @"',
                         '" + person.PhoneNumber + @"',
                         '" + person.Email + @"',
                         '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + @"',
                         '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + @"');";
             sqlcommand.Connection  = _connection; int count = (int)sqlcommand.ExecuteScalar(); return(count);
         }
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.Message);
     }
     finally
     {
         _connection.Close();
     }
     return(-1);
 }
Example #54
0
    public bool CheckIsRecordExist(string a_strChkSqlCommand)
    {
        System.Data.SqlClient.SqlCommand objCommand;
        bool RecordExists = false;

        try
        {
            bool AttemptToConnect = OpenSqlConnection();
            objCommand            = new System.Data.SqlClient.SqlCommand(a_strChkSqlCommand);
            objCommand.Connection = myConn;
            //objCommand.CommandTimeout=ConnectionTimeOut;
            objCommand.CommandType = CommandType.Text;
            string result = Convert.ToString(objCommand.ExecuteScalar());
            objCommand.Dispose();

            if (result != "")
            {
                RecordExists = true;
            }
            return(RecordExists);
        }
        catch (SqlException ex)
        {
            ex.Data.Clear();
            return(RecordExists);
        }
        catch (Exception ex)
        {
            ex.Data.Clear();
            return(RecordExists);
        }
        finally
        { CloseConnection(); }
    }
Example #55
0
    //Use method in order to select the accommodationID from the Accommodation table.
    public int returnAccomIDFromDB(Accommodation newAccom)
    {
        int accomID;

        try
        {
            sc1.Open();

            System.Data.SqlClient.SqlCommand getAccomID = new System.Data.SqlClient.SqlCommand();
            getAccomID.Connection  = sc1;
            getAccomID.CommandText = "SELECT AccommodationID FROM ACCOMMODATION WHERE HostID=@hostID AND Street=@address AND CityCo=@city AND AccomState=@state AND Zip=@zip AND Price=@price AND RoomType=@roomType AND Neighborhood=@neighborhood AND Description=@description";
            getAccomID.Parameters.Add(new SqlParameter("@address", newAccom.getAddress()));
            getAccomID.Parameters.Add(new SqlParameter("@city", newAccom.getAccomCity()));
            getAccomID.Parameters.Add(new SqlParameter("@state", newAccom.getAccomState()));
            getAccomID.Parameters.Add(new SqlParameter("@zip", newAccom.getAccomZip()));
            getAccomID.Parameters.Add(new SqlParameter("@price", newAccom.getAccomPrice()));
            getAccomID.Parameters.Add(new SqlParameter("@roomType", newAccom.getAccomRoomType()));
            getAccomID.Parameters.Add(new SqlParameter("@neighborhood", newAccom.getAccomNeighborhood()));
            getAccomID.Parameters.Add(new SqlParameter("@description", newAccom.getAccomDescription()));
            getAccomID.Parameters.Add(new SqlParameter("@hostID", newAccom.getAccomHostID()));
            getAccomID.ExecuteNonQuery();
            accomID = Convert.ToInt32(getAccomID.ExecuteScalar());
            sc1.Close();
            return(accomID);
        }
        catch
        {
            return(0);
        }
    }
Example #56
0
        protected object ScalarSQLCommand(System.Data.SqlClient.SqlCommand sqlcommand)
        {
            object ret = sqlcommand.ExecuteScalar();

            DisposeCommand(sqlcommand);
            return(ret);
        }
        /// <summary>
        /// 返回一行一列的数据
        /// </summary>
        /// <param name="ConnStr">连接字符串</param>
        /// <param name="strSQL">SQL语句</param>
        /// <returns></returns>
        public virtual object GetScalarData(string ConnStr, string strSQL)
        {
            {
                try
                {
                    using (sqlconn = new SDC.SqlConnection(ConnStr))
                    {
                        sqlconn.Open();

                        using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
                        {
                            return(sqlcmd.ExecuteScalar());
                        }
                    }
                }

                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (sqlconn != null)
                    {
                        if (sqlconn.State != SD.ConnectionState.Closed)
                        {
                            sqlconn.Close();
                        }

                        sqlconn.Dispose();
                    }
                }
            }
        }
        public override Object CreateSqlInsert(string _tableName, bool autoincr)
        {
            System.Text.StringBuilder _sb = new System.Text.StringBuilder();

            _sb.Append("INSERT INTO ");
            _sb.Append("[" + _tableName + "]");
            _sb.Append(" (");
            _sb.Append(_sb_insertCol);
            _sb.Append(") VALUES(");
            _sb.Append(_sb_insertVal);
            _sb.Append(") ");

            if (autoincr)
            {
                _sb.AppendLine("SELECT SCOPE_IDENTITY()");
            }

            this._command.CommandText = _sb.ToString();
            Settings.Log.LogSQL(_command.CommandText, _command.Parameters);
            Connect();
            var ret = _command.ExecuteScalar();

            Disconnect();
            return(ret);
        }
Example #59
0
 public void SqlConnection(String Statement)
 {
     try
     {
         System.Data.SqlClient.SqlConnection sc         = new System.Data.SqlClient.SqlConnection();
         System.Data.SqlClient.SqlDataReader DataReader = null;
         sc.ConnectionString = @"Server=SILAS-PC\LOCALHOST; Database=WLS;Trusted_Connection=Yes;";
         sc.Open();
         System.Diagnostics.Debug.WriteLine("databse connection opened");
         System.Data.SqlClient.SqlCommand sendComm = new System.Data.SqlClient.SqlCommand();
         sendComm.Connection  = sc;
         sendComm.CommandText = Statement;
         if (Statement.Contains("Delete") || (Statement.Contains("Insert")))
         {
             sendComm.ExecuteNonQuery();
         }
         if ((Statement.Contains("Select")))
         {
             String totSalary = sendComm.ExecuteScalar().ToString();
         }
         if (Statement.Contains("Equip") && (Statement.Contains("Select")))
         {
         }
         sc.Close();
     }
     catch (System.Data.SqlClient.SqlException sqlException)
     {
         System.Diagnostics.Debug.WriteLine(sqlException.Message);
     }
 }
Example #60
0
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection sqlCon = new SqlConnection(str);

            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("select photo from test where name=@name", sqlCon);
            command.Parameters.AddWithValue("@name", comboBox1.SelectedValue.ToString());

            sqlCon.Open();
            byte[] image = (byte[])command.ExecuteScalar();
            if (image != null)
            {
                sqlCon.Close();
                //Save the picture from Db in the memory stream
                MemoryStream ms = new MemoryStream(image);
                //Load the picture from memory stream to picture box
                //pictureBox1.Image = Image.FromStream(ms);
                foreach (PictureBox pb in listPB)
                {
                    //Find an empty picture box
                    if (pb.Image == null)
                    {
                        pb.Image = Image.FromStream(ms);
                        break;
                    }
                }
            }
        }