Esempio n. 1
2
    public List<string> consultarMarca(string id)
    {
        List<string> marcas = new List<string>();
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|DBTag.mdf';Integrated Security=True;User Instance=True");
        // Abre a conexão
        conn.Open();
        try
        {
            SqlCommand select = new SqlCommand("SELECT * from TbMarca where id=@id", conn);
            SqlParameter pID = new SqlParameter("id", id);
            select.Parameters.Add(pID);
            // Lê, linha a linha a tabela
            SqlDataReader dr = select.ExecuteReader();
            while (dr.Read())
            {
                marcas.Add(dr["id"].ToString());
                marcas.Add(dr["nome_marca"].ToString());
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.ToString(), ex);
        }

        return marcas;
    }
    protected string generate_forum_name(string group_id)
    {
        string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;

        SqlConnection connection = new SqlConnection(conn);

        string sqlqry = "SELECT group_name FROM forum_group WHERE group_id=@p1";

        SqlCommand command = new SqlCommand(sqlqry, connection);

        SqlParameter param1 = new SqlParameter();
        param1.SqlDbType = System.Data.SqlDbType.Int;
        param1.ParameterName = "@p1";
        param1.Value = group_id;
        command.Parameters.Add(param1);

        SqlDataReader Reader = null;

        command.Connection.Open();
        Reader = command.ExecuteReader();
        Reader.Read();
        string name = Reader[0].ToString();
        command.Connection.Close();

        return name;
    }
    //method to open or close the database connection
    public SqlConnection ManageDatabaseConnection(string actionToPerform)
    {
        string connectionString = "Data Source=ph0ibk90ya.database.windows.net;Initial Catalog=ParkingLot;Integrated Security=False;User ID=samara;Password=s4m4r4DEV;Connect Timeout=100;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
        SqlConnection sqlConnection = new SqlConnection(connectionString);

        try
        {
            //desicion to whether open or close the database connection
            if (actionToPerform.Equals("Open"))
            {
                sqlConnection.Open();
            }
            else
            {
                sqlConnection.Close();
            }

        }
        catch (SqlException sqlException)
        {

            //throw the exception to upper layers
            throw sqlException;
        }

        return sqlConnection;
    }
Esempio n. 4
1
    protected int getTotalCount()
    {
        SqlConnection connection = new SqlConnection(GetConnectionString());

        DataTable dt = new DataTable();

        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM tblContact";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
            sqlDa.Fill(dt);

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
        return dt.Rows.Count;
    }
    protected void Submit_Click1(object sender, EventArgs e)
    {
        try
        {
            //generate a new GUID ID
            Guid newGUID = Guid.NewGuid();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString);
            conn.Open();
            string insertQuery = "insert into UserData (ID,UserName,Email,Password,Country) values (@ID ,@Uname ,@email ,@password, @country)";
            SqlCommand com = new SqlCommand(insertQuery, conn);
            com.Parameters.AddWithValue("@ID", newGUID.ToString());
            com.Parameters.AddWithValue("@Uname", IDSUaserName.Text);
            com.Parameters.AddWithValue("@email", IDSEmail.Text);
            com.Parameters.AddWithValue("@password", IDSPassword.Text);
            com.Parameters.AddWithValue("@country", IDSCountry.SelectedItem.ToString());

            com.ExecuteNonQuery();
            Response.Redirect("Manager.aspx");
            Response.Write("Registration is sucessful");

            conn.Close();
        }
        catch(Exception ex)
        {
            Response.Write("Error:"+ex.ToString());
        }
    }
Esempio n. 6
1
        public static DataTable GetAllCidades(int estado_id)
        {
            DataTable retorno = new DataTable();
            StringBuilder SQL = new StringBuilder();
            SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID");

            try
            {
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(SQL.ToString(), connection);
                    command.Parameters.AddWithValue("@ESTADO_ID", estado_id);
                    command.ExecuteNonQuery();
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(retorno);
                }
            }
            catch (Exception erro)
            {
                throw erro;
            }

            return retorno;
        }
Esempio n. 7
0
    static void Main()
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder["Data Source"] = "aad-managed-demo.database.windows.net"; // replace with your server name
        builder["Initial Catalog"] = "demo"; // replace with your database name
        builder["Authentication"] = SqlAuthenticationMethod.ActiveDirectoryPassword;
        builder["Connect Timeout"] = 30;
        string username = "******"; // replace with your username

        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            try
            {
                connection.Credential = CreateCredential(username);
                connection.Open();
                using (SqlCommand cmd = new SqlCommand(@"SELECT SUSER_SNAME()", connection))
                {
                    Console.WriteLine("You have successfully logged on as: " + (string)cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        Console.WriteLine("Please press any key to stop");
        Console.ReadKey();
    }
Esempio n. 8
0
    protected void btnOK_Click(object sender, EventArgs e)
    {
        string ID;
        SqlConnection mycon = new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
        mycon.Open();
        DataSet mydataset = new DataSet();
        SqlDataAdapter mydataadapter = new SqlDataAdapter("select * from tb_Blog where UserName='******'", mycon);
        mydataadapter.Fill(mydataset, "tb_Blog");
        DataRowView rowview = mydataset.Tables["tb_Blog"].DefaultView[0];
        ID = rowview["BlogID"].ToString();

        string P_str_Com = "Insert into tb_Message(FriendName,Sex,HomePhone,MobilePhone,QQ,ICQ,Address,Birthday,Email,PostCode,BlogID,IP)"
            +" values ('"+this.txtName.Text+"','"+this.DropDownList1.SelectedValue+"','"+this.txtHphone.Text+"'"
        +",'"+this.txtMphone.Text+"','"+this.txtQQ.Text+"','"+this.txtICQ.Text+"','"+this.txtAddress.Text+"'"
        +",'"+this.txtBirthday.Text+"','"+this.txtEmail.Text+"','"+this.txtPostCode.Text+"','"+ID+"','"+Request.UserHostAddress+"')";
        SqlData da = new SqlData();
        if (!ValidateDate1(txtBirthday.Text) && !ValidateDate2(txtBirthday.Text) && !ValidateDate3(txtBirthday.Text))
        {
            Response.Write("<script language=javascript>alert('输入的日期格式有误!');location='javascript:history.go(-1)'</script>");
        }
        else
        {
            bool add = da.ExceSQL(P_str_Com);
            if (add == true)
            {
                Response.Write("<script language=javascript>alert('添加成功!');location='AddLinkMan.aspx'</script>");
            }
            else
            {
                Response.Write("<script language=javascript>alert('添加失败!');location='javascript:history.go(-1)'</script>");
            }
        }
    }
Esempio n. 9
0
 public SqlConnection GetConn()
 {
     string constr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["sqlconnstr"].ConnectionString;
     SqlConnection conn = new SqlConnection(constr);
     conn.Open();
     return conn;
 }
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=DIP\\SQLEXPRESS;Initial Catalog=UVPCE_DB;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("insert into [user](username,subject,comment,posteddate) values(@username,@subject,@comment,@postedate)", con);

        cmd.Parameters.AddWithValue("@username", Textname.Text);
        cmd.Parameters.AddWithValue("@subject", txtSubject.Text);
        cmd.Parameters.AddWithValue("@comment", txtComment.Text);
        cmd.Parameters.AddWithValue("@postedate", DateTime.Now);
        con.Open();
        //SqlCommand cmd = new SqlCommand("insert into (username,subject,comment,posteddate) values('"+Textname.Text+"','"+txtSubject.Text+"','"+txtComment+"')",con);
        cmd.ExecuteNonQuery();
        // con.Close();

        SqlCommand cmd1 = new SqlCommand("select no from [user] where username='******' and subject='" + txtSubject.Text + "' and comment='" + txtSubject.Text + "'", con);
        SqlDataReader dr = cmd1.ExecuteReader();
        while (dr.Read())
        {
            Label1.Text = dr["no"].ToString();
        }

        Textname.Text = string.Empty;
        txtSubject.Text = string.Empty;
        txtComment.Text = string.Empty;
        BindRepeaterData();
    }
Esempio n. 11
0
        protected void Fill_User_Header()
        {
            DataView view = null;
            SqlConnection con;
            SqlCommand cmd = new SqlCommand();
            DataSet ds     = new DataSet();
            DataTable dt   = new DataTable();
            System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/BitOp");
            System.Configuration.ConnectionStringSettings connString;
            connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BopDBConnectionString"];
            con            = new SqlConnection(connString.ToString());
            cmd.Connection = con;
            con.Open();
            string sql = @"SELECT Fecha_Desde, Inicio_Nombre, Region, Supervisor
                         FROM Criterios
                         WHERE Criterio_ID = " + @Criterio_ID;
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds);
            dt = ds.Tables[0];
            view = new DataView(dt);
            foreach (DataRowView row in view)
            {
                Lbl_Fecha_Desde.Text    = row["Fecha_Desde"].ToString("dd-MM-yyyy");
                Lbl_Inicio_Descrip.Text = row["Inicio_Nombre"].ToString();
                Lbl_Region.Text         = row["Region"].ToString();
                Lbl_Supervisor.Text     = row["Supervisor"].ToString();
             }

            con.Close();
        }
