第三天 -- 《2014-07-08 ADO》2 --ADO.NET的基本操作

一、《连接字符串》

1、连接字符串（下面是使用各家的.NET连接器）

（1）SQLServer主要的几种

　　使用sa账户密码连接本地： “server=.;uid=sa;pwd=123;database=SchoolDb”

　　使用用户名密码连接远程： "Data Source=192.168.1.123;Initial Catalog=SchoolDb;User Id=myUsername;Password=myPassword;"

　　使用Windows验证登录本地：“Data Source=.;Initial Catalog=SchoolDb;Integrated Security=True”

 

（2）MySQL（默认端口3306）

　　标准连接：“Data Source=192.168.1.123;Database=SchoolDb;User ID=root; Password=123;”

　　本地连接：“Data Source=localhost;Database=SchoolDb;User ID=root; Password=123;”

　　　　或　　“server=localhost;Database=SchoolDb;Uid=root;Pwd=123”

 

　　指定端口：“Server=192.168.1.123;Port=1234;Database=SchoolDb;Uid=myUsername;Pwd=123;”

 

（3）Oracle

　　标准连接：“Data Source=SchoolDb;User Id=myUsername;Password=myPassword;”

 

（4）SQLite

　　标准连接：“Data Source=filename;Version=3;Password=myPassword;”

 

（5）Mongodb（默认端口27017）

　　标准连接本地：“mongodb://localhost/SchoolDb”

　　指定密码：　　“mongodb://feng:1234@localhost/SchoolDb”

　　指定IP和端口：“mongodb://feng:1234@192.168.1.123:19191/SchoolDb”

 

（6）Excel或Access等

 　　Access使用OLE DB, OleDbConnection (.NET)方式：

　　“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydb.mdb; User Id=haha;Password=123;”

 　　

　　Excel使用OLE DB方式：

　　“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;”

 

二、上午  《05、ado的操作流程和原理》-- 《07、方法只是用来接收适合的返回值》

1、ado.net几个关键类

Connection. 用于连接和管理针对数据库的事务。　　　　　　　　　　　　　　　　（MSSQL对应SqlConnection）
Command. 用于发出针对数据库的SQL指令。　　　　　　　　　　　　　　　　　  （MSSQL对应SqlCommand）
DataReader. 用于从SQL Server数据源读取只进流的数据记录。　　　　　　　　　　（MSSQL对应SqlDataReader）
DataSet. 用于针对结构型数据，XML数据和关系型数据的存储，远程处理和编程。
DataAdapter. 用于推送数据到DataSet，并针对数据库协调数据。　　　　　　　　　　（MSSQL对应SqlDataAdapter）

 

 

2、ado.net的一般流程

//加入using
using System.Data;
using System.Data.SqlClient;

 

（1）若是增删改

　　创建db连接——打开连接——写sql语句——创建命令——[创建命令参数——向命令添加参数]——执行命令——获得返回值——关闭连接

关键方法：SqlCommand.ExecuteNonQuery()  //返回受影响的行数

string connStr = "server=.;uid=sa;pwd=123;database=SchoolDB"
using (SqlConnection conn = new SqlConnection(connStr)) 　　　　　　　　　　　　　　　　　　　　　 //创建db连接
{
    conn.Open();　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　//打开连接
    string sql = string.Format("insert into grade values('{0}');", "高一一班");　　　　　　　　  //写sql语句
    using (var cmd = new SqlCommand(sql, conn))　　　　　　　　　　　　　　　　　　　　　　　　　　  //创建命令
    {
        int count = cmd.ExecuteNonQuery();　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　//执行命令，获得返回值
        if (count == 1)
        {
            Console.WriteLine("insert OK");
        }
        else
        {
            Console.WriteLine("insert fail");
        }
     }
} //关闭连接          

 

 

（2）查询单个值的结果集（即结果集首行首列）

　　创建db连接——打开连接——写sql语句——创建命令——[创建命令参数——向命令添加参数]——执行命令——获得返回值——关闭连接

关键方法：SqlCommand.ExecuteScalar()  //返回结果集的首行首列值(object对象)

string connStr = "Data Source=.;Initial Catalog=SchoolDB;Integrated Security=True";　　　　　　　　　　//使用Windows验证

