实验三

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 实验三P176
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.MaximizeBox = false;
            this.CenterToScreen();
            this.Text = "实验三 连接环境下数据的操作";
        }

        private void btnQuit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnList_Click(object sender, EventArgs e)
        {
            this.ListBoxCJ.Items.Clear();
            this.ListBoxXH.Items.Clear();
            this.ListBoxXM.Items.Clear();
            SqlConnection conn = new SqlConnection();
            string strConn= @"Data Source=DESKTOP-UJ5IEQK\THIRTEEN;Initial Catalog=test_ADO.net;Integrated Security=true";
            conn.ConnectionString = strConn;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select xh AS 学号, XM AS 姓名,CJ AS 成绩 from STU";
                SqlDataReader dr;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    this.ListBoxXH.Items.Add(dr.GetString(0));
                    this.ListBoxXM.Items.Add(dr.GetString(1));
                    this.ListBoxCJ.Items.Add(dr.GetValue(2).ToString());
                }

            }
            catch (Exception sqlE)
            {

                MessageBox.Show(sqlE.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                conn.Close();
            }
        }

        private void btnCreateTable_Click(object sender, EventArgs e)
        { 
            SqlConnection conn = new SqlConnection();
            string strConn = @"Data Source=DESKTOP-UJ5IEQK\THIRTEEN;Initial Catalog=test_ADO.net;Integrated Security=true";
            conn.ConnectionString = strConn;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            string strCreateTable = "create table stu(XH char(10),XM varchar(20), CJ integer);";
            cmd.CommandText = strCreateTable;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlE)
            {
                MessageBox.Show(sqlE.Message,"错误提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
            finally
            {
                conn.Close();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            string strXH = this.txtXH.Text.Trim();
            if (strXH.Length == 0)
            {
                MessageBox.Show("学号不能为空！","提示信息",MessageBoxButtons.OK,MessageBoxIcon.Information);
                this.txtXH.Focus();
                return;
            }
            SqlConnection conn = new SqlConnection();
            try
            {
                string strConn = @"Data Source=DESKTOP-UJ5IEQK\THIRTEEN;Initial Catalog=test_ADO.net;Integrated Security=true";
                conn.ConnectionString = strConn;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;


                SqlParameter p1 = new SqlParameter();   //创建SqlParameter对象
                p1.ParameterName = "@xh";               //参数的名称
                p1.SqlDbType = SqlDbType.Char;           //参数的类型
                p1.Size = 10;                             //参数类型的大小
                p1.Direction = ParameterDirection.Input;  //参数的方向，Input 输入；Output 输出；InputOutput 输入或输出；Return Value 返回值
                p1.Value = strXH;                 //输入参数的值
                cmd.Parameters.Add(p1);

                string strDeleteData = "delete from stu where XH=@xh";
                cmd.CommandText = strDeleteData;
                int i = cmd.ExecuteNonQuery();
                MessageBox.Show(i + "条记录被删除！", "删除提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException sqlE)
            {
                MessageBox.Show(sqlE.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch (System.Exception NormalE)
            {
                MessageBox.Show(NormalE.Message , "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                conn.Close();
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string strXM;
            string strXH;
            int intCJ;
            SqlConnection conn = new SqlConnection();
            try
            {
                strXH = this.txtXH.Text.Trim();
                strXM = this.txtXM.Text.Trim();
                intCJ = Convert.ToInt16(this.txtCJ.Text.Trim());

                string strConn = @"Data Source=DESKTOP-UJ5IEQK\THIRTEEN;Initial Catalog=test_ADO.net;Integrated Security=true";
                conn.ConnectionString = strConn;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                SqlParameter p1 = new SqlParameter();   //创建SqlParameter对象
                p1.ParameterName = "@XH";               //参数的名称
                p1.SqlDbType = SqlDbType.Char;           //参数的类型
                p1.Size = 10;                             //参数类型的大小
                p1.Direction = ParameterDirection.Input;  //参数的方向，Input 输入；Output 输出；InputOutput 输入或输出；Return Value 返回值
                p1.Value = strXH;                 //输入参数的值
                cmd.Parameters.Add(p1);


                SqlParameter p2 = new SqlParameter();   //创建SqlParameter对象
                p2.ParameterName = "@XM";               //参数的名称
                p2.SqlDbType = SqlDbType.NVarChar;           //参数的类型
                p2.Size = 20;                             //参数类型的大小
                p2.Direction = ParameterDirection.Input;  //参数的方向，Input 输入；Output 输出；InputOutput 输入或输出；Return Value 返回值
                p2.Value = strXM;                 //输入参数的值
                cmd.Parameters.Add(p2);


                SqlParameter p3 = new SqlParameter();   //创建SqlParameter对象
                p3.ParameterName = "@CJ";               //参数的名称
                p3.SqlDbType = SqlDbType.Int;           //参数的类型
                p3.Direction = ParameterDirection.Input;  //参数的方向，Input 输入；Output 输出；InputOutput 输入或输出；Return Value 返回值
                p3.Value = intCJ;                 //输入参数的值
                cmd.Parameters.Add(p3);

                string strInsertData = "insert into stu values(@XH,@XM,@CJ)";
                cmd.CommandText = strInsertData;
                cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlE)
            {
                MessageBox.Show(sqlE.Message, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch (System.Exception NormalE)
            {
                MessageBox.Show(NormalE.Message ,"错误提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            finally
            {
                conn.Close();
            }

        }
    }
}