Esempio n. 12
0
    public static List<string> SearchEmployees(string prefixText, int count)
    {
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["rateMyMPConnectionString"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand())
            {

                cmd.CommandText = "SELECT dbo.mpDetails.constituencyId,dbo.userMaster.profilePic, dbo.constituency.constituency, dbo.state.state, dbo.userMaster.firstName, dbo.userMaster.middleName, dbo.userMaster.lastName,dbo.userMaster.profilePic FROM dbo.constituency INNER JOIN dbo.state ON dbo.constituency.stateId = dbo.state.stateId INNER JOIN dbo.mpDetails ON dbo.state.stateId = dbo.mpDetails.permanentStateId AND dbo.constituency.constituencyId = dbo.mpDetails.constituencyId INNER JOIN dbo.userMaster ON dbo.mpDetails.guid = dbo.userMaster.guid where dbo.userMaster.firstName like '%' + @search+'%' or dbo.userMaster.middleName like '%'+ @search+'%' or dbo.userMaster.lastName like '%' + @search+'%' or  dbo.state.state  like '%'+ @search+'%' or dbo.constituency.constituency like '%'+ @search+'%'";
                cmd.Parameters.AddWithValue("@search", prefixText);
                cmd.Connection = conn;
                conn.Open();
                List<string> employees = new List<string>();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        employees.Add(AjaxControlToolkit.AutoCompleteExtender
                           .CreateAutoCompleteItem(string.Format("{0}{1}{2}{3}{4}{5} ",
                           sdr["firstName"] + " ", sdr["middleName"] + " ", sdr["lastName"] + " ,", sdr["constituency"] + " ,", sdr["state"] + " ,", sdr["profilePic"].ToString()),
                           sdr["constituencyId"].ToString()));
                    }
                }
                conn.Close();
                return employees;
            }
        }
    }
Esempio n. 13
0
    public building(int id)
    {
        try{
            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["mainconn"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * FROM building WHERE id = @id", conn);
            cmd.Parameters.AddWithValue("id", id);
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            ID = new dataObject(id);
            Name = new dataObject(dr["Name"].ToString());
            Address1 = new dataObject(dr["Address1"].ToString());
            Address2 = new dataObject(dr["Address2"].ToString());
            Address3 = new dataObject(dr["Address3"].ToString());
            City = new dataObject(dr["City"].ToString());
            State = new dataObject(dr["State"].ToString());
            ZipCode = new dataObject(dr["ZipCode"].ToString());
            PhoneNumber = new dataObject(dr["PhoneNumber"].ToString());
            PhoneNumberExtension = new dataObject(dr["PhoneNumberExtension"].ToString());
            FaxNumber = new dataObject(dr["FaxNumber"].ToString());
            MobilePhoneNumber = new dataObject(dr["MobilePhoneNumber"].ToString());
            EmailAddress = new dataObject(dr["EmailAddress"].ToString());
            Contact = new dataObject(dr["Contact"].ToString());
            Active = new dataObject(Convert.ToBoolean(dr["Active"]));
            Comment = new dataObject(dr["Comment"].ToString());
            InputDate = new dataObject(Convert.ToDateTime(dr["InputDate"]));
            InputEmploee = new dataObject(new employee(Convert.ToInt32(dr["InputEmployeeID"])));

        }catch(Exception e){
            throw e;
        }
    }
Esempio n. 14
0
    SqlConnection connect; //database object

    public LoginClass(string usr, string pass)
    {
        username = usr;
        password = pass;
        connect = new SqlConnection(ConfigurationManager.ConnectionStrings["CommunityAssistConnectionString"].ConnectionString);

    }
Esempio n. 15
0
    void GetMedicalHistory(int ID)
    {
        using (SqlConnection con = new SqlConnection(Helper.GetCon()))
        using (SqlCommand cmd = new SqlCommand())
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "SELECT Operation, Details, StartDate, EndDate " +
                "FROM MedicalHistory WHERE DispatchID=@DispatchID";
            cmd.Parameters.AddWithValue("@DispatchID", ID);
            using (SqlDataReader data = cmd.ExecuteReader())
            {
                if (data.HasRows)
                {
                    while (data.Read())
                    {
                        DateTime sDate = Convert.ToDateTime(data["StartDate"].ToString());
                        DateTime eDate = Convert.ToDateTime(data["EndDate"].ToString());

                        txtOperation.Text = data["Operation"].ToString();
                        txtStartDate.Text = sDate.ToString("MM/dd/yyyy");
                        txtEndDate.Text = eDate.ToString("MM/dd/yyyy");
                        txtDetails.Text = data["Details"].ToString();

                    }
                    con.Close();
                }
                else
                {
                    con.Close();
                    Response.Redirect("View.aspx");
                }
            }
        }
    }
Esempio n. 16
0
    protected void Button4_Click(object sender, EventArgs e)
    {
        con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Shubhdip\\Downloads\\job portal\\job portal\\Jobadda\\App_Data\\JobportalDB.mdf;Integrated Security=True;User Instance=True");
        con.Open();

         int cid=0;
         cid = Convert.ToInt32(Session["Cid"].ToString());

         int rws = 0;
         rws = Convert.ToInt32(GridView1.Rows.Count.ToString());
          // Label22.Text = GridView1.Rows.Count.ToString();

        for (int i = 0; i < rws ; i++)
         {
             CheckBox chk = (CheckBox)GridView1.Rows[i].FindControl("CheckBox3");

             if (chk.Checked == true)
             {
                 String a = GridView1.Rows[i].Cells[2].Text;
                 string query = "insert into Saved_jobs (candidate_id,jobpost_id) values (" + cid + "," + a + ")";
                 cmd = new SqlCommand(query, con);
                 cmd.ExecuteNonQuery();
             }
         }
    }
Esempio n. 17
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();
        };
    }
Esempio n. 18
0
        public Category[] GetAllCategories()
        {
            List<Category> myCategories = new List<Category>();
            SqlConnection myConn = new SqlConnection(connstring);
            myConn.Open();

                SqlCommand mySqlCommand = new SqlCommand("select * from Category", myConn);
                SqlDataReader reader = mySqlCommand.ExecuteReader();

            while (reader.Read())
            {
                Category myCategory = new Category();
                object id = reader["Id"];

                if (id != null)
                {
                    int categoryId = -1;
                    if (!int.TryParse(id.ToString(), out categoryId))
                    {
                        throw new Exception("Failed to parse Id of video.");
                    }
                    myCategory.Id = categoryId;
                }

                myCategory.Name = reader["Name"].ToString();

                myCategories.Add(myCategory);
            }

            myConn.Close();

            return myCategories.ToArray();
        }
Esempio n. 19
0
        public Video[] GetAllVideo()
        {
            List<Video> myVideos = new List<Video>();
            SqlConnection myConn = new SqlConnection(connstring);
            myConn.Open();

            SqlCommand mySqlCommand = new SqlCommand("select * from video", myConn);
            SqlDataReader reader = mySqlCommand.ExecuteReader();

            while (reader.Read())
            {
                Video myVideo = new Video();
                object id = reader["Id"];

                if(id != null)
                {
                    int videoId = -1;
                    if (!int.TryParse(id.ToString(), out videoId))
                    {
                        throw new Exception("Failed to parse Id of video.");
                    }

                    myVideo.Id = videoId;
                }

                myVideo.Name = reader["Name"].ToString();
                myVideo.Url = reader["Url"].ToString();
                myVideos.Add(myVideo);
            }

            myConn.Close();

            return myVideos.ToArray();
        }
Esempio n. 20
0
    void GetCorporateMembership()
    {
        if (CheckCorporateMembership())
        {
            using (SqlConnection con = new SqlConnection(Helper.GetCon()))
            using (SqlCommand cmd = new SqlCommand())
            {
                con.Open();
                cmd.Connection = con;
                cmd.CommandText = "SELECT CorporateID FROM Users " +
                              "WHERE UserID=@UserID";
                cmd.Parameters.AddWithValue("@UserID", Session["userid"].ToString());
                int corporateID = (int)cmd.ExecuteScalar();

                cmd.CommandText = "SELECT Name, Length FROM CorporateAccounts " +
                                  "INNER JOIN CorporatePayments ON CorporateAccounts.CorporateID=" +
                                  "CorporatePayments.CorporateID WHERE CorporateAccounts.CorporateID=" +
                                  "@CorporateID";
                cmd.Parameters.AddWithValue("@CorporateID", corporateID);
                SqlDataReader da = cmd.ExecuteReader();
                while (da.Read())
                {
                    txtEmployerName.Text = da["Name"].ToString();
                    txtCorporateLength.Text = da["Length"].ToString();
                    txtCorporateType.Text = "Corporate/Bulk";
                }
            }
        }
        else
        {
            panelCorporate.Visible = false;
        }
    }
 protected void Page_Load(object sender, EventArgs e)
 {
     if (Page.IsPostBack)
     {
         Page.Validate();
         if (Page.IsValid)
         {
             SqlConnection dbConnection = new SqlConnection("Data Source=cscsql2.carrollu.edu;Initial Catalog=csc319mcrowell;Persist Security Info=True;User ID=csc319mcrowell;Password=393160");
             try
             {
                 dbConnection.Open();
                 string SQLString = "SELECT * FROM students WHERE studentID=" + studentID.Text;
                 SqlCommand checkIDTable = new SqlCommand(SQLString, dbConnection);
                 SqlDataReader idRecords = checkIDTable.ExecuteReader();
                 if (idRecords.Read())
                 {
                     Response.Redirect("ReturningStudent.aspx?studentID=" + studentID.Text);
                     idRecords.Close();
                 }
                 else
                 {
                     validateMessage.Text = "<p>**Invalid student ID**</P>";
                     idRecords.Close();
                 }
             }
             catch (SqlException exception)
             {
                 Response.Write("<p>Error code " + exception.Number + ": " + exception.Message + "</p>");
             }
         }
     }
 }
Esempio n. 22
0
 public DBAccounts()
 {
     string constr = ConfigurationManager.ConnectionStrings["Accounts"].ConnectionString;
     Con = new SqlConnection(constr);
     Com = new SqlCommand();
     Com.Connection = Con;
 }
