使用SQLHelper实现CURD

1 public class Student
2 {
3     public int id;
4     public string name;
5     public string address;
6 }

public static class SQLHelper
{
    //连接字符串
    private static readonly string connStr = "Data Source=DESKTOP-NJ2V6IC;Initial Catalog=Study;Integrated Security=True";

    //1.执行增、删、改的方法：ExecuteNonQuery
    public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
    {
        using (SqlConnection con = new SqlConnection(connStr))
        {
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                con.Open();
                return cmd.ExecuteNonQuery();
            }
        }
    }

    //2.封装一个执行返回单个对象的方法：ExecuteScalar()
    public static object ExecuteScalar(string sql, params SqlParameter[] pms)
    {
        using (SqlConnection con = new SqlConnection(connStr))
        {
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                con.Open();
                return cmd.ExecuteScalar();
            }
        }
    }

    //3.执行查询多行多列的数据的方法：ExecuteReader
    public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
    {
        SqlConnection con = new SqlConnection(connStr);
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            if (pms != null)
            {
                cmd.Parameters.AddRange(pms);
            }
            try
            {
                con.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {
                con.Close();
                con.Dispose();
                throw;
            }
        }
    }

    //4.执行返回DataTable的方法
    public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
    {
        DataTable dt = new DataTable();
        using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStr))
        {
            if (pms != null)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            adapter.Fill(dt);
        }
        return dt;
    }
}

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    /// <summary>
    /// 加载数据
    /// </summary>
    void LoadData()
    {
        string sql = "select * from Student";
        SqlDataReader sdr = SQLHelper.ExecuteReader(sql);
        if (sdr.HasRows)
        {
            while (sdr.Read())
            {
                int index = dataGridView1.Rows.Add();
                dataGridView1.Rows[index].Cells[0].Value = sdr[0].ToString();
                dataGridView1.Rows[index].Cells[1].Value = sdr[1].ToString();
                dataGridView1.Rows[index].Cells[2].Value = sdr[2].ToString();
            }
        }
        sdr.Close();
    }

    /// <summary>
    /// 查询
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button1_Click(object sender, EventArgs e)
    {
        dataGridView1.Rows.Clear();
        LoadData();
    }

    /// <summary>
    /// 添加
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button2_Click(object sender, EventArgs e)
    {
        try
        {
            int index = dataGridView1.CurrentRow.Index;
            if (index != -1)
            {
                Student student = new Student();
                student.name = dataGridView1.Rows[index].Cells[1].Value.ToString();
                student.address = dataGridView1.Rows[index].Cells[2].Value.ToString();

                string sql = "insert into Student(name,address) values('" + student.name + "','" + student.address + "')";
                int i = SQLHelper.ExecuteNonQuery(sql);
                MessageBox.Show("添加成功");
            }
        }
        catch (Exception ex)
        {

        }
    }

    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button3_Click(object sender, EventArgs e)
    {
        try
        {
            int index = dataGridView1.CurrentRow.Index;
            if (index != -1)
            {
                Student student = new Student();
                student.id = int.Parse(dataGridView1.Rows[index].Cells[0].Value.ToString());
                string sql = "DELETE FROM Student  where id='" + student.id + "'";
                int i = SQLHelper.ExecuteNonQuery(sql);
                MessageBox.Show("删除成功");
            }
        }
        catch (Exception ex)
        {

        }
    }

    /// <summary>
    /// 修改
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button4_Click(object sender, EventArgs e)
    {
        try
        {
            int index = dataGridView1.CurrentRow.Index;
            if (index != -1)
            {
                Student student = new Student();
                student.id = int.Parse(dataGridView1.Rows[index].Cells[0].Value.ToString());
                student.name = dataGridView1.Rows[index].Cells[1].Value.ToString();
                student.address = dataGridView1.Rows[index].Cells[2].Value.ToString();

                string sql = "update Student set name='" + student.name + "',address='" + student.address + "' where id='" + student.id + "'";
                int i = SQLHelper.ExecuteNonQuery(sql);
                MessageBox.Show("修改成功");
            }
        }
        catch (Exception ex)
        {

        }
    }
}