对数据的增删查改深度理解三(多)层编程理念

        private void btnSelect_Click(object sender, EventArgs e)
        {
            if (txtIDCar.Text.Trim().Length == 18)
            {
                DeliverData deliver = new DeliverData();
                TblPerson person = null;
                if ((person = deliver.Select(txtIDCar.Text.Trim())) != null)
                {
                    txtName.Text = person.姓名 == null ? "" : person.姓名.ToString();
                    txtPinYinShort.Text = person.拼音短拼 == null ? "" : person.拼音短拼.ToString();
                    txtPhone.Text = person.手机号 == null ? "" : person.手机号.ToString();
                    txtGender.Text = person.性别 == null ? "" : person.性别.ToString();
                    txtBirthPlace.Text = person.出生地址 == null ? "" : person.出生地址.ToString();
                    txtBirthDay.Text = person.出生日期 == null ? "" : person.出生日期.ToString();
                    txtAge.Text = person.年龄 == null ? "" : person.年龄.ToString();
                }
                else
                {
                    MessageBox.Show("未找到任何数据.");
                }
            }
        }

        private void btnModify_Click(object sender, EventArgs e)
        {
            TblPerson person = new TblPerson();
            DataGridViewRow row = dgvDataShow.SelectedRows[0];

            //row.Cells[0].ReadOnly = true;
            person.ID = int.Parse(row.Cells[0].Value.ToString());
            person.姓名 = row.Cells[1].Value.ToString();
            person.身份证号 = row.Cells[2].Value.ToString();
            person.年龄 = row.Cells[3].Value == null ? null : (int?)row.Cells[3].Value;
            person.性别 = row.Cells[4].Value == null ? null : (bool?)row.Cells[4].Value;
            person.出生日期 = row.Cells[5].Value == null ? null : (DateTime?)row.Cells[5].Value;
            person.出生地址 = row.Cells[6].Value == null ? null : (string)row.Cells[6].Value;
            person.手机号 = row.Cells[7].Value == null ? null : (string)row.Cells[7].Value;
            person.拼音短拼 = row.Cells[8].Value == null ? null : row.Cells[8].Value.ToString();
            DeliverData deliver = new DeliverData();
            if (1 == deliver.Modify(person))
                MessageBox.Show("成功更新1行.");
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            TblDataManager manager = new TblDataManager();
            TblPerson person = new TblPerson();
            try
            {
                //这里应检查不能为空。
                person.姓名 = txtName.Text.Trim();
                person.身份证号 = txtIDCar.Text.Trim();
                person.年龄 = txtAge.Text.Trim().Length == 0 ? null : (int?)int.Parse(txtAge.Text.Trim());
                person.性别 = txtGender.Text.Trim().Length == 0 ? null : (bool?)(txtGender.Text.ToLower().Trim() == "false" ? false : true);
                person.出生日期 = txtBirthDay.Text.Trim().Length == 0 ? null : (DateTime?)DateTime.Parse(txtBirthDay.Text.Trim());
                person.出生地址 = txtBirthPlace.Text.Trim().Length == 0 ? null : txtBirthPlace.Text.Trim();
                person.手机号 = txtPhone.Text.Trim().Length == 0 ? null : txtPhone.Text.Trim();
                person.拼音短拼 = txtPinYinShort.Text.Trim().Length == 0 ? null : txtPinYinShort.Text.Trim();
                if (manager.Add(person) == 1)
                    MessageBox.Show("成功插入1行数据.");
            }
            catch (Exception)
            {
                MessageBox.Show("数据有误");
            }
        }

        public MainData MainData;//更新UI界面
        private void Main_Load(object sender, EventArgs e)
        {
            DeliverData deliver = new DeliverData();
            dgvDataShow.DataSource = deliver.GetMainData();
        }

        private void dgvDataShow_RowEnter(object sender, DataGridViewCellEventArgs e)
        {//获取用户当前选择的行
            ((DataGridView)sender).Rows[e.RowIndex].Cells[0].ReadOnly = true;
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            string id = dgvDataShow.SelectedRows[0].Cells[0].Value.ToString();
            DeliverData deliver = new DeliverData();
            if (1 == deliver.Delete(id))
                MessageBox.Show("成功删除1行.");
        }

        public List<TblPerson> GetMainData()
        {
            string sql = "select * from Person";
            SqlHelper helper = new SqlHelper();
            SqlDataReader reader = helper.ExecuteDataReader(sql, null);
            if (reader.HasRows)
            {
                List<TblPerson> list = new List<TblPerson>();
                while (reader.Read())
                {
                    TblPerson model = new TblPerson();
                    model.ID = int.Parse(reader[0].ToString());
                    model.姓名 = reader.GetString(1);
                    model.身份证号 = reader[2].ToString();
                    model.年龄 = reader.IsDBNull(3) ? null : (int?)reader.GetInt32(3);
                    model.性别 = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
                    model.出生日期 = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
                    model.出生地址 = reader.IsDBNull(6) ? string.Empty : reader[6].ToString();
                    model.手机号 = reader.IsDBNull(7) ? "" : reader.GetString(7);
                    model.拼音短拼 = reader.IsDBNull(8) ? string.Empty : reader[8].ToString();
                    list.Add(model);
                }
                return list;
            }
            return null;
        }

        public int Add(TblPerson person)
        {
            if (person != null)
            {
                string sql = "insert into Person values(@name,@idCar,@age,@gender,@birthday,@birthplace,@phone,@pinyinShort)";
                SqlParameter[] param = {new SqlParameter("@name",person.姓名),
                                           new SqlParameter("@idCar",person.身份证号),//这里注意：：号两边的类型要可以转换
                                           new SqlParameter("@age",person.年龄==null?DBNull.Value:(object)person.年龄),
                                           new SqlParameter("@gender",person.性别==null?DBNull.Value:(object)person.性别),
                                           new  SqlParameter("@birthday",person.出生日期==null?DBNull.Value:(object)person.出生日期),
                                           new SqlParameter("@birthplace",person.出生地址==null?DBNull.Value:(object)person.出生地址),
                                           new  SqlParameter("@phone",person.手机号==null?DBNull.Value:(object)person.手机号),
                                           new SqlParameter("@pinyinShort",person.拼音短拼==null?DBNull.Value:(object)person.拼音短拼)
                                       };
                SqlHelper helper = new SqlHelper();
                return helper.ExecuteNonQuery(sql, param);
            }
            return 0;
        }

        internal TblPerson Select(string idCar)
        {
            string sql = "select * from Person where idCar=@idCar";
            SqlDataReader reader = new SqlHelper().ExecuteDataReader(sql, new SqlParameter("@idCar", idCar));
            if (reader.HasRows)
            {
                TblPerson person = new TblPerson();
                while (reader.Read())
                {
                    person.姓名 = reader.GetString(1);
                    person.年龄 = reader.IsDBNull(3) ? null : (int?)reader.GetInt32(3);
                    person.性别 = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
                    person.出生日期 = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
                    person.出生地址 = reader.IsDBNull(6) ? null : reader[6].ToString();
                    person.手机号 = reader.IsDBNull(7) ? null : reader[7].ToString();
                    person.拼音短拼 = reader.IsDBNull(8) ? null : reader[8].ToString();
                }
                return person;
            }
            return null;
        }

        internal int Delete(string id)
        {
            string sql = "delete from Person where id=@id";
            SqlHelper helper = new SqlHelper();
            return helper.ExecuteNonQuery(sql, new SqlParameter("id", id));
        }

        internal int Modify(TblPerson person)
        {
            if (person != null)
            {
                string sql = "update Person set name=@name,idCar=@idCar,age=@age,gender=@gender,birthday=@birthday,birthplace=@birthplace,phone=@phone,pinyinShort=@pinyinShort where id=@id";
                SqlParameter[] param = {new SqlParameter("@id",person.ID),
                                           new SqlParameter("@name",person.姓名),
                                           new SqlParameter("@idCar",person.身份证号),//这里注意：：号两边的类型要可以转换
                                           new SqlParameter("@age",person.年龄==null?DBNull.Value:(object)person.年龄),
                                           new SqlParameter("@gender",person.性别==null?DBNull.Value:(object)person.性别),
                                           new  SqlParameter("@birthday",person.出生日期==null?DBNull.Value:(object)person.出生日期),
                                           new SqlParameter("@birthplace",person.出生地址==null?DBNull.Value:(object)person.出生地址),
                                           new  SqlParameter("@phone",person.手机号==null?DBNull.Value:(object)person.手机号),
                                           new SqlParameter("@pinyinShort",person.拼音短拼==null?DBNull.Value:(object)person.拼音短拼)
                                       };
                SqlHelper helper = new SqlHelper();
                return helper.ExecuteNonQuery(sql, param);
            }
            return 0;
        }