Esempio n. 23
0
    protected void btnOpen_Click(object sender, EventArgs e)
    {
        string dbName = tbDBName.Text;

        SqlConnection con = new SqlConnection();
        con.StateChange += con_StateChange;
        con.InfoMessage += con_InfoMessage;

        try
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings[dbName].ConnectionString;
            con.Open();
        }
        catch (SqlException ex)
        {
            lbLog.Items.Add(ex.Message);
        }
        catch (Exception ex)
        {
            lbLog.Items.Add(ex.Message);
        }
        finally
        {
            con.Dispose();
        }

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlTransaction myTransaction = null;
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb;
                                                            User ID=ajjpsqlserverdb; Password= Devry2010;");

                conn.Open();
                SqlCommand command = conn.CreateCommand();
                string strSQL;
                string txtBoxText = TextBox1.Text;
                txtBoxText = txtBoxText.Replace("'", "''");

                myTransaction = conn.BeginTransaction();
                command.Transaction = myTransaction;

                strSQL = "UPDATE aspnet_Membership SET Password = '******' WHERE UserID = '" + DropDownList1.SelectedValue + "'";

                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = strSQL;
                command.ExecuteNonQuery();

                myTransaction.Commit();

                command.Connection.Close();
                Response.Redirect("~/User/Main.aspx");
            }
            catch (Exception ex)
            {
                lblErr.Text = ex.Message;
            }
        }
    }
Esempio n. 25
0
 //  To insert 'Group' record in database by stored procedure
 public int InsertGroup(string GroupName, string GroupDescription,bool IsActive, int  LoginUser,string Ret)
 {
     SqlConnection Conn = new SqlConnection(ConnStr);
         Conn.Open();
         //  'uspInsertGroup' stored procedure is used to insert record in Group table
         SqlCommand DCmd = new SqlCommand("uspInsertGroup", Conn);
         DCmd.CommandType = CommandType.StoredProcedure;
         try
         {
             DCmd.Parameters.AddWithValue("@GroupName", GroupName);
             DCmd.Parameters.AddWithValue("@GroupDescription", GroupDescription);
             DCmd.Parameters.AddWithValue("@LoggedInUser", LoginUser);
             DCmd.Parameters.AddWithValue("@IsActive", IsActive);
             DCmd.Parameters.AddWithValue("@RetMsg", Ret);
              return DCmd.ExecuteNonQuery();
         }
         catch
         {
             throw;
         }
         finally
         {
             DCmd.Dispose();
             Conn.Close();
             Conn.Dispose();
         }
 }
Esempio n. 26
0
    private void AddNewRecord(string contactid, string name, string phone, string fileName)
    {
        SqlConnection connection = new SqlConnection(GetConnectionString());
        string sqlStatement = string.Empty;

            sqlStatement = "INSERT INTO tblContact" +
                            " VALUES (@contactid,@name,@phone,@image)";

        try
        {
            connection.Open();
            SqlCommand cmd = new SqlCommand(sqlStatement, connection);
            cmd.Parameters.AddWithValue("@contactid", contactid);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@phone", phone);
            cmd.Parameters.AddWithValue("@image", fileName);

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            connection.Close();
        }
    }
Esempio n. 27
0
    public static List<ReporteNotificacionGeocerca> ObtenerNotificacionesGeocerca(List<string> unidades, List<string> geocercas, DateTime fechaInicial, DateTime fechaFinal, int accion, string cliente)
    {
        List<ReporteNotificacionGeocerca> reporteFinal = new List<ReporteNotificacionGeocerca>();
        List<Vehiculo> vehiculos = AutentificacionBD.VehiculosCliente(cliente);
        string query = "SELECT     Unidad, accion, fechahora, geocerca, nombre, id_reportegeocerca " +
                       "FROM         vReporteGeocercaUTC " +
                       "WHERE     (Unidad IN (";
        foreach (string unidad in unidades)
            query += "'" + unidad + "', ";
        //quitar la última coma y espacio
        query = query.Remove(query.Length - 2);
        //agregar los dos paréntesis finales.
        query += ")) AND (fechahora between @fechainicial AND @fechafinal) ";
        if (accion == ReporteNotificacionGeocerca.AccionFuera)
        {
            query += "AND accion = 'Fuera' ";
        }
        else if(accion == ReporteNotificacionGeocerca.AccionDentro)
        {
            query += "AND accion = 'Dentro' ";
        }
        else if (accion == ReporteNotificacionGeocerca.AccionDentroFuera)
        {
            query += " AND (accion = 'Fuera' OR accion = 'Dentro') ";
        }
        query +="ORDER BY fechahora DESC";

        using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionStringBD"].ConnectionString))
        {
            sqlConnection.Open();
            using (SqlCommand sqlCommand = new SqlCommand(query,sqlConnection))
            {
                sqlCommand.Parameters.AddWithValue("@fechainicial", fechaInicial);
                sqlCommand.Parameters.AddWithValue("@fechafinal", fechaFinal);
                Hashtable nombresGeocercas = GeocercasBD.ConsultarGeocercasNombreBD(cliente);
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read()){
                        string geocercaID= (string)reader["geocerca"];
                        if (geocercas.Where(x=>x.Equals(geocercaID)).ToList().Count == 1)
                        {
                            ReporteNotificacionGeocerca reporte = new ReporteNotificacionGeocerca();
                            string sVehiculo = (string)reader["Unidad"];
                            string sAccionBD = (string)reader["accion"];
                            reporte.VehiculoID = sVehiculo;
                            reporte.GeocercaID = geocercaID;
                            reporte.Fecha = (DateTime)reader["fechahora"];
                            reporte.Vehiculo = vehiculos.Find(x=> x.Unidad.Equals(sVehiculo)).Descripcion;
                            //reporte.Accion = sAccionBD == "Dentro"?Resources.Reportes.aspx.Dentro:Resources.Reportes.aspx.Fuera;
                            reporte.iAccion = sAccionBD == "Dentro" ? ReporteNotificacionGeocerca.AccionDentro : ReporteNotificacionGeocerca.AccionFuera;
                            reporte.Geocerca = nombresGeocercas[geocercaID].ToString();
                            reporteFinal.Add(reporte);
                        }
                    }
                }
            }
        }

        return reporteFinal.OrderBy(x => x.Fecha).ToList();
    }
