預存程序的應用

create proc modify
(@編號 [int],
@姓名 [varchar](50),
@電話 [varchar](50)
)
as
insert into pro(stuID,stuName,stuPhone) values(@編號,@姓名,@電話)
go

create proc upd
(@編號 [int],
@姓名 [varchar](50),
@電話 [varchar](50)
)
as
update pro set stuID=@編號,stuName=@姓名,stuPhone=@電話 where (stuID=@編號)
go
create proc del
(
@編號 [int]
)
as
delete pro where ([stuID]=@編號)

 

 

namespace 預存程序
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = DB.CreateCon();
            con.Open();
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand("getAll",con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            sda.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0].DefaultView;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection con = DB.CreateCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("modify",con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] prams ={ new SqlParameter("@編號", SqlDbType.Int), new SqlParameter("@姓名", SqlDbType.VarChar, 50), new SqlParameter("@電話", SqlDbType.VarChar, 50) };
            prams[0].Value = id.Text;
            prams[1].Value = name.Text;
            prams[2].Value = phone.Text;
            foreach (SqlParameter pa in prams)
            {
                cmd.Parameters.Add(pa);
            }
            cmd.ExecuteNonQuery();
            //this.Load(sender,e);
            this.button1_Click(sender,e);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection con = DB.CreateCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("select stuID from pro",con);
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                comboBox1.Items.Add(sdr["stuID"].ToString());
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection con = DB.CreateCon();
            con.Open();
            int i = Convert.ToInt32(comboBox1.SelectedItem);
            SqlCommand cmd = new SqlCommand("select * from pro where stuID='" + i + "'", con);
            SqlDataReader sdr = cmd.ExecuteReader();
            sdr.Read();
            textBox1.Text = sdr["stuName"].ToString();
            textBox2.Text = sdr["stuPhone"].ToString();
            //while (sdr.Read())
            //{
                   
            //}
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SqlConnection con = DB.CreateCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("upd",con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] prams ={ new SqlParameter("@編號", SqlDbType.Int), new SqlParameter("@姓名", SqlDbType.VarChar, 50), new SqlParameter("@電話", SqlDbType.VarChar, 50) };
            prams[0].Value = comboBox1.Text;
            prams[1].Value = textBox1.Text;
            prams[2].Value = textBox2.Text;
            foreach (SqlParameter sp in prams)
            {
                cmd.Parameters.Add(sp);
            }
            cmd.ExecuteNonQuery();
            button1_Click(sender,e);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            SqlConnection con = DB.CreateCon();
            con.Open();
            SqlCommand cmd = new SqlCommand("del",con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter sp = new SqlParameter("@編號",SqlDbType.Int);
            cmd.Parameters.Add(sp);
            cmd.Parameters["@編號"].Value = comboBox1.Text;
            cmd.ExecuteNonQuery();
            button1_Click(sender, e);
        }
    }