using(SqlConnection conn=new SqlConnection(connStr))　　　　　　　　　　　　　　　　　　　　　　　　　　　　//创建db连接
{
    conn.Open();　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　//打开连接
    string sql = string.Format("insert into grade values('{0}'); select @@identity ", "高一二班"); 　 //写sql
    SqlCommand cmd = new SqlCommand(sql, conn);　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　//创建命令
    object  cid  = cmd.ExecuteScalar();　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 //执行命令，获得返回值(即结果集的首行首列)
    
    Console.WriteLine("标识列值是：" + cid);
}

 

（3）DataRead查询结果集（即多行多列）

　　创建db连接——打开连接——写sql语句——创建命令——[创建命令参数——向命令添加参数]——执行命令——获得DataReader——使用DataReader循环读取行——关闭连接

关键方法：SqlCommand.ExecuteReader()  //返回一个读取器，可以使用读取器遍历结果集每一行

string connStr = "server=.;uid=sa;pwd=feng;database=CMA_M";
using (SqlConnection conn = new SqlConnection(connStr))　　　　　　　　　　　　　　//创建连接
{
    conn.Open();　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 //打开连接
    string sql = "select * from ProductInfo where id < 100";　　　　　　　　　　  //写sql语句
    using (SqlCommand cmd = new SqlCommand(sql,conn))　　　　　　　　　　　　　　　 //创建命令
    {
        using (SqlDataReader reader = cmd.ExecuteReader())　　　　　　　　　　　　 //执行命令，获取DataReader
        {
            while(reader.Read())　　　　　　　　　　　　　　　　　　　　　　　　　　　 //使用DataReader循环读取下一行
            {
                int id = (int)reader["id"];　　　　　　　　　　　　　　　　　　　　　//通过列名找到当前行的一个列值
                int swift_no = (int)reader["swift_no"];
                string product_name = reader["product_name"].ToString();

                Console.WriteLine("id:{0},swift:{1},name:{2}", id, swift_no, product_name);
            }
        }
        
    }
}

 

（4）DataAdapter查询结果集（即多行多列）

 DataAdapter实际上封装了Connection、Command 和 DataReader

数据适配器介绍详见下一节。这里先给出简单例子

DataSet ds = new DataSet();

using (SqlConnection conn = new SqlConnection(connStr))　　　　　　　　//创建连接
{
    SqlDataAdapter adapter = new SqlDataAdapter();　　　　　　　　　　  //创建适配器
    adapter.SelectCommand = new SqlCommand(sql, conn);　　　　　　　　 //为适配器创建查询命令（因为下一行就要查询数据）
    adapter.Fill(ds);　　　　　　　　　　　　　　　　　　　　　　　　　　　　//查询数据，填充到本地内存（填充到dataset中）
}

 

 

 

三、下午  《01、回顾》-- 《04、ADO的几个常用类》

1、当需要增删改、查询单个值（首行首列值）时

　　通过SqlCommand直接将sql交给数据库执行，并获得返回值。

2、当需要查询结果集（多行多列）时

　　（1）使用DataReader“在线”读取数据

　　执行SqlCommand.ExecuteReader() 返回一个DataReader对象（实际上SQLServer服务器此时将sql查询的结果集存到服务器缓存上了），应用程序可以循环调用DataReader.Read()方法，每次从服务器缓存中读取下一行记录。

 

　　（2）使用DataAdapter和DataSet“离线”读取数据

　　执行DataAdapter.Fill()方法，将sql查询的结果集一次性全部读出来（从服务器中获得），并填充到本地DataSet对象中。

 

3、查询数据小结：（DataReader方式和DataAdapter对比）

（1）DataReader

　　特点：对象只允许以只读、顺向的方式读取结果集，一行一行读。

　　优点：提供一个非常有效率的数据查看模式。（因为一次只从服务器中读取一行记录，传输速度快）

　　　　　同时该对象还是一种非常节省资源的数据对象。（也因为每次只能读一行，占用客户端内存小，读取占用网络带宽少）

　　缺点：占用服务器缓存（因为sql查询的结果集先缓存在服务器上，而DataReader每次只能读走一行）

　　　　   循环读取时，必须一直占用Connection连接。在其获得数据过程中其它操作不可再使用该Connection连接对象。

　　大忌：分布式应用中存在高并发，DataReader读到数据应迅速处理，尽快将数据存到本地内存（比如while循环读取，存入List或者DataSet中，至于绑定到控件应该是后两者跟控件应该发生的关系，而非DataReader），操作完毕关闭连接。

