Monday, 30 November 2015

Some Button code:

private void CmbSelectedName_SelectedIndexChanged(object sender, EventArgs e)
            {
                Register regis = new Register();
                DataTable dt = new DataTable();


                if (combo == false)
                {
                    dt = regis.ViewSelectedRecords(Convert.ToInt32(CmbSelectedName.SelectedValue));
                    if (dt.Rows.Count > 0)
                    {

                        txtName.Text = dt.Rows[0]["Name"].ToString();
                        txtAge.Text = dt.Rows[0]["Age"].ToString();
                        txtAddress.Text = dt.Rows[0]["Address"].ToString();
                        if (dt.Rows[0]["Gender"].ToString() == "Male")
                        {
                            rdbFemale.Checked = true;
                        }
                        else
                        {
                            rdbMale.Checked = true;
                        }
                        cmbCity.Text = dt.Rows[0]["City"].ToString();
                        txtPin.Text = dt.Rows[0]["Pin"].ToString();
                        txtMobileNum.Text = dt.Rows[0]["MobileNum"].ToString();
                        cmbState.Text = dt.Rows[0]["State"].ToString();
                        cmbCountry.Text = dt.Rows[0]["Country"].ToString();
                        txtEmail.Text = dt.Rows[0]["Email"].ToString();
                    }
                }
            }

            private void Form1_Load(object sender, EventArgs e)
            {
                this.Size = Screen.PrimaryScreen.WorkingArea.Size;
                combo = true;
                fillmethod();
                ClearData();
                combo = false;

            }

            private void fillmethod()
            {
                DataTable dtName = new DataTable();
                Register reg = new Register();
                CmbSelectedName.DataSource = reg.GetName();
                CmbSelectedName.DisplayMember = "Name";
                CmbSelectedName.ValueMember = "Id";
                CmbSelectedName.SelectedIndex = -1;
            }
            private void ClearData()
            {
                CmbSelectedName.SelectedItem = "";
                txtName.Text = "";
                txtAge.Text = "";
                txtAddress.Text = "";
                rdbFemale.Checked = false;
                rdbMale.Checked = false;
                cmbCity.Text = "";
                txtPin.Text = "";
                txtMobileNum.Text = "";
                cmbState.Text = "";
                cmbCountry.Text = "";
                txtEmail.Text = "";
            }
            private void btnSaved_Click_1(object sender, EventArgs e)
            {

                Register regi = new Register();
                if (txtName.Text == "")
                {
                    MessageBox.Show("enter your name");
                    txtName.Focus();
                    return;
                }
                if (txtAge.Text == "")
                {
                    MessageBox.Show("enter your age");
                    txtAge.Focus();
                    return;
                }
                regi.Name = txtName.Text;
                int a = Convert.ToInt32(txtAge.Text);
                if (a > 10)
                {
                    MessageBox.Show("Can't Register for age greater than 10");
                    ClearData();
                    return;
                }
                else
                {
                   // int Age = DateTime.Today.Year - dateTimePicker1.Value.Year; // CurrentYear - BirthDate
                   // txtAge.Text = Age.ToString();
                    regi.Age = Convert.ToString(txtAge.Text);                
                   
                }
                regi.Address = txtAddress.Text;
                if (rdbMale.Checked == true)
                {
                    regi.Gender = "MALE";
                }
                else
                {
                    regi.Gender = "Female";
                }
                regi.City = cmbCity.Text;
                if (txtPin.Text.Length == 6)
                {
                    regi.Pin = txtPin.Text;
                }
                else
                {
                    MessageBox.Show("Invalid Pin Number");
                    ClearData();
                    return;
                }
                regi.Pin = txtPin.Text;

                if (txtMobileNum.Text.Length == 10)
                {
                    regi.MobileNum = txtMobileNum.Text;
                }
                else
                {
                    MessageBox.Show("Invalid Phn Number");
                    ClearData();
                    return;
                }

                regi.State = cmbState.Text;
                regi.Country = cmbCountry.Text;

                System.Text.RegularExpressions.Regex rEmail = new System.Text.RegularExpressions.Regex(@"^[a-zA-Z][\w\.-]{2,28}[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$");
                if (txtEmail.Text.Length > 0)
                {
                    if (rEmail.IsMatch(txtEmail.Text))
                    {
                        regi.Email = txtEmail.Text;
                    }
                    else
                    {
                        MessageBox.Show("Give valid mail id");
                        ClearData();
                        return;
                    }

                    int saveRecords = regi.SaveToRegister();
                    if (saveRecords > 0)
                    {
                        MessageBox.Show("Successfuly Saved");
                        fillmethod();
                        ClearData();
                    }
                    else
                    {
                        MessageBox.Show("Failed to Save");

                    }
                }
            }

            private void btnUpdated_Click(object sender, EventArgs e)
            {
                Register reg = new Register();
                reg.Name = txtName.Text;
                reg.Age = Convert.ToString(txtAge.Text);
                reg.Address = txtAddress.Text;
                if (rdbMale.Checked == true)
                {
                    reg.Gender = "Male";
                }
                else
                {
                    reg.Gender = "Female";
                }
                reg.City = cmbCity.Text;
                reg.Pin = txtPin.Text;
                reg.MobileNum = txtMobileNum.Text;
                reg.State = cmbState.Text;
                reg.Country = cmbCountry.Text;
                reg.Email = txtMobileNum.Text;

                int UpdateRecord = reg.UpdateRecords(CmbSelectedName.SelectedIndex + 1);
                if (UpdateRecord > 0)
                {
                    MessageBox.Show("Member updated");
                    fillmethod();
                    ClearData();

                }
                else
                {
                    MessageBox.Show("Failed To Updated");
                    ClearData();
                }
            }

            private void btnCleared_Click(object sender, EventArgs e)
            {
                CmbSelectedName.SelectedIndex = -1;
                txtName.Text = "";
                txtAge.Text = "";
                txtAddress.Text = "";
                rdbFemale.Checked = false;
                rdbMale.Checked = false;
                cmbCity.Text = "";
                txtPin.Text = "";
                txtMobileNum.Text = "";
                cmbState.Text = "";
                cmbCountry.Text = "";
                txtEmail.Text = "";

            }

            private void btnDeleted_Click(object sender, EventArgs e)
            {
                Register re = new Register();
                int rs = re.DeleteRecords(Convert.ToInt32(CmbSelectedName.SelectedValue));
                if (rs > 0)
                {
                    MessageBox.Show("Deleted Successfully");
                    fillmethod();
                    ClearData();

                }
                else
                {
                    MessageBox.Show("Failed to Delete");
                    ClearData();
                }
            }

            private void btnViewd_Click(object sender, EventArgs e)
            {
                frmView objView = new frmView();
                objView.Show();
            }
           
            private void btnClosed_Click(object sender, EventArgs e)
            {
                //LoginPage lp = new LoginPage();
                this.Close();
           

            }

            private void btnLogout_Click(object sender, EventArgs e)
            {
                MessageBox.Show("Thank you for registration");
                this.Hide();
                LoginPage lp = new LoginPage();
                lp.Show();
               
            }

          
            private void btnDetails_Click(object sender, EventArgs e)
            {
                detailsView df = new detailsView();
                string gender = "";
                if (rdbFemale.Checked)
                {
                    gender = "Male";  
                }
                else
                {
                    gender = "Female";
                }
                df.setvalues(txtName.Text,
                             Convert.ToInt32( txtAge.Text),
                             txtAddress.Text,gender,
                              cmbCity.Text.ToString(),
                                txtPin.Text,txtMobileNum.Text,
                                cmbState.Text.ToString(),
                                cmbCountry.Text.ToString(),
                                txtEmail.Text);
                df.ShowDialog();
            }

            private void txtAge_TextChanged(object sender, EventArgs e)
            {
            }

            private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
            {
                int age = DateTime.Today.Year - dateTimePicker1.Value.Year;
                txtAge.Text = age.ToString();
            }
 
