第五天 -- 《2014-07-11 ADO》2 -- 一个电话簿小项目体现：多表连接、CRUD、控件绑定等等

一、下午《01、多表连接》--《05、返回实体类集合》

1、数据库的连接字符串应该放到哪里？

　　因为一个项目中有可能多个类代码都会去操作数据库，或者一个解决方案中多个项目去操作数据库（当然我们真正开发时，会分层，会有架构，减少或避免这种情况）。所以（应用在单机、无并发小程序里）我们一般把连接字符串（字段）封装到SqlHelper这样的帮助类里面去，该类从配置文件中读取连接字符串内容。并且该类封装了需要的CRUD操作。其实写SqlHelper类，关键的方法也就三个：增删改（返回受影响行数）、检索结果集（可以多行多列）、返回单个值（只读取首行首列）。

另外：写SqlHelper类遇到的一些问题和错误，详见《不该活着的SqlHelper和DBHelper》

 

<1>将连接字符串放到.config配置文件中。（以Winform的App.config为例）

<configuration>
  <!--需要创建一个能够保存连接字符串的节点-->
  <connectionStrings> <!--  加s说明可以在里面创建多种数据库连接字符串-->
    <!--添加两个连接字符串-->
    <add name="connStr" connectionString="Data Source=.;Initial Catalog=MySchool;Integrated Security=True"/>
    <add name="connStr1" connectionString="Data Source=.;Initial Catalog=MySchool;uid=sa;pwd=123"/>
  </connectionStrings>
</configuration>

 

<2>在SqlHelper中读取配置文件，获取连接字符串

//1、先引用System.Configuration.dll程序集

using System.Configuration;

...

public class SqlHelper
{
    public static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; 
    
    //执行查询结果集
    public static DataTable ExecuteTable(string sql, params SqlParameter[] ps)
    {
        SqlDataAdapter da = new SqlDataAdapter(sql, connStr);
        DataTable dt = new DataTable();
        //添加参数
        da.SelectCommand.Parameters.AddRange(ps);
        da.Fill(dt);
        return dt;
    } 
    
...
}

 

2、使用实体类，并将DataTable对象转List<实体类>

这种转换方法一般就直接放在SqlHelper类中了。如下所示：（实体类略）

public class SqlHelper
{
...
    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"];
                
                if(!(row["Ptypeid"] is DBNull))
                {//如果Ptypeid列不是数据库null的话，再转换。
                    temp.Ptypeid = (int)row["Ptypeid"];
                }
                
                //注意System.DBNull.ToString()返回String.Empty
                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;
    } 

...
}

 

 

二、下午《06、dgv控件的属性设置》--《07、绑定下拉列表的数据》

Winform控件DataGridView、ComboBox、ListView、TreeView的数据绑定相关，详见其链接。

1、《电话簿项目》UI 和 数据库表结构

                       

2、窗体 FrmPhoneManager.cs 代码

<1>窗体加载 和 查询

//窗体加载时
private void FrmPhoneManager_Load(object sender, EventArgs e)
{
    #region 绑定dgv控件的数据
    string sql = @"select pid, ptypeid, pname, pcellphone, phomephone, ptname 
                    from phonetype,phonenum where PhoneNum.pTypeId=PhoneType.ptId";//内连接
    DataTable dt = SqlHelper.ExceuteTable(sql);
    this.dgvList.DataSource = SqlHelper.GetPhoneNumList(dt);//将DataTable转为实体对象列表
    #endregion

    #region 绑定下拉列表的数据
    DataTable dttype = SqlHelper.ExceuteTable("select ptid, ptname from phonetype");
    this.cboGroup.DisplayMember = "ptname";//下拉列表显示每个实体的ptname属性值
    this.cboGroup.ValueMember = "ptid";//选中值SelectValue为实体的ptid属性值
    this.cboGroup.DataSource = SqlHelper.GetPhoneTypeList(dttype);
    
    //这里实际上可以将上面一行集合拷贝一份为下面控件做绑定。但不可以两个下拉列表控件绑定同一个集合，否则选择的时候两个控件一起变动。
    List<PhoneType> lists = SqlHelper.GetPhoneTypeList(dttype);
    //在集合中添加“请选择”
    lists.Insert(0, new PhoneType() { Ptid = -1, Ptname = "请选择" });//插入此项为了去除该列的检索条件
    this.cboSeaGroup.DisplayMember = "ptname";
    this.cboSeaGroup.ValueMember = "ptid";
    this.cboSeaGroup.DataSource = lists;
    #endregion
} 

