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;
using System.Data.OleDb;
using System.Data.Odbc;


namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        //OdbcConnection con;
        //OleDbConnection con;
        SqlConnection con;
        SqlDataAdapter da;
        DataSet ds;
        DataGridView dgv;
        CurrencyManager CM;
        DataView DV;
        Properties.Settings S = new WindowsFormsApplication4.Properties.Settings();
        private void Form1_Load(object sender, EventArgs e)
        {
            
            //con = new SqlConnection("server=site9-ostad; database=Test;integrated security=true;");
            //con = new SqlConnection("Data Source=site9-ostad; Initial Catalog=Test;integrated security=true;");
            //con = new SqlConnection("Data Source=site9-ostad; Initial Catalog=Test;user id=ali; password=123");
            con = new SqlConnection(S["ConStr"].ToString());
            try
            {
                con.Open();
                da = new SqlDataAdapter("Select * From St_Info", con);
                ds = new DataSet();
                da.Fill(ds,"T1");
                dgv=new DataGridView() {Parent=this,Dock=DockStyle.Fill };
                
                dgv.DataSource = ds;
                dgv.DataMember = "T1";
                con.Close();
                dgv.SelectionChanged += new EventHandler(dgv_SelectionChanged);
            }
            catch (Exception)
            {
                MessageBox.Show("Error In Openinig Database...");
            }
        }

        void dgv_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                if (dgv.RowCount > 1)
                {
                    textBox1.Text = dgv.SelectedRows[0].Cells[0].Value.ToString();
                    textBox2.Text = dgv.SelectedRows[0].Cells[1].Value.ToString();
                    textBox3.Text = dgv.SelectedRows[0].Cells[2].Value.ToString();
                }
            }
            catch (Exception)
            {                
            }
        }
        private  void dgvRefresh()
        {
            ds.Tables["T1"].Clear();
            con.Open();
            da = new SqlDataAdapter("Select * From St_Info", con);            
            da.Fill(ds, "T1");
            con.Close();
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            
            con.Open();
            //SqlCommand AddCMD = new SqlCommand("Insert Into St_Info (St_ID,St_Name,St_Avg) Values (" +
              //  long.Parse(textBox1.Text) + ",'" + textBox2.Text + "'," + float.Parse(textBox3.Text) + ");", con);
            SqlCommand AddCMD = new SqlCommand("Insert Into St_Info (St_ID,St_Name,St_Avg) Values (@i,@n,@a);", con);
            AddCMD.Parameters.AddWithValue("@i", Convert.ToInt64(textBox1.Text));
            AddCMD.Parameters.AddWithValue("@n", textBox2.Text);
            AddCMD.Parameters.AddWithValue("@a", Convert.ToSingle(textBox3.Text));
            AddCMD.ExecuteNonQuery();            
            con.Close();
            dgvRefresh();
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            con.Open();
            da = new SqlDataAdapter();
            da.DeleteCommand = new SqlCommand("Delete St_Info Where St_ID=" + long.Parse(textBox1.Text), con);
            da.DeleteCommand.ExecuteNonQuery();            
            con.Close();
            dgvRefresh();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            con.Open();
            SqlCommand UpdateCMD = new SqlCommand("Update St_Info set St_Name='" + textBox2.Text 
                + "',St_Avg=" + float.Parse(textBox3.Text) + " Where St_ID=" + long.Parse(textBox1.Text) , con);            
            UpdateCMD.ExecuteNonQuery();
            con.Close();
            dgvRefresh();
        }
    }
}
