通讯录管理

SqlHelper

 

namespace 通讯录管理
{
    using System.Data;
    using System.Data.SqlClient;
    using System.Reflection; //反射的命名空间
    class SqlHelper
    {
       
        static string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

       
        public static DataTable ExecuteTable(string sql,params SqlParameter[] ps)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, connStr);
            //添加参数到SelectCommand命令对象，因为在Fill方法的时候使用的就是SelectCommand
            da.SelectCommand.Parameters.AddRange(ps);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
      

       
        通过PhoneNum表获取PhoneNum实体类集合
        public static List<PhoneNum> GetPhoneNumList(DataTable dt)
        {
            List<PhoneNum> lists = null;
            if (dt.Rows.Count > 0) //说明有数据
            {
                lists = new List<PhoneNum>();
                foreach (DataRow row in dt.Rows)
                {
                    //每一行数据就是一个实体对象
                    PhoneNum temp = new PhoneNum();
                    temp.PId = (int)row["PId"];
                    temp.PTypeId = (int)row["PTypeId"];
                    if (!(row["PName"] is DBNull))
                    {
                        temp.PName = row["PName"].ToString();
                    }
                    temp.PCellPhone = row["PCellPhone"].ToString();
                    temp.PHomePhone = row["PHomePhone"].ToString();
                   
                    temp.PtName = row["ptName"].ToString();
                  
                    lists.Add(temp);
                }
            }
            return lists;
        }
      
     获取PhoneType的实体类集合 +static List<PhoneType> GetPhoneTypeList(DataTable dt)
       
        public static List<PhoneType> GetPhoneTypeList(DataTable dt)
        {
            List<PhoneType> lists = null;
            if (dt.Rows.Count > 0)
            {
                lists = new List<PhoneType>();
                foreach (DataRow row in dt.Rows)
                {
                    PhoneType temp = new PhoneType();
                    temp.PtId = (int)row["PtId"];
                    temp.PtName = row["PtName"].ToString();
                    lists.Add(temp);
                }
            }
            return lists;
        }
       
       
        public static int ExecuteNonQuery(string sql, params SqlParameter[] ps)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                //添加参数
                comm.Parameters.AddRange(ps);
                return comm.ExecuteNonQuery();
            }
        }
     

       
    }
}

 

 

From

 

namespace 通讯录管理
{
    public partial class FrmPhoneManager : Form
    {
        public FrmPhoneManager()
        {
            InitializeComponent();
           
            this.dgvList.AutoGenerateColumns = false;
        }

        private void FrmPhoneManager_Load(object sender, EventArgs e)
        {
            LoadDGVData();
            //加载操作区域下拉列表控件的数据
            this.cboGroup.DisplayMember = "ptname";
   //为下拉列表控件的每一项设置一个隐藏在项背后的实际值
            this.cboGroup.ValueMember = "ptid";
            DataTable TypeTB = SqlHelper.ExecuteTable("select ptid,ptname from phonetype");
            this.cboGroup.DataSource = SqlHelper.GetPhoneTypeList(TypeTB);
          
            //加载查询区域下拉列表控件的数据
            this.cboSeaGroup.DisplayMember = "ptname";
            this.cboSeaGroup.ValueMember = "ptid";
           
            List<PhoneType> typeList = SqlHelper.GetPhoneTypeList(TypeTB);
            typeList.Insert(0, new PhoneType() {PtId=-1,PtName="请选择"});
            this.cboSeaGroup.DataSource = typeList;
          
        }
        #region 加载dgv控件的数据 +void LoadDGVData()
        /// <summary>
        /// 加载dgv控件的数据
        /// </summary>
        private void LoadDGVData()
        {
           
            string sql = "select pId, pTypeId, pName, pCellPhone, pHomePhone,phonetype.ptName from PhoneNum,PhoneType where PhoneNum.pTypeId=PhoneType.ptId";
            DataTable dt = SqlHelper.ExecuteTable(sql);//获得目标数据表
            this.dgvList.DataSource = SqlHelper.GetPhoneNumList(dt);//把表的数据转换成集合对象
          
        }
      