private void btnSearch_Click(object sender, EventArgs e)
{//点击查询按钮后
    string sql = @"select pid, ptypeid, pname, pcellphone, phomephone, ptname 
                    from phonetype, phonenum where PhoneNum.ptypeid = PhoneType.ptid";
    List<SqlParameter> ps = new List<SqlParameter>();
    if(this.cboSeaGroup.Text != "请选择")
    {//如果用户做出了联系人组的筛选
        sql = sql + " and ptypeid = @id";
        ps.Add( new SqlParameter("id", this.cboSeaGroup.SelectedValue));//因为下拉列表选中值就是联系人组的Id
    }

    if(!string.IsNullOrEmpty(txtSeaName.Text.Trim()))
    {//如果用户输入了姓名
        //模糊查询的%不是在sql语句中添加的
        sql = sql + " and pname like @pname";
        //而是在参数中添加的
        ps.Add(new SqlParameter("pname", "%" + txtSeaName.Text.Trim() + "%"));
    }
    //如果用户输入了号码筛选条件，处理同上。
  
    DataTable dt = SqlHelper.ExceuteTable(sql, ps.ToArray());
    this.dgvList.DataSource = SqlHelper.GetPhoneNumList(dt);
}

 注意：

（1）ComboBox控件绑定数据源（DataTable或实体集合）之前，最好指定DisplayMember和ValueMember属性。也就是选项应该显示啥，选中后的值是啥。

（2）为ComboBox的数据源插入第一个元素，用来除去此筛选条件。一般第一个元素显示为 “请选择” 或者  “All”

（3）此例中ComboBox控件是绑定数据源的（而不是手动Add项），所以不用担心它会多次加载重复项。（如果是手动Add项，那么每次重新添加前，要先Clear掉）

（4）因为（1）中设定了ValueMember属性，需要时就可以读取ComboBox的SelectedValue属性来得到当前选择项，对应的ptid值（PhoneType表的ptid列）。

（5）参数化查询的字符串模糊查询，不是在sql中添加通配符，而是加到参数值中。

　　如sql中“ and  pname like  @pname”   

　　　ps.Add(new SqlParameter("pname", "%"+  "模糊查询的输入文本"  + "%"));

 

<2>选中表格的行时，将各列值显示到下面可编辑的控件内。

private void dgvList_Click(object sender, EventArgs e)
{//表格被点击后
    if (this.dgvList.SelectedRows.Count == 0)
    {//如果没有行被选中，不处理
        return;
    }

    //因为DataGridView绑定的数据源是实体对象集合，所以每行绑定了一个实体对象
    PhoneNum obj = this.dgvList.CurrentRow.DataBoundItem as PhoneNum;
    txtCellPhone.Text = obj.Pcellphone;
    txtHomePhone.Text = obj.Phomephone;
    txtName.Text = obj.Pname;
    
    //将pid值存储到控件的TAG值中，这样做方便后面保存时Update
    this.dgvList.Tag = obj.Pid;
    //cboGroup.Text = obj.Ptname;//推荐使用下面方式显示Ptname，因为更合理
    cboGroup.SelectedValue = obj.Ptypeid;//指定SelectedValue，可以自动选中对应ComboBox项
}

注意：

（1）判断DataGridView是否有行被选中，可以用以下两种方式

　　if (this.dgvList.SelectedRows.Count == 0)
　　或if (this.dgvList.CurrentRow != null)

（2）本例控件DataGridView绑定的数据源是实体对象集合（这么做后面处理很方便），所以每个行的DataBoundItem属性就是绑定的实体对象。

