using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace university
{
    public partial class student : Form
    {
        public student()
        {
            InitializeComponent();
        }
        bool newMode;
        DataSet ds = new DataSet();
        string strSql;
        string strCon = "SERVER=.;DATABASE=university;Integrated Security=True";
        SqlDataAdapter da;
        SqlConnection con;
        DataRow newRow;
        SqlCommandBuilder cb;
        private void empty()
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox6.Text = "";
            textBox7.Text = "";
            textBox8.Text = "";
            textBox9.Text = "";
            textBox10.Text = "";
            textBox11.Text = "";
            textBox12.Text = "";
            textBox13.Text = "";
        }
        private void student_Load(object sender, EventArgs e)
                    {
                        comboBox1.Items.Add("=");
                        comboBox1.Items.Add(">");
                        comboBox1.Items.Add(">=");
                        comboBox1.Items.Add("<");
                        comboBox1.Items.Add("<=");
                        comboBox1.Items.Add("!=");

                        comboBox2.Items.Add("stNo");
                        comboBox2.Items.Add("fname");
                        comboBox2.Items.Add("lname");

                        
                        comboBox1.Text = "=";
                        comboBox2.Text = "stNo";

            newMode = false;
            strSql = "SELECT * FROM student";
            con = new SqlConnection(strCon);
            con.Open();
            da = new SqlDataAdapter(strSql, con);
            da.Fill(ds, "student");
            textBox1.DataBindings.Add(new Binding("Text", ds, "student.stno"));
            textBox2.DataBindings.Add(new Binding("Text", ds, "student.fname"));
            textBox3.DataBindings.Add(new Binding("Text", ds, "student.lname"));
            textBox4.DataBindings.Add(new Binding("Text", ds, "student.id1"));
            textBox5.DataBindings.Add(new Binding("Text", ds, "student.birthdate"));
            textBox6.DataBindings.Add(new Binding("Text", ds, "student.sex"));
            textBox7.DataBindings.Add(new Binding("Text", ds, "student.age"));
            textBox8.DataBindings.Add(new Binding("Text", ds, "student.groupcode"));
            textBox9.DataBindings.Add(new Binding("Text", ds, "student.city"));
            textBox10.DataBindings.Add(new Binding("Text", ds, "student.address1"));
            textBox11.DataBindings.Add(new Binding("Text", ds, "student.tel"));
            textBox12.DataBindings.Add(new Binding("Text", ds, "student.notes"));
            textBox13.DataBindings.Add(new Binding("Text", ds, "student.picture"));

            dataGridView1.DataBindings.Add(new Binding("DataSource", ds, "student"));
            con.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            newRow = ds.Tables["student"].NewRow();
            newMode = true;
           
            empty();
            MessageBox.Show("Insert data and press save..");
            textBox1.Focus(); 
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (newMode == true)
            {
                newRow["stno"] = textBox1.Text;
                newRow["fname"] = textBox2.Text;
                newRow["lname"] = textBox3.Text;
                newRow["id1"] = textBox4.Text;
                newRow["birthdate"] = textBox5.Text;
                newRow["sex"] = textBox6.Text;
                newRow["age"] = textBox7.Text;
                newRow["groupcode"] = textBox8.Text;
                newRow["city"] = textBox9.Text;
                newRow["address1"] = textBox10.Text;
                newRow["tel"] = textBox11.Text;
                newRow["notes"] = textBox12.Text;
                newRow["picture"] = textBox13.Text;

                ds.Tables["student"].Rows.Add(newRow);
                cb = new SqlCommandBuilder(da);
                da = cb.DataAdapter;
                da.Update(ds, "student");
                con.Close();
                MessageBox.Show("Save is successful..");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string sn;
            SqlCommand cmd = new SqlCommand();
            string sqlDel;
            con.Open();
            sn = textBox1.Text;
            sqlDel = "DELETE FROM student WHERE stno=" + sn;
            cmd.CommandText = sqlDel;
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            ds.Clear();
            da.Fill(ds, "student");
            con.Close();
            MessageBox.Show("Delete is successful");
        }

        private void button5_Click(object sender, EventArgs e)
        {

            this.BindingContext[ds, "student"].Position = 0;
            textBox15.Text = Convert.ToString(this.BindingContext[ds, "student"].Position + 1);  
        }

        private void button6_Click(object sender, EventArgs e)
        {
            int iPos;
            iPos = this.BindingContext[ds, "student"].Position + 1;
            if (iPos > 0)
            {
                this.BindingContext[ds, "student"].Position -= 1;
                textBox15.Text = Convert.ToString(this.BindingContext[ds, "student"].Position + 1);
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            int iPos;
            int iCount;
            iPos = this.BindingContext[ds, "student"].Position + 1;
            iCount = this.BindingContext[ds, "student"].Count;
            if (iPos < iCount)
            {
                this.BindingContext[ds, "student"].Position += 1;
                textBox15.Text = Convert.ToString(this.BindingContext[ds, "student"].Position + 1);
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            int iCount;
            iCount = this.BindingContext[ds, "student"].Count;
            this.BindingContext[ds, "student"].Position = iCount;
            textBox15.Text = Convert.ToString(this.BindingContext[ds, "student"].Position + 1);
            
        }

        private void button4_Click(object sender, EventArgs e)
        {
            dataGridView1.DataBindings.Clear();
            strCon = "SERVER=.;DATABASE=university;Integrated Security=True";
            SqlConnection con = new SqlConnection(strCon);
            con.Open();
            if (textBox14.Text == "")
            {
                MessageBox.Show("جعبه متن خالی است کجایی همشهری ؟");
                textBox14.Focus();
            }
            else
                strSql = "SELECT * FROM student WHERE " + comboBox2.Text + " " + comboBox1.Text + " " + "'" + textBox14.Text + "'";
            SqlDataAdapter da = new SqlDataAdapter(strSql, con);
            textBox16.Text = strSql;
            DataSet ds = new DataSet();
            da.Fill(ds, "student");
            dataGridView1.DataBindings.Add(new Binding("DataSource", ds, "student"));
            con.Close();
        }

        private void button9_Click(object sender, EventArgs e)
        {
            dataGridView1.DataBindings.Clear();
            strCon = "SERVER=.;DATABASE=university;Integrated Security=True";
            SqlConnection con = new SqlConnection(strCon);
            con.Open();
            if (textBox16.Text == "")
            {
                MessageBox.Show("جعبه متن خالی است کجایی همشهری ؟");
                textBox14.Focus();
            }
            else
                strSql = textBox16.Text;
            SqlDataAdapter da = new SqlDataAdapter(strSql, con);
          
            DataSet ds = new DataSet();
            da.Fill(ds, "student");
            dataGridView1.DataBindings.Add(new Binding("DataSource", ds, "student"));
            con.Close();
        }

        private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox2.Focus();
        }

        private void textBox2_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox3.Focus();
        }

        private void textBox3_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox4.Focus();
        }

        private void textBox4_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox5.Focus();
        }

        private void textBox5_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox6.Focus();
        }

        private void textBox6_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox7.Focus();
        }

        private void textBox7_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox8.Focus();
        }

        private void textBox8_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox9.Focus();
        }

        private void textBox9_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox10.Focus();
        }

        private void textBox10_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox11.Focus();
        }

        private void textBox11_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                textBox12.Focus();
        }

        private void textBox12_KeyPress(object sender, KeyPressEventArgs e)
        {
            char ch;
            ch = e.KeyChar;
            if (ch == '\r')
                button2.Focus(); 
        }
    }
}