　　切忌将DataReader直接绑定到控件（如WebForm的Repeater控件），因为服务端控件列表渲染出表格的周期通常比较长，所以只有等到最后结果列表出来的时候（也就是服务器将Repeater控件包括其绑定的数据翻译成html表格文本后），最后一行数据才读完。（读一行生成一个标签）。因此连接是持续相当长的打开状态，对于web这种并发多的情况，狂点几下,估计就报错了，连接池用满了。用DataReader绑定列表控件就等于耍流氓。

 

（2）DataAdapter结合DataSet

　　特点：一次性读取所有结果集。

　　优点：DataAdapter会将数据一次性读取，并存入内存DataSet中，然后断开连接，这时其它操作就可以使用该Connection连接对象。

　　　　   一次性读取，提高了灵活性，在断开数据库连接情况下你可以对本地内存（DataSet对象）中数据进行任何增删改查，可按照任意的顺序读取数据，

　　　　   还可以将其写回到数据库等。

　　缺点：一次性读取，瞬间占用一定带宽，

　　　　　更多地占用客户端内存（与DataReader方式比较）

（3）综上所述，各有优缺点。但后者方式简单好用，节省服务器资源，关键是更加灵活。所以ADO.NET查询数据时，更多地使用后者。

　　事实上DataAdapter类中封装了Connection、Command、DataReader。

 

 

四、下午  《05、连接池》--   连接的关闭与清理，以及连接池

1、Connection连接的关闭与清理

（1）微软MSDN推荐的关闭与清理方式有两种：

//方式一
public void DoSomeWork()
{
  SqlConnection conn = new SqlConnection(connectionString);
  …try
  {
    conn.Open();
    // Do Work

  }
  catch (Exception e)
  {
    // Handle and log error
  }
  finally
  {
    if(null!=conn)
       conn.Close();
  }
}

//方式二
using (SqlConnection conn = new SqlConnection(connString))
{
  conn.Open();
  . . .
} // Dispose is automatically called on the conn variable here

对比一下，显然第二种简洁（更重要地不怕忘记关闭连接），第一种可以处理异常更灵活。所以可以根据需要，自己选择。

那么连接的关闭Close()方法，与清理Dispose()方法到底有什么区别：

 

（2）连接的关闭与清理

我们先来看一下Connection.Dispose()的实现：

public void Dispose()
{
    this.Dispose(true);
    GC.SuppressFinalize(this);
}

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

　　<1>可以看到，调用Dispose()方法，它会自动调用Close()方法。

　　<2>Dispose()方法不仅执行了Close()，而且它把连接字符串清空了。也就是说如果执行了Dispose()方法，下次再Open()连接时会报异常“ConnectionString 属性尚未初始化”。这时必须重新new SqlConnection(connStr)，或者重新指定连接字符串。

　　<3>再说了Dispose()方法在.NET里面压根没有释放对象的意思和功能，释放对象那是CLR-GC做的事情。Dispose()只是在GC释放对象之前做好对象所占用资源的清理工作。

详见《.NET中对非托管资源的释放机制》

　　<4>事实上Connection.Close()，并不是真正地把数据库连结给关闭了，只是把连接放回到连接池而已。这一点，很多人会误以为调用了Close()就真把连接关闭了。

当需要再次访问数据库的时候，可以再次Open()打开连接。（除非连接已经Dispose()了，参见上面<2>内容）

 

2、连接池

　　（1）基本概念

　　.net 中通过Connection连接sql server服务，我们发现第一次连接时很耗时，但后面连接会很快，这和Connection的连接池机制有关，正确理解连接池机制，有助于我们编写高效的数据库应用程序。

 　　物理连接建立时，需要和服务器握手，服务器要解析连接字符串，检查授权、约束等等，物理连接建立之后，这些操作就不用再做了，这些操作需要消耗一定的时间。所以很多人喜欢用一个静态Connection对象来保持物理连接，但采用静态对象，多线程访问时会带来一些问题（必须要处理好线程同步）。实际上我们完全不必这么做，因为Connection 默认会打开连接池功能，当调用Connection.Close()后，物理连接并不会被立即释放，所以这才出现循环执行Open操作时，平均执行时间几乎为0.

　　另外，连接池有一个超时时间（默认5-10分钟），如果在此期间没有任何的连接操作，物理连接就会被关闭。当再次有连接操作时，连接池会寻找其他空闲的可用物理连接，没有的话就重新建立物理连接。

　　