       //点击 选择控件的某一行(只需要某一个单元格)数据触发
        private void dgvList_CellClick(object sender, DataGridViewCellEventArgs e)
        {
           
            PhoneNum temp = this.dgvList.CurrentRow.DataBoundItem as PhoneNum;
            txtCellPhone.Text = temp.PCellPhone;
            txtHomephone.Text = temp.PHomePhone;
            txtName.Text = temp.PName;
           
            cboGroup.SelectedValue = temp.PTypeId; //ValueMenber:实际值

          
            dgvList.Tag = temp.PId;
            isSelected = true;
        }
        bool isSelected = false;

        //在做新增或者修改的时候不可能根据用户的输入操作去生成新的sql语句，因为根据排列组合，可能性太多。所以不现实。一般是新增或者修改所有列的值。只是根据用户输入的值去确定是否使用用户的值或者DBNull.Value
        private void btnSave_Click(object sender, EventArgs e)
        {
            if (isSelected==false)
            {
                return;
            }
           
            string sql = "update phonenum set ptypeid=@ptypeid,pName=@pName,pcellPhone=@pcellPhone,phomephone=@phomephone wherepid=@pid";
            SqlParameter[] ps = {
           
           new SqlParameter("ptypeid", (this.cboGroup.SelectedItem as PhoneType).PtId),
           new SqlParameter("pName", this.txtName.Text.Trim()),
           new SqlParameter("pcellPhone", this.txtCellPhone.Text.Trim()),
            new SqlParameter("phomephone", this.txtHomephone.Text.Trim()),
           new SqlParameter("pid", this.dgvList.Tag)//从tag值中将PID值取出使用
            };
            int num = SqlHelper.ExecuteNonQuery(sql, ps);
            if (num == 1)
            {
                MessageBox.Show("修改成功");
                //记住当前用户所选择的数据行索引
                int index = this.dgvList.CurrentRow.Index;
                LoadDGVData(); //会默认选择第一行
                if(dgvList.Rows.Count>index)
                {
                    //让相应的行被选择
                    this.dgvList.Rows[index].Selected = true;
                }
                isSelected = false;
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string sql = "insert into phonenum values(@ptypeid,@pName,@pcellPhone,@phomephone)";
            SqlParameter[] ps = { 
                                    new SqlParameter("ptypeid", (this.cboGroup.SelectedItem as PhoneType).PtId),
            new SqlParameter("pName", this.txtName.Text.Trim()),
            new SqlParameter("pcellPhone", this.txtCellPhone.Text.Trim()),
            new SqlParameter("phomephone", this.txtHomephone.Text.Trim())
                                };
            int num=SqlHelper.ExecuteNonQuery(sql,ps);
            if (num == 1)
            {
                MessageBox.Show("修改成功");
                LoadDGVData();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            string sql = "delete from phonenum where pid=@pid";
            SqlParameter p = new SqlParameter("pid", dgvList.Tag);
            int num = SqlHelper.ExecuteNonQuery(sql, p);
            if (num == 1)
            {
                MessageBox.Show("删除成功");
                LoadDGVData();
            }

        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            string sql = "select pId, pTypeId, pName, pCellPhone, pHomePhone,phonetype.ptName from PhoneNum,PhoneType where PhoneNum.pTypeId=PhoneType.ptId";
            //创建一个集合，动态接收用户的参数
            List<SqlParameter> ps = new List<SqlParameter>();
            //根据用户的查询输入  拼接 sql查询命令
            if(cboSeaGroup.Text!="请选择") //说明用户选择了分组名称
            {
                sql = sql + " and pTypeId=@pTypeId";
                ps.Add( new SqlParameter("pTypeId", this.cboSeaGroup.SelectedValue));
            }
            if(!string.IsNullOrEmpty(txtSeaName.Text.Trim())) //说明用户选择了姓名进行模糊查询
            {
                //sql = sql + "    and pName like  '%'+@name+'%'                  ";
                //ps.Add(new SqlParameter("name",txtSeaName.Text.Trim()));
                //建议：
                sql = sql + " and pName like @name";
                ps.Add(new SqlParameter("name","%"+txtSeaName.Text.Trim()+"%"));
            }
            DataTable dt = SqlHelper.ExecuteTable(sql, ps.ToArray());
            this.dgvList.DataSource = SqlHelper.GetPhoneNumList(dt); //SqlHelper.GetList<PhoneNum>(dt);
        }
    }
}