Esempio n. 28
0
    public static string PublishActivity(int ClubId, string ActivityContent)
    {
        // 将新增活动存入数据库,并从数据库返回信息及数据
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["CZConnectionString"].ConnectionString;
        SqlConnection conn = new SqlConnection(connString);
        conn.Open();
        // 存储
        string PublishDate = DateTime.Now.ToString();
        string queryString1 = "Insert Into Activity Values (" + ClubId + ",N'" + ActivityContent + "','" + PublishDate + "')";
        SqlCommand cmd = new SqlCommand(queryString1, conn);
        cmd.ExecuteNonQuery();
        // 查询最后插入的数据,就是新的数据
        string queryString2 = "Select Top 1 * From Activity Where ClubId=" + ClubId + " Order By PublishDate Desc";
        cmd = new SqlCommand(queryString2, conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        int Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"].ToString());
        string Content = ds.Tables[0].Rows[0]["Content"].ToString();
        string Date = ds.Tables[0].Rows[0]["PublishDate"].ToString();

        conn.Close();

        // 通过判断前后时间知是否查入成功
        if (PublishDate == Date)
        {
            return "{status:1,id:" + Id + ",content:'" + Content + "',date:'" + Date + "'}";
        }
        else
        {
            return "{status:-1}";
        }
    }
Esempio n. 29
0
 //  To Change status of 'Group' record of specific GroupId from database by stored procedure
 public int ChangeGroupStatus(int GroupId, int LoggedInUser, string returnmsg, bool IsActive)
 {
     SqlConnection Conn = new SqlConnection(ConnStr);
         Conn.Open();
         //  'uspUpdateGroupStatus' stored procedure is used to Chnage Status of record in Group table
         SqlCommand DCmd = new SqlCommand("uspUpdateGroupStatus", Conn);
         DCmd.CommandType = CommandType.StoredProcedure;
         DCmd.Parameters.AddWithValue("@GroupId", GroupId);
         DCmd.Parameters.AddWithValue("@LoggedInUser", LoggedInUser);
         DCmd.Parameters.AddWithValue("@IsActive", IsActive);
         DCmd.Parameters.AddWithValue("@RetMsg", returnmsg);
         try
         {
             return DCmd.ExecuteNonQuery();
         }
         catch
         {
             throw;
         }
         finally
         {
             DCmd.Dispose();
             Conn.Close();
             Conn.Dispose();
         }
 }
Esempio n. 30
0
 public dapperHelper()
 {
     sqlConn = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
     db      = new SqlConnection(sqlConn);
 }
Esempio n. 31
0
        private void btnDelete_Click(object sender, EventArgs e)
        {
            string err = DataValidate();

            if (!string.IsNullOrEmpty(err))
            {
                MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            GrpMain.Enabled = false;
            Cursor.Current  = Cursors.WaitCursor;
            using (SqlConnection cn = new SqlConnection(Utils.Helper.constr))
            {
                try
                {
                    cn.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }


                SqlTransaction tr = cn.BeginTransaction("DeleteEmp");


                try
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection  = cn;
                        cmd.Transaction = tr;

                        string sql = "insert into MastEmpHistory " +
                                     " select 'Before Delete Master Data, Action By " + Utils.User.GUserID + "',GetDate(),* from MastEmp where CompCode = '" + txtCompCode.Text.Trim() + "' " +
                                     " and EmpUnqID ='" + txtEmpUnqID.Text.Trim() + "' ";

                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from AttdData where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from MastEmpBio where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from LeaveEntry where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from MastEmpFamily  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from MastEmpExp  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from MastEmpEDU  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from MastEmpPPE  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from MastLeaveSchedule  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();


                        cmd.CommandText = "Delete from MastShiftSchedule  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        //cmd.CommandText = "Delete from ATTDLOG  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        //cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from LeaveBal  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Delete from MastEmp  where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();

                        tr.Commit();

                        MessageBox.Show("Record Deleted Sucessfull...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                catch (Exception ex)
                {
                    tr.Rollback();

                    MessageBox.Show(err + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }


            GrpMain.Enabled = true;
            Cursor.Current  = Cursors.Default;
            ResetCtrl();
            SetRights();
        }
Esempio n. 32
0
        public static СправочникиСсылка.Склады НайтиПоКоду(string Код)
        {
            using (var Подключение = new SqlConnection(СтрокаСоединения))
            {
                Подключение.Open();
                using (var Команда = Подключение.CreateCommand())
                {
                    Команда.CommandText = @"Select top 1 
					_IDRRef [Ссылка]
					,_Version [Версия]
					,_Marked [ПометкаУдаления]
					,_IsMetadata [Предопределенный]
					,_ParentIDRRef [Родитель]
					,_Folder [ЭтоГруппа]
					,_Code [Код]
					,_Description [Наименование]
					,_Fld1744 [Комментарий]
					,_Fld1745RRef [ТипЦенРозничнойТорговли]
					,_Fld1746RRef [Подразделение]
					,_Fld1747RRef [ВидСклада]
					,_Fld1748 [НомерСекции]
					,_Fld1749 [РасчетРозничныхЦенПоТорговойНаценке]
					From _Reference147(NOLOCK)
					Where _Code=@Код"                    ;
                    Команда.Parameters.AddWithValue("Код", Код);
                    using (var Читалка = Команда.ExecuteReader())
                    {
                        if (Читалка.Read())
                        {
                            var Ссылка = new СправочникиСсылка.Склады();
                            //ToDo: Читать нужно через GetValues()
                            Ссылка.Ссылка = new Guid((byte[])Читалка.GetValue(0));
                            var ПотокВерсии = ((byte[])Читалка.GetValue(1));
                            Array.Reverse(ПотокВерсии);
                            Ссылка.Версия           = BitConverter.ToInt64(ПотокВерсии, 0);
                            Ссылка.ВерсияДанных     = Convert.ToBase64String(ПотокВерсии);
                            Ссылка.ПометкаУдаления  = ((byte[])Читалка.GetValue(2))[0] == 1;
                            Ссылка.Предопределенный = ((byte[])Читалка.GetValue(3))[0] == 1;
                            Ссылка.одитель          = V82.СправочникиСсылка.Склады.ВзятьИзКэша((byte[])Читалка.GetValue(4));
                            Ссылка.ЭтоГруппа        = ((byte[])Читалка.GetValue(5))[0] == 0;
                            Ссылка.Код          = Читалка.GetString(6);
                            Ссылка.Наименование = Читалка.GetString(7);
                            if (!Ссылка.ЭтоГруппа)
                            {
                                Ссылка.Комментарий             = Читалка.GetString(8);
                                Ссылка.ТипЦенРозничнойТорговли = V82.СправочникиСсылка.ТипыЦенНоменклатуры.ВзятьИзКэша((byte[])Читалка.GetValue(9));
                                Ссылка.Подразделение           = V82.СправочникиСсылка.Подразделения.ВзятьИзКэша((byte[])Читалка.GetValue(10));
                                Ссылка.ВидСклада   = V82.Перечисления /*Ссылка*/.ВидыСкладов.ПустаяСсылка.Получить((byte[])Читалка.GetValue(11));
                                Ссылка.НомерСекции = Читалка.GetDecimal(12);
                                Ссылка.асчетРозничныхЦенПоТорговойНаценке = ((byte[])Читалка.GetValue(13))[0] == 1;
                            }
                            return(Ссылка);
                        }
                        else
                        {
                            return(null);
                        }
                    }
                }
            }
        }
Esempio n. 33
0
 private static void PrepareTransSqlCommand(string ProcedureName, SqlCommand cmd, SqlConnection conn, SqlTransaction trans)
 {
     cmd.Transaction = trans;
     cmd.Connection  = conn;
     cmd.CommandText = ProcedureName;
     cmd.CommandType = CommandType.StoredProcedure;
 }
Esempio n. 34
0
 public SqlBaseRepo(string connectionString)
 {
     _connectionString = connectionString;
     _connection       = new SqlConnection(connectionString);
     TableName         = typeof(T).Name;
 }
Esempio n. 35
0
 public SolutionService()
 {
     _connection = new SqlConnection("Data Source=b9wyaqyyrn.database.windows.net;Initial Catalog=MobileApp;User ID=laxmanrapolu;Password=Lucky_123;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;");
 }
Esempio n. 36
0
 public EmployeeRepository(SqlConnection sqlconn)
 {
     _sqlconn = sqlconn;
 }
        /// <summary>
        /// 快取 LINQ Query 的結果(僅適用於 LINQ to SQL 環境)
        /// 使用的的限制跟使用 SqlCacheDependency 的限制一樣
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="q"></param>
        /// <param name="dc">你的 LINQ to SQL DataContext</param>
        /// <param name="CacheId">Cache ID,需為每一個不同的 IQueryable 物件設定一個唯一的 ID</param>
        /// <returns></returns>
        public static List <T> LinqCache <T>(this IQueryable <T> q, DataContext dc, string CacheId)
        {
            List <T> objCache = (List <T>)System.Web.HttpRuntime.Cache.Get(CacheId);

            if (objCache == null)
            {
                #region 从数据库查找

                ///////// 尚未快取,實做 new SqlCacheDependeny //////////

                // 1. 透過 DataContext 取得連線字串
                string connStr = dc.Connection.ConnectionString;

                // 2. 透過 DataContext 與 IQueryable 物件取得 SqlCommand 物件
                SqlCommand sqlCmd = dc.GetCommand(q) as SqlCommand;

                // 3. 建立要給 SqlCacheDependency 使用的 SqlConnection 物件
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();

                    // 4. 建立要給 SqlCacheDependency 使用的 SqlCommand 物件
                    using (SqlCommand cmd = new SqlCommand(sqlCmd.CommandText, conn))
                    {
                        // 5.0 將 sqlCmd 中的所有參數傳遞給 cmd 物件
                        foreach (System.Data.Common.DbParameter dbp in sqlCmd.Parameters)
                        {
                            cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value));
                        }

                        // 5.1 啟用資料庫的 Query notifications 功能
                        SqlCacheDependencyAdmin.EnableNotifications(connStr);

                        // 5.2 取得要進行異動通知的表格名稱(ElementType)
                        string NotificationTable = q.ElementType.Name;

                        // 5.3 將取得的 NotificationTable 啟用通知功能
                        if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))
                        {
                            SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);
                        }

                        // 6. 建立 SqlCacheDependency物件
                        SqlCacheDependency sqlDep = new SqlCacheDependency(cmd);

                        // 7. 刷新 LINQ to SQL 的值(取得資料庫中的最新資料)
                        dc.Refresh(RefreshMode.OverwriteCurrentValues, q);

                        // 8. 執行 SqlCacheDepency 查詢
                        cmd.ExecuteNonQuery();

                        // 9. 執行 LINQ to SQL 的查詢,並將結果轉成 List<T> 型別,避免延遲查詢(Delayed Query)立即將資料取回
                        objCache = q.ToList();

                        //10. 將結果插入到 System.Web.HttpRuntime.Cache 物件中,並且指定 SqlCacheDependency 物件
                        System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqlDep);
                    }
                }
                #endregion
            }

            // 回傳查詢結果(或快取的結果)
            return(objCache);
        }
Esempio n. 38
0
 public SqlCommand getCommand(string sql, SqlConnection connection)
 {
     return new SqlCommand(sql, connection);
 }
Esempio n. 39
0
 public ManejadorCliente(SqlConnection pConnection, SqlTransaction pTransaction) : base(pConnection, pTransaction)
 {
 }
