创建sqlhelp类以封装对数据库的操作及对可空类型的操作

namespace 对TblPerson增删查改_Sqlhelp_
{
    public static class SqlHelp
    {
        static readonly string constr = ConfigurationManager.ConnectionStrings["MyconnectionString2"].ConnectionString;
        public static int ExecuteNoneQuery(string sql,params SqlParameter[] p) {
            using (SqlConnection conn=new SqlConnection(constr))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    if (p!=null)
                    {
                        cmd.Parameters.AddRange(p); 
                    }
                    if (conn.State==ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                       return  cmd.ExecuteNonQuery();
                }
            }
        }
        public static int ExecuteScalar(string sql,params SqlParameter[] p) {
            using (SqlConnection conn=new SqlConnection(constr))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    if (p!=null)
                    {
                        cmd.Parameters.AddRange(p);
                    }
                    if (conn.State==ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    return (int)cmd.ExecuteScalar();
                }
            }
        }
        public static SqlDataReader ExecuteReader(string sql,params SqlParameter[] p) {
            SqlConnection conn = new SqlConnection(constr);
            using (SqlCommand cmd=new SqlCommand(sql,conn))
            {
                if (p!=null)
                {
                    cmd.Parameters.AddRange(p);
                }
                try
                {
                    conn.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch 
                {
                    conn.Close();
                    conn.Dispose();
                    throw;
                }
            } 
        }
    }
}

app.config文件

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>

    <add  name="MyconnectionString2" connectionString="Data Source=MGLI217RZIKKH6B;Initial Catalog=Itcast2014;Integrated Security=true"/>
  </connectionStrings>
</configuration>

下面的案例为调用sqlhelp来对数据库操作，注意对可空类型的处理

namespace 对TblPerson增删查改_Sqlhelp_
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadData();
        }
        public void LoadData() { 
            List<Person> ls = new List<Person>();
            string sql = "select * from TblPerson";
            using (SqlDataReader reader=SqlHelp.ExecuteReader(sql))
            {     //SqlDataReader reader = SqlHelp.ExecuteReader(sql)
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        int age = reader.GetInt32(2);
                        int? height = reader.IsDBNull(3) ? null : (int?)reader.GetInt32(3);
                        bool? isBoy = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
                        string gender = "保密";
                         if (isBoy==true)
                        {
                            gender = "男";
                        }
                        else 
                        {
                            gender = "女";
                        }
                         ls.Add(new Person(id, name, age, height, gender));
                    }
                }
                else {
                    MessageBox.Show("没有任何数据");
                }    
            }
            dataGridView1.DataSource = ls;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string sql = "insert into TblPerson values(@name,@age,@height,@gender)";
            SqlParameter sp1 = new SqlParameter("@name", txtUerName.Text);
            SqlParameter sp2 = new SqlParameter("@age",txtAge.Text);
            SqlParameter sp3 = new SqlParameter("@height",txtHeight.Text);
           bool gender=false;
            if (cbGender.SelectedIndex==0)
            {
               gender=true;
            }
            SqlParameter sp4 = new SqlParameter("@gender",gender);
           int num= SqlHelp.ExecuteNoneQuery(sql, sp1, sp2, sp3, sp4);
           if (num>0)
           {
               MessageBox.Show("成功插入"+num+"tia"); 
           }
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
            Person p = row.DataBoundItem  as Person;
            labelID.Text = p.Id.ToString();
            txtUserE.Text = p.Name;
            txtAgeE.Text = p.Age.ToString();
            txtHeightE.Text = p.Height.ToString();
            if (p.Gender == "男")
            {
                this.cbGenderE.SelectedIndex = 0;
            }
            else if (p.Gender == "女")
            {
                this.cbGenderE.SelectedIndex = 1;
            }
            else {
                this.cbGenderE.SelectedIndex = -1;
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            string sql = "update TblPerson set uName=@name,age=@age,height=@height,gender=@gender where autoId=@id";
            SqlParameter sp1 = new SqlParameter("@name",txtUserE.Text);
            SqlParameter sp2 = new SqlParameter("@age",txtAgeE.Text);
            SqlParameter sp3 = new SqlParameter("@height",txtHeightE.Text);
            bool? gender =null;
            if (cbGenderE.SelectedItem.ToString() =="男")
            {
                gender = true;
            }
            else   {
                gender = false;
            }
            SqlParameter sp4= new SqlParameter("@gender",gender);
            SqlParameter sp5 = new SqlParameter("@id",Convert.ToInt32(labelID.Text));
          int num=  SqlHelp.ExecuteNoneQuery(sql,sp1,sp2,sp3,sp4,sp5);
          if (num>0)
          {
              MessageBox.Show("成功修改"+num+"条数据");
              LoadData();
          }
        }
          
        }
    }

 

namespace 对TblPerson增删查改_Sqlhelp_
{
    class Person
    {
        public int Id { set; get; }
        public string Name { set; get; }
        public int Age { set; get; }
        public int? Height { set; get; }
        public string Gender { set; get; }
        public Person(int id,string name,int age,int? heigth,string gender) {
            this.Id = id;
            this.Name = name;
            this.Age = age;
            this.Height = heigth;
            this.Gender = gender;
        }
    }
}