BL - method calling:

 class Register
    {
        public string Name
        {
            get;
            set;
        }
        public string Age
        {
            get;
            set;
        }
        public string Address
        {
            get;
            set;
        }
       
        public string Gender
        {
            get;
            set;
        }
        public string City
        {
            get;
            set;
        }
        public string Pin
        {
            get;
            set;
        }
        public string MobileNum
        {
            get;
            set;
        }
        public string State
        {
            get;
            set;
        }
        public string Country
        {
            get;
            set;
        }
        public string Email
        {
            get;
            set;
        }
       
        public int SaveToRegister()
        {
            SqlDb db = new SqlDb();
            StringBuilder strb = new StringBuilder();
            strb.Append("Insert into RegisterDetails (Name,Age,Address,Gender,City,Pin,MobileNum,State,Country,Email) values(");
            strb.Append("'" + Name + "',");
            strb.Append("'" + Age + "',");
            strb.Append("'" + Address + "',");
            strb.Append("'" + Gender + "',");
            strb.Append("'"+City+"',");
            strb.Append("'"+Pin+"',");
            strb.Append("'"+MobileNum+"',");
            strb.Append("'" + State + "',");
            strb.Append("'"+Country+"',");
            strb.Append("'"+Email+"')");
            int reg = db.ExecuteNonQry(strb.ToString());
            strb.Remove(0, strb.Length);
            return reg;
        }
        public string Username
        {
            get;
            set;
        }
        public string Password
        {
            get;
            set;
        }
        public int CreateUser()
        {
            SqlDb db = new SqlDb();
            StringBuilder strb = new StringBuilder();
            strb.Append("insert into LoginPage (Username,Password) values(");
            strb.Append("'"+Username+"',");
            strb.Append("'"+Password+"')");
            int re = db.ExecuteNonQry(strb.ToString());
            strb.Remove(0, strb.Length);
            return re;
         }

        public DataTable viewRecords()
        {
            SqlDb db = new SqlDb();
            StringBuilder strb = new StringBuilder();
            strb.Append("Select * from RegisterDetails");
            DataTable dt = new DataTable();
            dt = db.GetTable(strb.ToString());
            return dt;

        }
        public DataTable GetId()
        {
            SqlDb sqdb = new SqlDb();
            String str = "";
            str = "Select Id from RegisterDetails";
            DataTable dt = sqdb.GetTable(str.ToString());
            return dt;
        }

        public DataTable GetName()
        {
            SqlDb sqdb = new SqlDb();
            String str = "";
            str = "select Name,Id from RegisterDetails";
            DataTable dt = sqdb.GetTable(str.ToString());
            return dt;
        }
        public DataTable ViewSelectedRecords(int id)
        {
            SqlDb db = new SqlDb();
            StringBuilder strb = new StringBuilder();
            strb.Append(" Select * from RegisterDetails where Id='"+id+"'");
            DataTable dt = new DataTable();
            dt = db.GetTable(strb.ToString());
            return dt;
        }
      
        public int DeleteRecords(int id)
        {
            SqlDb db = new SqlDb();
            String str = "";
            str = "Delete from RegisterDetails where Id='"+id+"'";
            int rs = db.ExecuteNonQry(str.ToString());
            return rs;
        }     
        public int UpdateRecords(int id)
        {
            SqlDb  sqdb = new SqlDb();
            string strb = "";
            strb = "update RegisterDetails set Name='" + Name + "',Age='" + Age + "',Address='" + Address + "',Gender='" + Gender + "',City='"+City+"',Pin='"+Pin+"',MobileNum='"+MobileNum+"',State='"+State+"',Country='"+Country+"',Email='"+Email+"'  where Id='" + id + "'";
            int rs = sqdb.ExecuteNonQry(strb.ToString());
            return rs;
        }
    }
    
    }

Thursday, 26 November 2015

Connection String:

public class dbcommon
{
    String str = "Data Source=P02\\SQLEXPRESS;Initial Catalog=TravelManagemnt;User Id=sa;Password=softex";
        SqlConnection con;
        SqlCommand cmd;
        public int ExeNonQry(string qry)       //insert,update,delete
        {
            try
            {
                con = new SqlConnection(str);
                con.Open();
                cmd = new SqlCommand(qry, con);
                int rs = cmd.ExecuteNonQuery();
                return rs;
            }
            catch (Exception e)
            {
                return 0;
            }
            finally
            {
                con.Close();
            }
        }


        public SqlDataReader reader(string qry)  //reading values from table using sqldatareader
        {
            try
            {
                con = new SqlConnection(str);
                con.Open();
                cmd = new SqlCommand(qry, con);
                SqlDataReader dr = cmd.ExecuteReader();
                return dr;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {  //con is not closed for reading values from table
            }
        }


        public DataTable getTable(string qry) //reading values from table using datatable
        {
            try
            {
                con = new SqlConnection(str);
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(qry, con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
            }
        }
}