ADO.NET(用窗体实现crud)

来源：互联网发布：广州数控g71编程实例编辑：程序博客网时间：2024/06/08 00:08
//下面用WinForm结合DataGridView控件做个crud
//用到的ADO.NET对象有Connection、Command、DataAdapter、Dataset、DataReader
//另外提下using的三种用法
//①.using引入命名空间，例如：using System;
//②.using创建的对象，自动释放资源，例如：下面创建的SqlConnection对象
//③.using引入命名空间的同时赋别名，例如：using AA=System.Text;
using System;using System.Data;using System.Windows.Forms;using System.Data.SqlClient;namespace WindowsFormsApplication1{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        public static string connStr = "server=.;database=winform;integrated security=true";        /// <summary>        /// 窗体加载        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void Form1_Load(object sender, EventArgs e)        {            dataGridView1.AutoGenerateColumns = false;//不自动产生列            DataBind();                               }              /// <summary>        /// 刷新列表        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void button1_Click(object sender, EventArgs e)        {            DataBind();        }               #region 新增        private void button2_Click(object sender, EventArgs e)        {            string name = textBox1.Text.Trim();            string pwd = textBox2.Text.Trim();            string sex = "男";            if (radioButton2.Checked==true)            {                sex = "女";            }            else            {                sex = "男";            }            DateTime dt = dateTimePicker1.Value;            string remark = richTextBox1.Text.Trim();            //1.准备sql语句            string sql = "insert into users values(@a,@b,@c,@d,@e)";            SqlParameter[] pms = {                   new SqlParameter("@a",name),                   new SqlParameter("@b",pwd),                   new SqlParameter("@c",sex),                   new SqlParameter("@d",dt),                   new SqlParameter("@e",remark)                              };            //2.创建连接对象            SqlConnection conn = new SqlConnection(connStr);            //3.打开连接            conn.Open();            //4.创建执行对象            SqlCommand cmd = new SqlCommand(sql, conn);            cmd.Parameters.AddRange(pms);            //5.执行            int row = cmd.ExecuteNonQuery();            //6.关闭连接            conn.Close();            if (row>0)            {                MessageBox.Show("新增成功！");                DataBind();            }            else            {                MessageBox.Show("新增失败！");            }        }#endregion        #region 删除        //单项删除很简单：Sql语句+Connection对象+Command对象+ExecuteNonequery方法        //多项删除也很简单：SqlTransaction+Try...Catch()+Foreach+单项删除内容        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)        {            if (dataGridView1.SelectedRows.Count<1)            {                return;            }           if (dataGridView1.SelectedRows.Count >0)            {                if (MessageBox.Show("确定删除？","警告",MessageBoxButtons.YesNo,MessageBoxIcon.Asterisk)==DialogResult.Yes)                {                    SqlConnection conn = new SqlConnection(connStr);                    conn.Open();                    SqlTransaction st = conn.BeginTransaction();//开始事务                    try                    {                        foreach (DataGridViewRow item in dataGridView1.SelectedRows)                        {                            string id = item.Cells[0].Value.ToString();                            string sql = "delete from users where uid=@a";                            SqlCommand cmd = new SqlCommand(sql, conn);                            cmd.Parameters.Add(new SqlParameter("@a", id));                            cmd.Transaction = st;//指定cmd执行是属于哪个事务                            cmd.ExecuteNonQuery();                                                    }                        st.Commit();//提交事务                        MessageBox.Show("删除成功！");                        DataBind();//刷新列表                    }//成功                                                                  catch (Exception)                    {                        st.Rollback();//回滚                        MessageBox.Show("删除失败！");                    }                    conn.Close();                }            }        }        #endregion        #region 修改        //修改：1.将要修改的项遍历到控件中去:SqlConnection+SqlCommand+SqlDataReader+ExecuteReader        private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)        {            string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();                    SqlConnection conn = new SqlConnection(connStr);            conn.Open();            SqlCommand cmd = new SqlCommand("select * from users where uid=@a", conn);            cmd.Parameters.Add("@a", id);            SqlDataReader sdr = cmd.ExecuteReader();            bool b = sdr.Read();//只进向前流的读取            if (b)            {                textBox1.Text = sdr["uname"].ToString();                textBox2.Text = sdr["upwd"].ToString();                if (sdr["usex"].ToString()=="男")                {                    radioButton1.Checked = true;                }                else                {                    radioButton2.Checked = true;                }            }            dateTimePicker1.Value = Convert.ToDateTime(sdr["ubirthday"]);            richTextBox1.Text = sdr["uremark"].ToString();                        conn.Close();                    }              //提交修改：2.获取控件中的值以此改数据库数据        //其实这里有个bug：如果当用户选中1001这条记录右键修改，        //在点击提交修改数据的过程中点击了其它条记录，实际上修改的就是其它记录        //建议：当选中行后右键修改时，将该条记录的编号用静态变量存储，这里我就难得改了        private void button3_Click(object sender, EventArgs e)        {            string id=dataGridView1.SelectedRows[0].Cells[0].Value.ToString();            using (SqlConnection conn=new SqlConnection(connStr))            {                conn.Open();                SqlCommand cmd = new SqlCommand("update users set uname=@a,usex=@b,ubirthday=@c,uremark=@d,upwd=@f where uid=@e", conn);                SqlParameter[] pms = new SqlParameter[6];                pms[0] = new SqlParameter("@a", textBox1.Text.Trim());                pms[1] = new SqlParameter("@f", textBox2.Text.Trim());                if (radioButton1.Checked==true)                {                    pms[2] = new SqlParameter("@b", radioButton1.Text);                }                else                {                    pms[2] = new SqlParameter("@b", radioButton2.Text);                }                pms[3] = new SqlParameter("@c", dateTimePicker1.Value.ToString());                pms[4] = new SqlParameter("@d", richTextBox1.Text.Trim());                pms[5] = new SqlParameter("@e", id);                cmd.Parameters.AddRange(pms);                int row=cmd.ExecuteNonQuery();                conn.Close();                if (row>0)                {                    MessageBox.Show("修改成功！");                    DataBind();                }                else                {                    MessageBox.Show("修改成功！");                }            }        }        #endregion        #region 自定义方法        //显示表数据        private void DataBind()        {            //1.准备sql语句            string sql = "select * from users";            //2.创建连接对象            SqlConnection conn = new SqlConnection(connStr);            //3.打开连接            conn.Open();            //4.创建执行对象            SqlCommand cmd = new SqlCommand(sql, conn);            //5.创建适配器对象            SqlDataAdapter sda = new SqlDataAdapter();            sda.SelectCommand = cmd;            //6.创建适配器对象            DataSet ds = new DataSet();            //7.填充数据集            sda.Fill(ds, "aa");            DataTable dt = ds.Tables["aa"];            //8.关闭连接            conn.Close();            //显示            dataGridView1.DataSource = dt;        }        #endregion    }}
0 0