　　（2）连接池如何应对多线程

　　如果是多线程连接数据库 ，每次都重新new SqlConnection()，那么这里有两个问题：

　　<1>如果后一个线程在前一个线程Close前调用了Open，那么 Ado.net 不可能复用一个物理连接，它将为第二个线程分配一个新的物理连接。

　　<2>如果后一个线程 Open  时，前一个线程已经 Close 了，则新的线程使用前一个线程的物理连接。

　　也就是说，如果同时有n个线程连接数据库，最多情况下会创建n条物理连接，最少情况下为1条。

　

　　（3）另外在连接字符串中可以指定：

　　<1>是否启用连接池 Pooling=True;

如："Data Source=192.168.10.2; Initial Catalog=SchoolDB; Integrated Security=True; Pooling=False;"  //不启用连接池

　　<2>指定 Min Pool Size=1 就可以保证连接池中至少保留一个物理连接。（即使没有任何连接操作，也会保留至少一个）如果不指定，这个值默认是0.

如："Data Source=192.168.10.2; Initial Catalog=SchoolDB; Integrated Security=True; Min Pool Size=1;"

 

3、那么，如果不调用Dispose()或者Close()又会怎样呢？
　　

　　当你的程序并不需要频繁地操作数据库的时候，就算忘记调用Dispose或者Close也无伤大雅，因为SqlConnection类定义了析构函数（编译器根据析构函数自动生成override的Finalize方法），而Finalize方法里面又调用了Dispose。所以，最终Finalizer Queue在回收资源的时候也会调用到SqlConnection的Close()方法的。

　　只不过你的代码访问完数据库，最好Close掉（上面微软推荐的两个方式）把连接放回到连接池，以供其他代码使用该连接。

 

 

五、下午  《09、创建读取器对象读取数据》--《10、点击控件显示详细信息》--查询数据显示到WinForm的ListView控件

1、SqlDataReader读取原理

　　该对象只能通过SqlCommand.ExecuteReader()创建并返回，当对象创建后MSSQL服务器会将sql查询结果集放到服务器缓存。读取器（在保持连接情况下）每次Read()都从服务器缓存读取下一行记录（通过封装好的TCP/IP协议）。也就是说缓存中该结果集是不会因为物理表中的数据变化（比如其他客户端同时在做增删改）而改变的。

 

 

2、一个小例子：查询数据显示到WinForm的ListView控件

（1）控件如下：需求是从grade表中读取班级信息，显示到ListView控件，选择行时，把班级名称显示到文本框。修改文本框的班级名称点击修改，马上更新到grade表。

ListView控件名：lvClasses，文本框名：txtClassName，修改按钮名：btnUpdate

<1>ListView控件属性：

View = Details；MultiSelect=false不允许多行选择；FullRowSelect=true（整行选中）；GridLines=true（显示网格线）；Columns添加两个列。

 

（2）主要代码如下：

private void Form1_Load(object sender, EventArgs e)
{
    string connStr = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
    using(SqlConnection conn=new SqlConnection(connStr))
    {
        conn.Open();
        string sql = "select classid,classname from grade";
        //创建命令对象
        SqlCommand comm = new SqlCommand(sql,conn);
        //创建一个读取器对象，这个对象可以从服务器中每一次读取出一行数据
        SqlDataReader reader = comm.ExecuteReader();
        //数据还需要去循环读取
        while (reader.Read())
        {
            //先添加主项，再为主项添加子项
            ListViewItem lv = new ListViewItem(reader[0].ToString());
            //为主项添加子项
            lv.SubItems.Add(reader["classname"].ToString());
            //将主项添加到控件的Items集合
            lvClasses.Items.Add(lv);
        }
    }
}

private void lvClasses_Click(object sender, EventArgs e)
{
    if(this.lvClasses.SelectedItems.Count==0)
    {
        return;
    }
    txtClassName.Tag=  this.lvClasses.SelectedItems[0].SubItems[0].Text;
    txtClassName.Text= this.lvClasses.SelectedItems[0].SubItems[1].Text;
}

private void btnUpdate_Click(object sender, EventArgs e)
{//具体更新代码略
    string sql = string.Format("update grade set classname='{0}' where classid='{1}'" ,
　　　　　　　　　　 　　　txtClassName.Text.Trim(),  txtClassName.Tag.ToString());
}