Esempio n. 40
0
        private void btnAdd_Click(object sender, EventArgs e)
        {
            string err = DataValidate();

            if (!string.IsNullOrEmpty(err))
            {
                MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (mode == "NEW" && dupadhar)
            {
                string msg = "This Adhar No is Already Registered with " + dupadharemp + Environment.NewLine
                             + " Are you sure to Insert this as New Employee ?";

                DialogResult qdr = MessageBox.Show(msg, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (qdr == DialogResult.No)
                {
                    return;
                }
            }

            Cursor.Current  = Cursors.WaitCursor;
            GrpMain.Enabled = false;

            using (SqlConnection cn = new SqlConnection(Utils.Helper.constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        if (string.IsNullOrEmpty(txtBasic.Text.Trim()))
                        {
                            txtBasic.Text = "0";
                        }

                        cn.Open();
                        cmd.Connection = cn;
                        string sql = "Insert into MastEmp (CompCode,WrkGrp,EmpUnqID,EmpName,FatherName," +
                                     " UnitCode,MessCode,MessGrpCode,BirthDt,JoinDt,ValidFrom,ValidTo," +
                                     " ADHARNO,IDPRF3,IDPRF3No,Sex,ContractFlg,PayrollFlg,OTFLG,Weekoff,Active,AddDt,AddID,Basic) Values (" +
                                     "'{0}','{1}','{2}','{3}','{4}' ," +
                                     " '{5}',{6},{7},'{8}','{9}',{10},{11}," +
                                     " '{12}','ADHARCARD','{13}','{14}','{15}','{16}','{17}','{18}','1',GetDate(),'{19}','{20}')";

                        sql = string.Format(sql, txtCompCode.Text.Trim().ToString(), txtWrkGrpCode.Text.Trim().ToString(), txtEmpUnqID.Text.Trim().ToString(), txtEmpName.Text.Trim().ToString(), txtFatherName.Text.Trim(),
                                            txtUnitCode.Text.ToString(), ((txtMessCode.Text.Trim() == "")? "null" :"'" + txtMessCode.Text.Trim() + "'"),
                                            ((txtMessGrpCode.Text.Trim() == "")? "null" :"'" + txtMessGrpCode.Text.Trim() + "'"),
                                            txtBirthDT.DateTime.ToString("yyyy-MM-dd"), txtJoinDt.DateTime.ToString("yyyy-MM-dd"),
                                            ((txtWrkGrpCode.Text.Trim() == "COMP") ? "null" : "'" + txtValidFrom.DateTime.ToString("yyyy-MM-dd") + "'"),
                                            ((txtWrkGrpCode.Text.Trim() == "COMP") ? "null" : "'" + txtValidTo.DateTime.ToString("yyyy-MM-dd") + "'"),
                                            txtAdharNo.Text.Trim(), txtAdharNo.Text.Trim(), ((Convert.ToBoolean(txtGender.EditValue))?1:0),
                                            ((chkCont.Checked)?1:0), ((chkComp.Checked)?1:0), ((chkOTFlg.Checked)?1:0), txtWeekOff.Text.Trim(),
                                            Utils.User.GUserID, txtBasic.Text.Trim());

                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();



                        //createmuster
                        clsEmp t    = new clsEmp();
                        string err2 = string.Empty;
                        if (t.GetEmpDetails(txtCompCode.Text.Trim(), txtEmpUnqID.Text.Trim()))
                        {
                            DateTime sFromDt, sToDt, sCurDt;
                            sCurDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select GetDate()", Utils.Helper.constr));
                            if (txtJoinDt.DateTime.Year < sCurDt.Year)
                            {
                                sFromDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarStartOfYearDate from dbo.F_TABLE_DATE(GetDate(),GetDate())", Utils.Helper.constr));
                                sToDt   = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarEndOfYearDate from dbo.F_TABLE_DATE(GetDate(),GetDate())", Utils.Helper.constr));
                            }
                            else
                            {
                                sFromDt = txtJoinDt.DateTime;
                                sToDt   = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarEndOfYearDate from dbo.F_TABLE_DATE('" + sFromDt.ToString("yyyy-MM-dd") + "','" + sFromDt.ToString("yyyy-MM-dd") + "')", Utils.Helper.constr));
                            }


                            if (!t.CreateMuster(sFromDt, sToDt, out err2))
                            {
                                MessageBox.Show(err, "Error While Creating Muster Table", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                        }
                        if (string.IsNullOrEmpty(err2))
                        {
                            MessageBox.Show("Record saved...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                        else
                        {
                            MessageBox.Show("Record saved with error please check muster table created...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }

                        ResetCtrl();
                    }catch (Exception ex) {
                        MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
            GrpMain.Enabled = true;

            Cursor.Current = Cursors.Default;
        }
Esempio n. 41
0
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string err          = DataValidate();
            bool   WrkGrpChange = false;

            if (!string.IsNullOrEmpty(err))
            {
                MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (dupadhar)
            {
                string msg = "This Adhar No is Already Registered with " + dupadharemp + Environment.NewLine
                             + " Are you sure to Update this Adhar No ?";

                DialogResult qdr = MessageBox.Show(msg, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (qdr == DialogResult.No)
                {
                    return;
                }
            }


            GrpMain.Enabled = false;

            Cursor.Current = Cursors.WaitCursor;

            using (SqlConnection cn = new SqlConnection(Utils.Helper.constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        cn.Open();
                        cmd.Connection = cn;

                        string sql = "insert into MastEmpHistory " +
                                     " select 'Before Update Master Data, Action By " + Utils.User.GUserID + "', GetDate(), * from MastEmp where CompCode = '" + txtCompCode.Text.Trim() + "' " +
                                     " and EmpUnqID ='" + txtEmpUnqID.Text.Trim() + "'";

                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();


                        clsEmp t = new clsEmp();
                        t.CompCode = txtCompCode.Text.Trim();
                        t.EmpUnqID = txtEmpUnqID.Text.Trim();
                        t.GetEmpDetails(t.CompCode, t.EmpUnqID);

                        //WrkGrp is Changed.. need to process in all tables..
                        if (t.WrkGrp != txtWrkGrpCode.Text.Trim())
                        {
                            try
                            {
                                sql             = "Exec ChangeWrkGrp '" + t.EmpUnqID + "','" + txtWrkGrpCode.Text.Trim() + "';";
                                cmd.CommandText = sql;
                                cmd.ExecuteNonQuery();
                                WrkGrpChange = true;
                            }
                            catch (Exception ex)
                            {
                                WrkGrpChange    = false;
                                GrpMain.Enabled = true;
                                Cursor.Current  = Cursors.Default;
                                MessageBox.Show("Kindly Clear the Job Profile First,(EmpTypeCode,CatCode,GradeCode,DesgCode,DeptCode,StatCode)" + Environment.NewLine +
                                                "and try again..."
                                                , "WrkGrp Change Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                                return;
                            }
                        }
                        else
                        {
                            WrkGrpChange = false;
                        }

                        if (string.IsNullOrEmpty(txtBasic.Text.Trim()))
                        {
                            txtBasic.Text = "0";
                        }

                        sql = "Update MastEmp set WrkGrp ='{0}',EmpName='{1}',FatherName = '{2}'," +
                              " UnitCode = '{3}',MessCode={4},MessGrpCode = {5},BirthDt ='{6}',JoinDt ='{7}',ValidFrom = {8},ValidTo = {9}," +
                              " ADHARNO = '{10}',IDPRF3No = '{11}',Sex='{12}',ContractFlg='{13}',PayrollFlg='{14}',OTFLG='{15}',Weekoff='{16}',UpdDt=GetDate(),UpdID ='{17}', Basic='{18}' Where " +
                              " CompCode ='{19}' and EmpUnqID = '{20}'";


                        sql = string.Format(sql, txtWrkGrpCode.Text.Trim().ToString(), txtEmpName.Text.Trim().ToString(), txtFatherName.Text.Trim(),
                                            txtUnitCode.Text.ToString(), ((txtMessCode.Text.Trim() == "") ? "null" : "'" + txtMessCode.Text.Trim() + "'"),
                                            ((txtMessGrpCode.Text.Trim() == "") ? "null" : "'" + txtMessGrpCode.Text.Trim() + "'"),
                                            txtBirthDT.DateTime.ToString("yyyy-MM-dd"), txtJoinDt.DateTime.ToString("yyyy-MM-dd"),
                                            ((txtWrkGrpCode.Text.Trim() == "COMP")? "null" :"'" + txtValidFrom.DateTime.ToString("yyyy-MM-dd") + "'"),
                                            ((txtWrkGrpCode.Text.Trim() == "COMP")? "null" :"'" + txtValidTo.DateTime.ToString("yyyy-MM-dd") + "'"),
                                            txtAdharNo.Text.Trim(), txtAdharNo.Text.Trim(), ((Convert.ToBoolean(txtGender.EditValue))?1:0),
                                            ((chkCont.Checked) ? 1 : 0), ((chkComp.Checked) ? 1 : 0), ((chkOTFlg.Checked) ? 1 : 0), txtWeekOff.Text.Trim(),
                                            Utils.User.GUserID,
                                            txtBasic.Text.Trim(),
                                            txtCompCode.Text.Trim(), txtEmpUnqID.Text.Trim()

                                            );

                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();


                        if (WrkGrpChange)
                        {
                            MessageBox.Show("Employee Job Profile is Discarded.." + Environment.NewLine +
                                            "Please Fill the Employee Job Profile Again.."
                                            , "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }

                        MessageBox.Show("Record Updated...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ResetCtrl();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
            GrpMain.Enabled = true;

            Cursor.Current = Cursors.Default;
        }
Esempio n. 42
0
 public Cls_SQL()
 {
     //
     // TODO: 在此加入建構函式的程式碼
     conn = sqlConnection();
 }
Esempio n. 43
0
        protected void Button1_Click(object sender, EventArgs e)
        {
            int      courseID, assignmentNumber, fullGrade;
            decimal  weight;
            DateTime deadline;

            try
            {
                courseID         = Int16.Parse(TextBox1.Text);
                assignmentNumber = Int16.Parse(TextBox2.Text);
                fullGrade        = Int16.Parse(TextBox4.Text);
                weight           = decimal.Parse(TextBox5.Text);
            }
            catch
            {
                Label1.Text = "Please enter a number in course ID, Assignment number, full grade, and weight";
                return;
            }

            try
            {
                deadline = Convert.ToDateTime(TextBox6.Text);
            }
            catch
            {
                Label1.Text = "Enter a valid date (DD/MM/YYYY)";
                return;
            }



            string content = TextBox7.Text;
            string type    = TextBox3.Text;


            string        connStr = WebConfigurationManager.ConnectionStrings["GUCera"].ToString();
            SqlConnection conn    = new SqlConnection(connStr);

            SqlCommand addAssignment = new SqlCommand("DefineAssignmentOfCourseOfCertianType", conn);

            addAssignment.CommandType = CommandType.StoredProcedure;

            addAssignment.Parameters.Add(new SqlParameter("@cid", courseID));
            addAssignment.Parameters.Add(new SqlParameter("@number", assignmentNumber));
            addAssignment.Parameters.Add(new SqlParameter("@type", type));
            addAssignment.Parameters.Add(new SqlParameter("@fullGrade", fullGrade));
            addAssignment.Parameters.Add(new SqlParameter("@weight", weight));
            addAssignment.Parameters.Add(new SqlParameter("@deadline", deadline));
            addAssignment.Parameters.Add(new SqlParameter("@content", content));
            addAssignment.Parameters.Add(new SqlParameter("@instId", Session["userId"]));

            conn.Open();

            try
            {
                addAssignment.ExecuteNonQuery();
                Label1.Text = "Success";
            }
            catch
            {
                Label1.Text = "Error";
            }



            conn.Close();
        }
Esempio n. 44
0
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["UserId"] == null)
        {
            Response.Redirect("login.aspx");
        }
        if (Session["SESSION_USERTYPE"].ToString() != ((int)Constant.USERTYPE.VMOFFICER).ToString())
        {
            Response.Redirect("login.aspx");
        }

        if (IsPostBack)
        {
            if (Request["__EVENTTARGET"].ToString() == "Details" && Request["__EVENTARGUMENT"].ToString() != "")
            {
                VendorIdstr = Request["__EVENTARGUMENT"].ToString().Trim();
                string   sArg             = VendorIdstr.Trim();
                char[]   mySeparator      = new char[] { '|' };
                string[] Arr              = sArg.Split(mySeparator);
                string   VendorAlias      = "";
                int      SendToSAP_Status = 0;

                query = "SELECT CompanyName FROM tblVendor WHERE VendorId=@VendorId";
                using (conn = new SqlConnection(connstring))
                {
                    using (cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(Arr[0].ToString()));
                        conn.Open();
                        oReader = cmd.ExecuteReader();
                        if (oReader.HasRows)
                        {
                            while (oReader.Read())
                            {
                                VendorAlias = oReader["CompanyName"].ToString().Replace(" ", "").Substring(0, 4).ToLower();
                            }
                            SendMail(Arr[0].ToString(), Arr[1].ToString());
                            if (Arr[1].ToString() == "6")
                            {
                                SendToSAP_Status = 1;
                            }
                            query = @"UPDATE tblVendor SET NotificationSent=NULL, SendToSAP_Status=NULL, Status=0, 
                            IsAuthenticated = NULL, AuthenticationTicket = NULL,
                            approvedbyDnb = NULL, approvedbyDnbDate=NULL,
                            approvedbyVMOfficer = NULL, approvedbyVMOfficerDate = NULL,
                            approvedbyVMReco = NULL, approvedbyVMRecoDate = NULL, 
                            approvedbyFAALogistics = NULL, approvedbyFAALogisticsDate = NULL,
                            approvedbyFAAFinance = NULL, approvedbyFAAFinanceDate = NULL
                            WHERE VendorId=@VendorId";
                            using (conn = new SqlConnection(connstring))
                            {
                                using (cmd = new SqlCommand(query, conn))
                                {
                                    cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(Arr[0].ToString()));
                                    conn.Open(); cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }



                GridView1.DataBind();
            }
        }
    }
Esempio n. 45
0
 public DataProvider()
 {
     connection = new SqlConnection(connString);
 }
Esempio n. 46
0
 public UserRepository(SqlConnection connection)
 {
     _connection = connection;
 }
Esempio n. 47
0
    private string CreateNotificationBody(string cfromName, string ctoName, string cAuthenticationNumber, string cVendorName, string VendorIdx)
    {
        SqlDataReader oReader;
        string        connstring = ConfigurationManager.ConnectionStrings["AVAConnectionString"].ConnectionString;
        string        cCeo = "", cCeoEmail = "", cAddress = "", cServices = "", cAccreDuration = "";

        query = "SELECT * FROM tblVendorInformation WHERE VendorId = @VendorId";
        using (conn = new SqlConnection(connstring))
        {
            using (cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(VendorIdx));
                conn.Open();
                //Process results
                oReader = cmd.ExecuteReader();
                if (oReader.HasRows)
                {
                    while (oReader.Read())
                    {
                        cCeo      = oReader["conBidName"].ToString();
                        cCeoEmail = oReader["conBidEmail"].ToString();
                        cAddress  = oReader["regBldgCode"].ToString() != "" ? cAddress + "Bldg. " + oReader["regBldgCode"].ToString() + ", " : cAddress + "";
                        cAddress  = oReader["regBldgRoom"].ToString() != "" ? cAddress + "Rm. " + oReader["regBldgRoom"].ToString() + ", " : cAddress + "";
                        cAddress  = oReader["regBldgFloor"].ToString() != "" ? cAddress + oReader["regBldgFloor"].ToString() + " Fr, " : cAddress + "";
                        cAddress  = oReader["regBldgHouseNo"].ToString() != "" ? cAddress + "No. " + oReader["regBldgHouseNo"].ToString() + " " : cAddress + "";
                        cAddress  = oReader["regStreetName"].ToString() != "" ? cAddress + oReader["regStreetName"].ToString() + ", " : cAddress + "";
                        cAddress  = cAddress + "<br>";
                        cAddress  = oReader["regCity"].ToString() != "" ? cAddress + oReader["regCity"].ToString() + ", " : cAddress + "";
                        cAddress  = oReader["regProvince"].ToString() != "" ? cAddress + oReader["regProvince"].ToString() + ", " : cAddress + "";
                        cAddress  = cAddress + "<br>";
                        cAddress  = oReader["regCountry"].ToString() != "" ? cAddress + oReader["regCountry"].ToString() + ", " : cAddress + "";
                        cAddress  = oReader["regPostal"].ToString() != "" ? cAddress + oReader["regPostal"].ToString() + " " : cAddress + "";
                    }
                }
            }
        }
        query = "SELECT t1.*, t2.CategoryName, t3.SubCategoryName FROM tblVendorProductsAndServices t1, rfcProductCategory t2, rfcProductSubcategory t3 WHERE t2.CategoryId = t1.CategoryId AND t3.SubCategoryId = t1.SubCategoryId AND t1.VendorId = @VendorId";
        using (conn = new SqlConnection(connstring))
        {
            using (cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(VendorIdx));
                conn.Open();
                //Process results
                oReader = cmd.ExecuteReader();
                if (oReader.HasRows)
                {
                    while (oReader.Read())
                    {
                        cServices = cServices + "&bull; " + oReader["CategoryName"].ToString() + " - " + oReader["SubCategoryName"].ToString() + "<br>";
                    }
                }
            }
        }

        query = "SELECT * FROM tblVendorApprovalbyVmReco  WHERE VendorId = @VendorId";
        using (conn = new SqlConnection(connstring))
        {
            using (cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(VendorIdx));
                conn.Open();
                //Process results
                oReader = cmd.ExecuteReader();
                if (oReader.HasRows)
                {
                    while (oReader.Read())
                    {
                        cAccreDuration = oReader["AccreDuration"].ToString();
                    }
                }
            }
        }

        StringBuilder sb   = new StringBuilder();
        string        sTxt = "<table border='1' style='font-size:12px'>";

        sTxt = sTxt + "<tr>";
        sTxt = sTxt + "<td><strong>&nbsp;Vendor ID</strong></td>";
        sTxt = sTxt + "<td>&nbsp;" + VendorIdx + "&nbsp;</td>";
        sTxt = sTxt + "</tr>";
        sTxt = sTxt + "<tr>";
        sTxt = sTxt + "<td><strong>&nbsp;Company Name</strong></td>";
        sTxt = sTxt + "<td>&nbsp;" + cVendorName + "&nbsp;</td>";
        sTxt = sTxt + "</tr>";
        sTxt = sTxt + "</table>";

        sb.Append("<tr><td>");
        sb.Append("<p>");
        sb.Append("Date: " + DateTime.Now.ToLongDateString() + "<br><br>");
        sb.Append(cCeo + "<br>");
        sb.Append("<b>" + cVendorName + "</b><br>");
        sb.Append(cAddress + "<br><br>");
        sb.Append("</p>");
        sb.Append("<tr><td>");
        sb.Append("<p>");
        sb.Append("Dear " + cCeo + ":<br><br>");
        sb.Append("Please consider this email as reminders to renew/update your accreditation status with Trans-Asia.<br><br>");
        sb.Append("Looking forward for your compliance on this request before your accreditation expired. Failure to comply will temporarily suspend your company to participate on any upcoming bid events.<br><br>");

        sb.Append("We encourage you to renew your accreditation and continue to be a business partner of Trans-Asia.<br><br>");
        sb.Append("Hope to receive positive response from your end.<br><br>");
        sb.Append("Please get in touch with Marife Pablo of Vendor Management for any clarifications.<br><br>");
        sb.Append(sTxt);
        sb.Append("</p>");
        sb.Append("<br><br><br>");
        sb.Append("Sincerely,<br><br>");
        sb.Append("Trans-Asia<br><br>");
        sb.Append("</td></tr>");
        sb.Append("<tr><td>");
        sb.Append("<p>&nbsp;</p><span style='font-size:10px; font-style:italic;'>Please do not reply to this auto-generated  message.&nbsp;</span>");
        sb.Append("</td></tr>");
        //Response.Write(sb.ToString());
        return(MailTemplate.IntegrateBodyIntoTemplate(sb.ToString()));
    }
        protected void BUpdate_Click(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                string sql = null;
                char gen;

                if (rbMale.Checked == true)
                {
                    gen = 'M';
                }
                else if (rbFemale.Checked == true)
                {
                    gen = 'F';
                }
                else
                {
                    gen = default(char);
                }

                using (SqlConnection cnn = new SqlConnection("Data Source = JARVIS; Initial Catalog = HousingMSdb; User ID = sa; Password = 2411"))
                {
                    if (fuImage.HasFile == true)
                    {
                        sql = "UPDATE Users SET [RCNumber]=@RCNumber, [Bdate]=@Bdate, [Gender]=@Gender, [Email]=@Email, [Mobile]=@Mobile, [AMobile]=@AMobile, [Telephone]=@Telephone, [Username]=@Username, [Image]=@Image WHERE Username=@Username";
                    }
                    else
                        sql = "UPDATE Users SET [RCNumber]=@RCNumber, [Bdate]=@Bdate, [Gender]=@Gender, [Email]=@Email, [Mobile]=@Mobile, [AMobile]=@AMobile, [Telephone]=@Telephone, [Username]=@Username WHERE Username=@Username";
                    try
                    {
                        using (SqlCommand cmd = new SqlCommand(sql, cnn))
                        {

                            var cardnumber = cmd.Parameters.AddWithValue("@RCNumber", tbCardNumber.Text);
                            cardnumber.SqlDbType = SqlDbType.NVarChar;

                            var BDate = cmd.Parameters.AddWithValue("@Bdate", Convert.ToDateTime(tbDOB.Text));
                            BDate.SqlDbType = SqlDbType.DateTime;

                            if (gen != default(char))
                                cmd.Parameters.AddWithValue("@Gender", gen);
                            else
                                cmd.Parameters.AddWithValue("@Gender", DBNull.Value);

                            var email = cmd.Parameters.AddWithValue("@Email", tbEmail.Text);
                            email.SqlDbType = SqlDbType.NVarChar;

                            var mobile = cmd.Parameters.AddWithValue("@Mobile", tbMobile.Text);
                            mobile.SqlDbType = SqlDbType.NVarChar;

                            var AMobile = cmd.Parameters.AddWithValue("@AMobile", tbAlternateMobile.Text);
                            AMobile.SqlDbType = SqlDbType.NVarChar;

                            var telephone = cmd.Parameters.AddWithValue("@Telephone", tbTelephone.Text);
                            telephone.SqlDbType = SqlDbType.NVarChar;

                            var username = cmd.Parameters.AddWithValue("@Username", tbUsername.Text);
                            username.SqlDbType = SqlDbType.NVarChar;

                            if (fuImage.HasFile == true)
                            {
                                int length = fuImage.PostedFile.ContentLength;
                                byte[] displaypicture = new byte[length];
                                fuImage.PostedFile.InputStream.Read(displaypicture, 0, length);
                                cmd.Parameters.AddWithValue("@Image", displaypicture);
                            }

                            cnn.Open();
                            cmd.ExecuteNonQuery();
                            if (cmd.ExecuteNonQuery() == 1)
                            {
                                Notification(cnn, UID);
                            }
                                
                        }
                    }
                    catch (System.Data.SqlClient.SqlException sqlException)
                    {
                        System.Windows.Forms.MessageBox.Show(sqlException.Message);
                    }
                    finally
                    {
                        cnn.Close();
                        cnn.Dispose();
                    }
                }

            }
        }
        public DbResponse<int> Inserir(Funcionario func)
        {
            int idInserida = -1;

            string connectionString = Parametros.GetConnectionString();

            SqlConnection connection = new SqlConnection(connectionString);

            SqlCommand command = new SqlCommand();
            command.CommandText = @"INSERT INTO FUNCIONARIOS (NOME, CPF, RG, ENDERECO, TELEFONE, EMAIL, SENHA, EHADMIN, EHATIVO) VALUES
                                  (@NOME, @CPF, @RG, @ENDERECO, @TELEFONE, @EMAIL, @SENHA, @EHADMIN, @EHATIVO); select scope_identity()";
            

            command.Parameters.AddWithValue("@NOME", func.Nome);
            command.Parameters.AddWithValue("@CPF", func.CPF);
            command.Parameters.AddWithValue("@RG", func.RG);
            command.Parameters.AddWithValue("@ENDERECO", func.Endereco);
            command.Parameters.AddWithValue("@TELEFONE", func.Telefone);
            command.Parameters.AddWithValue("@EMAIL", func.Email);
            command.Parameters.AddWithValue("@SENHA", func.Senha);
            command.Parameters.AddWithValue("@EHADMIN", func.EhAdmin);
            command.Parameters.AddWithValue("@EHATIVO", func.EhAtivo);
        
            command.Connection = connection;

            try 
	        {	        
		        connection.Open();
                idInserida = Convert.ToInt32(command.ExecuteNonQuery());

            }
            catch (Exception EX)
	        {
                if (EX.Message.Contains("UNIQUE") || EX.Message.Contains("FK"))
                {
                    return new DbResponse<int>
                    {
                        Sucesso = false,
                        Mensagem = "Funcionario já cadastrado.",
                        Excessao = EX

                    };
                }
                return new DbResponse<int>
                {

                    Sucesso = false,
                    Mensagem = "Erro no Bando de Dados, favor contactar o suporte",
                    Excessao = EX
                };
	        }
            finally
            {
                connection.Dispose();
            }
            return new DbResponse<int>
            {
                Sucesso = true,
                Mensagem = "Funcionário cadastrado com sucesso",
                Dados = idInserida
            };
        }
        public void BasicIncrementalBackupScenario2()
        {
            var dbName             = "basic_scenario_two";
            var backupFile         = "";
            var testConfigFileName = tmpFolder + "BasicScenario2.ini";

            var parser = new FileIniDataParser();
            var config = parser.ReadFile(defaultConfigIni);

            config["sqlserver"].SetKeyData(new KeyData("database")
            {
                Value = dbName
            });

            parser.WriteFile(testConfigFileName, config);

            var conn = new SqlConnection(string.Format("Server={0};Database=master;Trusted_Connection=True;", config["sqlserver"]["server"]));

            conn.Open();

            var logBackups = new List <string>();

            try
            {
                // Create the database and insert some data
                conn.Execute("create database " + dbName);
                conn.Execute("alter database " + dbName + " set recovery full");
                conn.ChangeDatabase(dbName);
                conn.Execute("create table test1 (test1 varchar(50))");
                conn.Execute("insert into test1 values ('data_full')");

                // Backup the database
                var backupManager = new BackupManager();
                var backupResult  = backupManager.Backup(new string[2] {
                    "full", testConfigFileName
                });

                Assert.AreEqual(0, backupResult.ReturnCode);
                Assert.IsFalse(File.Exists(backupResult.BackupName));

                // Do incremental stuff
                for (int i = 1; i <= 3; i++)
                {
                    // The minimum increment is a second between log backups
                    System.Threading.Thread.Sleep(1000);
                    conn.Execute(string.Format("insert into test1 values ('data_log_{0}')", i));

                    var backupLogResult = backupManager.Backup(new string[2] {
                        "incremental", testConfigFileName
                    });

                    Assert.AreEqual(0, backupLogResult.ReturnCode);
                    Assert.IsFalse(File.Exists(backupLogResult.BackupName));

                    logBackups.Add(backupLogResult.BackupName);
                }

                // Drop the database
                conn.ChangeDatabase("master");
                conn.Execute("drop database " + dbName);

                // Restore the backup using the restore manager
                var restoreMan = new RestoreManager();

                var argList = new List <string>()
                {
                    testConfigFileName, backupResult.BackupName
                };

                argList.AddRange(logBackups);

                restoreMan.Restore(argList.ToArray());

                // Verify that the restore worked
                conn.ChangeDatabase(dbName);
                var result = conn.Query("select test1 from test1 where test1 in ('data_full', 'data_log_1', 'data_log_2', 'data_log_3')");

                Assert.IsTrue(result.Count() == 4);
            }
            finally
            {
                // Cleanup our mess
                // S3
                var awsProfile = config["aws"]["profile"];
                Amazon.Util.ProfileManager.RegisterProfile(awsProfile, config["aws"]["access_key"], config["aws"]["secret_key"]);
                var creds     = Amazon.Util.ProfileManager.GetAWSCredentials(awsProfile);
                var awsClient = new AmazonS3Client(creds, Amazon.RegionEndpoint.USEast1);
                var listReq   = new ListObjectsRequest()
                {
                    BucketName = config["aws"]["bucket"]
                };

                var objects = awsClient.ListObjects(listReq);

                foreach (var obj in objects.S3Objects)
                {
                    if (obj.Key.IndexOf(dbName) != -1)
                    {
                        var delReq = new DeleteObjectRequest()
                        {
                            BucketName = config["aws"]["bucket"], Key = obj.Key
                        };

                        awsClient.DeleteObject(delReq);
                    }
                }

                // Testing database server
                conn.ChangeDatabase("master");
                conn.Execute("drop database " + dbName);

                if (File.Exists(backupFile))
                {
                    File.Delete(backupFile);
                }
            }
        }
Esempio n. 51
0
    protected void Button1_Click(object sender, EventArgs e)
    {
        string strcon = @"Server=LAPTOP-68A09JRH ;Initial Catalog=Agriculture; Integrated Security=true;";
        SqlConnection con = new SqlConnection(strcon);
        con.Open();
        SqlCommand sqlCmd = new SqlCommand("Login", con);
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.Parameters.AddWithValue("@Email", TextBox1.Text.Trim());
        sqlCmd.Parameters.AddWithValue("@Password", TextBox2.Text.Trim());

        sqlCmd.ExecuteNonQuery();
        SqlDataReader rd = sqlCmd.ExecuteReader();
        if (rd.HasRows)
        {
            rd.Close();

            if (gender.SelectedItem.Value == "Buyer")
            {

                SqlCommand sqlCmd1 = new SqlCommand("getUserid", con);
                sqlCmd1.CommandType = CommandType.StoredProcedure;
                sqlCmd1.Parameters.AddWithValue("@Email", TextBox1.Text.Trim());
                sqlCmd1.Parameters.AddWithValue("@Type", "Buyer");
                sqlCmd1.ExecuteNonQuery();

                SqlDataReader rd1 = sqlCmd1.ExecuteReader();
                if (rd1.HasRows)
                {
                    rd1.Read();

                    Application["name1"] = rd1["UserId"].ToString();
                    Response.Redirect("Buyer.aspx");
                }
                else
                {
                    Label4.Text = "Email does not exists as a buyer";
                    Label4.Visible = true;
                    HyperLink2.Visible = true;
                }
            }



            else if (gender.SelectedItem.Value == "Seller")
            {

                SqlCommand sqlCmd1 = new SqlCommand("getUserid", con);
                sqlCmd1.CommandType = CommandType.StoredProcedure;
                sqlCmd1.Parameters.AddWithValue("@Email", TextBox1.Text.Trim());
                sqlCmd1.Parameters.AddWithValue("@Type", "Seller");
                sqlCmd1.ExecuteNonQuery();

                SqlDataReader rd1 = sqlCmd1.ExecuteReader();
                if (rd1.HasRows)
                {
                    rd1.Read();

                    Application["name1"] = rd1["UserId"].ToString();
                    Response.Redirect("Seller.aspx");
                }
                else
                {
                    Label4.Text = "Email does not exists as a seller";
                    Label4.Visible = true;
                    HyperLink2.Visible = true;
                }
            }
            else
            {
                Label4.Text = "Error";
                Label4.Visible = true;
            }
        }      

        
        else
        {
            // Label3.Visible = true;
            Label4.Text = "Invalid username or password.";
            Label4.Visible = true;
        }

    }
Esempio n. 52
0
 public Personnel()
 {
     this.sqlConn = DBConnection.JSqlDBConn;
 }
Esempio n. 53
0
 public ExecuteQuery(string configNameOfConnectionString = "dblocal")
 {
     _connection = new SqlConnection();
     _connection.ConnectionString = ConfigurationManager.ConnectionStrings[configNameOfConnectionString].ConnectionString;
     _da = new SqlDataAdapter();
 }
Esempio n. 54
0
 private static void PrepareProcedureCommand(string ProcedureName, SqlCommand salesCommand, SqlConnection conn)
 {
     salesCommand.Connection  = conn;
     salesCommand.CommandText = ProcedureName;
     salesCommand.CommandType = CommandType.StoredProcedure;
     conn.Open();
 }
Esempio n. 55
0
        public static СправочникиВыборка.Склады ИерархияВыбратьПоСсылке(Guid одитель, int ежим, int Первые, Guid Мин, Guid Макс)
        {
            using (var Подключение = new SqlConnection(СтрокаСоединения))
            {
                Подключение.Open();
                using (var Команда = Подключение.CreateCommand())
                {
                    Команда.CommandText = string.Format(@"Select top {0} 
					_IDRRef [Ссылка]
					,_Version [Версия]
					,_Marked [ПометкаУдаления]
					,_IsMetadata [Предопределенный]
					,_ParentIDRRef [Родитель]
					,_Folder [ЭтоГруппа]
					,_Code [Код]
					,_Description [Наименование]
					,_Fld1744 [Комментарий]
					,_Fld1745RRef [ТипЦенРозничнойТорговли]
					,_Fld1746RRef [Подразделение]
					,_Fld1747RRef [ВидСклада]
					,_Fld1748 [НомерСекции]
					,_Fld1749 [РасчетРозничныхЦенПоТорговойНаценке]
					From _Reference147(NOLOCK)
					Where _IDRRef between @Мин and @Макс  -- and _Folder = 0x01 
					AND _ParentIDRRef = @Родитель
					Order by _IDRRef"                    , Первые);
                    Команда.Parameters.AddWithValue("Родитель", одитель);
                    Команда.Parameters.AddWithValue("Мин", Мин);
                    Команда.Parameters.AddWithValue("Макс", Макс);
                    var Выборка = new V82.СправочникиВыборка.Склады();
                    using (var Читалка = Команда.ExecuteReader())
                    {
                        while (Читалка.Read())
                        {
                            var Ссылка = new СправочникиСсылка.Склады();
                            //ToDo: Читать нужно через GetValues()
                            Ссылка.Ссылка = new Guid((byte[])Читалка.GetValue(0));
                            var ПотокВерсии = ((byte[])Читалка.GetValue(1));
                            Array.Reverse(ПотокВерсии);
                            Ссылка.Версия           = BitConverter.ToInt64(ПотокВерсии, 0);
                            Ссылка.ВерсияДанных     = Convert.ToBase64String(ПотокВерсии);
                            Ссылка.ПометкаУдаления  = ((byte[])Читалка.GetValue(2))[0] == 1;
                            Ссылка.Предопределенный = ((byte[])Читалка.GetValue(3))[0] == 1;
                            Ссылка.одитель          = V82.СправочникиСсылка.Склады.ВзятьИзКэша((byte[])Читалка.GetValue(4));
                            Ссылка.ЭтоГруппа        = ((byte[])Читалка.GetValue(5))[0] == 0;
                            Ссылка.Код          = Читалка.GetString(6);
                            Ссылка.Наименование = Читалка.GetString(7);
                            if (!Ссылка.ЭтоГруппа)
                            {
                                Ссылка.Комментарий             = Читалка.GetString(8);
                                Ссылка.ТипЦенРозничнойТорговли = V82.СправочникиСсылка.ТипыЦенНоменклатуры.ВзятьИзКэша((byte[])Читалка.GetValue(9));
                                Ссылка.Подразделение           = V82.СправочникиСсылка.Подразделения.ВзятьИзКэша((byte[])Читалка.GetValue(10));
                                Ссылка.ВидСклада   = V82.Перечисления /*Ссылка*/.ВидыСкладов.ПустаяСсылка.Получить((byte[])Читалка.GetValue(11));
                                Ссылка.НомерСекции = Читалка.GetDecimal(12);
                                Ссылка.асчетРозничныхЦенПоТорговойНаценке = ((byte[])Читалка.GetValue(13))[0] == 1;
                            }
                            Выборка.Add(Ссылка);
                        }
                        return(Выборка);
                    }
                }
            }
        }
Esempio n. 56
0
        private void btn_Dangnhap_Click(object sender, EventArgs e)
        {

            selectedCNValue = (int)cbo_Chinhanh.SelectedValue;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand("spDangnhap", connection))
                {

                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter email = new SqlParameter("@email", SqlDbType.VarChar);
                    email.Direction = ParameterDirection.Input;
                    email.Value = txt_email.Text;
                    cmd.Parameters.Add(email);
                    SqlParameter matkhau = new SqlParameter("@matkhau", SqlDbType.VarChar);
                    matkhau.Direction = ParameterDirection.Input;
                    matkhau.Value = txt_matkhau.Text;
                    cmd.Parameters.Add(matkhau);
                    cmd.Parameters.Add(new SqlParameter("@macuahang", selectedCNValue));

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {

                        while (reader.Read())
                        {

                            if (reader.IsDBNull(reader.GetOrdinal("MaCuaHang")))
                            {
                                Thongtindangnhap.MaCH = 0;
                                
                            }
                            else
                            {
                                Thongtindangnhap.MaCH = (int)reader["MaCuaHang"];
                            }
                            
                            Thongtindangnhap.TenNV = reader["Ten"].ToString();
                            Thongtindangnhap.role = (int)reader["ChucVu"];
                            Thongtindangnhap.MaNV=(int)reader["MaNV"];

                        }
                        if (Thongtindangnhap.TenNV == null)
                        {
                            MessageBox.Show("Đăng nhập thất bại");
                        }
                        else {
                            DangNhapNV.Thongtindangnhap.dnnv = true;
                            Form chucnang = new HuongvietChucNang();
                            chucnang.Show();
                            
                        }



                    }

                }
            }

        }
Esempio n. 57
0
 public ExecuteQuery()
 {
     _connection = new SqlConnection();
     _connection.ConnectionString = ConfigurationManager.ConnectionStrings["dblocal"].ConnectionString;
     _da = new SqlDataAdapter();
 }
Esempio n. 58
0
 /// <summary>
 /// 用于执行数据库transcation,需要把相关的connection传入
 /// </summary>
 public static void TransExecuteNonQueryReturnPKID(string ProcedureName, SqlCommand cmd, SqlConnection conn, SqlTransaction trans, out int PKID)
 {
     PrepareTransSqlCommand(ProcedureName, cmd, conn, trans);
     cmd.ExecuteNonQuery();
     PKID = Convert.ToInt32(cmd.Parameters["@PKID"].Value);
     cmd.Parameters.Clear();
 }
        public void BasicFullBackupScenario1()
        {
            var dbName             = "basic_scenario_one";
            var backupFile         = "";
            var testConfigFileName = tmpFolder + "BasicScenario1.ini";

            var parser = new FileIniDataParser();
            var config = parser.ReadFile(defaultConfigIni);

            config["sqlserver"].SetKeyData(new KeyData("database")
            {
                Value = dbName
            });

            parser.WriteFile(testConfigFileName, config);

            var conn = new SqlConnection(string.Format("Server={0};Database=master;Trusted_Connection=True;", config["sqlserver"]["server"]));

            conn.Open();

            try
            {
                // Create the database and insert some data
                conn.Execute("create database " + dbName);
                conn.ChangeDatabase(dbName);
                conn.Execute("create table test1 (test1 varchar(50))");
                conn.Execute("insert into test1 values ('data')");

                // Backup the database
                var backupManager = new BackupManager();
                var backupResult  = backupManager.Backup(new string[2] {
                    "full", testConfigFileName
                });

                Assert.AreEqual(0, backupResult.ReturnCode);
                Assert.IsFalse(File.Exists(backupFile));

                // Drop the database
                conn.ChangeDatabase("master");
                conn.Execute("drop database " + dbName);

                // Restore the backup from S3
                var restoreMan = new RestoreManager();

                restoreMan.Restore(new string[2] {
                    testConfigFileName, backupResult.BackupName
                });

                var awsProfile = config["aws"]["profile"];

                Amazon.Util.ProfileManager.RegisterProfile(awsProfile, config["aws"]["access_key"], config["aws"]["secret_key"]);

                var creds = Amazon.Util.ProfileManager.GetAWSCredentials(awsProfile);

                var awsClient = new AmazonS3Client(creds, Amazon.RegionEndpoint.USEast1);

                conn.ChangeDatabase(dbName);
                var result = conn.Query("select test1 from test1 where test1 = 'data'");

                Assert.IsTrue(result.Count() == 1);

                var listReq = new ListObjectsRequest()
                {
                    BucketName = config["aws"]["bucket"]
                };

                var objects = awsClient.ListObjects(listReq);

                foreach (var obj in objects.S3Objects)
                {
                    if (obj.Key.IndexOf(dbName) != -1)
                    {
                        var delReq = new DeleteObjectRequest()
                        {
                            BucketName = config["aws"]["bucket"], Key = obj.Key
                        };

                        awsClient.DeleteObject(delReq);
                    }
                }
            }
            finally
            {
                conn.ChangeDatabase("master");
                conn.Execute("drop database " + dbName);

                if (File.Exists(backupFile))
                {
                    File.Delete(backupFile);
                }
            }
        }