（3）可以每次在用户点选某行后，把行绑定的实体的主键id存到DataGridView.Tag（这么做后面保存Update时很方便）

（4）可以为ComboBox的SelectedValue赋值（由于之前指定了它的ValueMember属性），使得控件自动选中对应项。

 

<3>点击保存，Update行记录

private void btnSave_Click(object sender, EventArgs e)
{//点击保存按钮，将文本控件内容Update到数据库

    string sql1 = @"update phonenum set ptypeid = @ptypeid ,pname = @pname, 
                    pcellphone = @pcellphone, phomephone = @phomephone where pid = @pid";

    SqlParameter[] ps = {   //因为ComboBox绑定的数据源是实体对象集合，所以每个项都是个实体对象。此处用SelectedValue也可以（就不用转换了）。
                            new SqlParameter("ptypeid",(this.cboGroup.SelectedItem as PhoneType).Ptid),
                            new SqlParameter("pname",txtName.Text.Trim()),
                            new SqlParameter("pcellphone",txtCellPhone.Text.Trim()),
                            new SqlParameter("phomephone",txtHomePhone.Text.Trim()),
                            new SqlParameter("pid",this.dgvList.Tag)//因为之前将pid存到Tag里了
                         };

    if (SqlHelper.ExecuteNonQuery(sql1, ps) == 1)
    {//如果更新OK，重新加载所有记录
        MessageBox.Show("ok");
        string sql2 = @"select pid, ptypeid, pname, pcellphone, phomephone, ptname 
                        from phonetype, phonenum where PhoneNum.pTypeId = PhoneType.ptId";//内连接
        DataTable dt = SqlHelper.ExceuteTable(sql2);
        this.dgvList.DataSource = SqlHelper.GetPhoneNumList(dt);
    }

}

 

3、SqlHelper类中的其余重要代码

public static List<PhoneType> GetPhoneTypeList(DataTable dt)
{//DataTable转phoneType集合
    List<PhoneType> lists = null;
    if (dt.Rows.Count > 0)
    {
        lists = new List<PhoneType>();
        foreach (DataRow row in dt.Rows)
        {
            PhoneType pty = new PhoneType();
            pty.Ptid = (int)row["Ptid"];
            pty.Ptname = row["Ptname"].ToString();
            lists.Add(pty);
        }
    }
    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();
    }
}

 

 

三、《2014-07-12 SQL进阶》上午《01、回顾》

1、可选的多条件检索时，where 1=1的问题。

　　多条件检索时，一般UI上有多个可选条件供用户选择，这样的sql语句就需要根据用户的选择进行多条件的拼接。这时很多人喜欢先写一个where 1=1 在前面，然后根据用户的选择在后面拼接 and XXX=@XX。然而这种方式并不高效。

　　如果使用这种方式，在数据库中会做全表扫描（对每行数据都进行扫描，比对。），会无法使用索引等优化查询的策略。建的索引将会失效。解决办法如下所示。

//解决办法：
string sql = "select * from TblStudent";
bool hasWhere = false;
if (!string.IsNullOrEmpty(textBox1.Text.Trim()))
{
    if (!hasWhere)
    {
        sql += " where col1 = txt1";
        hasWhere = true;
    }
    else
    {
        sql += " and col1 = txt1";
    }
}

if (!string.IsNullOrEmpty(textBox2.Text.Trim()))
{
    if (!hasWhere)
    {
        sql += " where col2 = txt2";
        hasWhere = true;
    }
    else
    {
        sql += " and col2 = txt2";
    }
}

if (!string.IsNullOrEmpty(textBox3.Text.Trim()))
{
    if (!hasWhere)
    {
        sql += " where col3 = txt3";
        hasWhere = true;
    }
    else
    {
        sql += " and col3 = txt3";
    }
}

if (!string.IsNullOrEmpty(textBox4.Text.Trim()))
{
    if (!hasWhere)
    {
        sql += " where col4 = txt4";
        hasWhere = true; 
    }
    else
    {
        sql += " and col4 = txt4";
    }
}

MessageBox.Show(sql);

注意：当然拼接sql的时候，最好使用StringBuilder类