C#二十五 连接式访问数据库

在连接式访问数据库方式下，数据库就好比一个水池，你要取水池里的水进行操作就必须先建立一条管道，连接就相当于通向数据库的管道。在操作时连接不可以断开，一旦连接断开将造成数据访问的严重错误。

       我们知道真正的水池能建立的管道必定有限，对于数据库也是一样的，同一个数据库能在同一时间接受的连接是有限的。缺点是并发性差，比如针对多并发操作的程序，有点是可以快速高效的操作数据库。

视频课堂https://edu.csdn.net/course/play/7621

C#程序设计及宿舍管理系统实战  

C#程序设计与宿舍管理系统实战-c#宿舍管理系统,c#学生宿舍管理系统-C#视频教程-后端开发-CSDN程序员研修院

重点：

Ø      SqlConnection对象

Ø      SqlCommand对象的增、删、改、查操作

Ø      SqlDataReader对象从数据库读取数据

Ø      资源释放

预习功课：

Ø      SqlConnectioni对象的声明与使用

Ø      SqlCommand对象常用方法

Ø      SqlDataReader对象如何读取数据

Ø      为何要进行数据连接的资源释放

2.1 进水笼头——建立Connection

       Connection表示与数据源之间的连接。可根据Connection对象的各种不同属性来指定数据源的类型、位置及其他属性，可用它来与数据库建立连接或断开连接。其他对象如DataAdapter和Command对象通过它与数据库通信。根据.NET Framework 数据提供程序的不同，也有几种不同的Connection，如针对SQL Server的SqlConnection、针对Oracle的OracleConnection、针对MySQL的MySqlConnection、针对OLEDB的OleDbConnection等。

1．用SqlConnection连接SQL Server

（1）加入命名空间：

1.     using System.Data.SqlClient;

（2）连接数据库：

1.     string conString = "data source=127.0.0.1;Database=codematic;user id=sa;

password=";  

2.     SqlConnection myConnection = new SqlConnection(conString);

3.     myConnection.Open();

2．用OracleConnection连接Oracle

首先添加OracleClient的引用，如图5-5所示。

（1）加入命名空间：

1.     using System.Data.OracleClient;

（2）连接数据库：

1.     string conString = "Data Source=codematic;User ID=codeuser;

Password=user123";  

2.     OracleConnection myConnection = new OracleConnection(conString);

3.     myConnection.Open();

3．用MySqlConnection连接MySQL

       在.NET中连接MySQL数据库有两种方法：MySQL Connector/ODBC 和 MySQL Connector/NET，ODBC连接器是符合ODBC标准的交互平台，是.NET访问MySQL数据库的最好的选择。

首先，我们下载安装MySql-connector-net-5.1.5.Data.msi这个组件。如果是默认安装，则可以在C:\Program Files\MySQL\MySQL Connector Net 5.1.5\Binaries\.NET 2.0（这里安装的是MySQL Connector/Net 5.1.5，老的1.1版本是：C:\Program Files\MySQL\MySQL Connector Net 1.0.4\bin\.NET 1.1\）中找到MySql.Data.dll，将该文件复制到项目的bin目录下。

然后在项目引用中添加MySql.Data.dll的引用，如图5-6所示。

实现代码如下。

（1）加入命名空间：

1.     using MySql.Data.MySqlClient;

（2）连接数据库：

1. string conString = "server=127.0.0.1;database=mysql;user id=root;password=123";  
2. MySqlConnection myConnection = new MySqlConnection(conString); 
3. myConnection.Open(); 
4．用OleDbConnection连接各种数据源

由于数据源不同，相应的连接字符串也会不同。

（1）加入命名空间：

1.     using System.Data.OleDb;

（2）连接SQL Server：

1.     string conString = " Provider=SQLOLEDB.1;Persist Security Info=False;

2.     User ID=sa;Database=Codematic;Data Source=COMPUTER";  

3.     OleDbConnection myConnection = new OleDbConnection(conString);

4.     myConnection.Open();

（3）连接Access（可通过建立.udl文件来获得字符串）：

1.     string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source

2.     =C:\\Database1.mdb;Persist Security Info=False";

（4）连接Oracle（也可通过OracleConnection连接）：

1.     string conString = "Provider=MSDAORA.1;User ID=user; Password=123;

2.     Data Source=db;Persist Security Info=False";

从以上几个对象实例对比来看，几个.NET数据提供程序组件模型的基本编程模式相同，只是组件对象的前缀有所区别，正是这种统一编程模型，让我们在做不同类型数据库开发时，变得非常简单。

下面我们主要来研究SqlConnection连接类的重要属性和方法：

要建立一个数据库的连接，必须给数据库对象足够的关于它要连接的数据库的信息，这些信息包括数据库服务器名称、数据库名称、数据库账户、账户密码等。由这些链接信息组成的连接字符串叫“数据库连接字符串”。格式如下：

string conn=＂server=.;database=NIIT;uid=sa;pwd=123456＂;

   下面来解释下数据库连接字符串：

      server:后面跟数据库服务器的名称；可以为本机地址（.、local、或127.0.0.1）；或对方数据库服务器名称或IP，如：＂ha001.mobile＂、＂224.68.102.86＂。

      database:你要访问的服务器的名称,也可写成＂Initial Catalog＂；

      uid:登录数据库账户的名称，也可写成＂user id＂；

      pwd：登录数据库账户的密码，密码为空时，pwd不能省略，可以这样写＂uid=sa;pwd=＂；

有了数据库连接字符串，就可以直接建立数据库连接对象了：

SqlConnection cn=newSqlConnection(server=.;database=NIIT;uid=sa;pwd=123456＂);

建立了数据库连接后，如果要通过该连接操作数据库里面的数据，还需要打开该连接：

cn.Open();

如果该数据库连接使用完了，可以调用Close()方法关闭该连接,如：

cn.Close();

       要注意每个数据库能接受的连接是有限的，每个连接都可以为一个应用程序服务，所以，数据连接式非常宝贵的资源。因此，在操作数据库连接对象时要注意谨慎，要使用异常结构确保连接使用完后正常释放。

为什么使用异常处理,可能会有如下情况:

      与数据库的连接中断

      无法打开数据库

     无法操作数据

      …

一个完整并且有效的使用数据库连接对象的例子如下：

下：

using System;
using System.Data.SqlClient;
class DataDemo
{
    static void Main(string[]args)
    {
       //通过连接字符串建立数据库连接
SqlConnection cn=newSqlConnection(＂server=.;database=School;uid=sa;pwd=123456;＂);
try
{
    //打开数据库连接
    cn.Open();
    Console.WriteLine(＂连接建立成功!＂);
}catch(SqlException ex)
{
    //要对连接异常做仔细的处理
    Console.WriteLine(“连接数据库出错：＂+ex.Message);
}
finally
{
            //一定要确保数据连接正常释放
            cn.Close();
        }
}
}

    运行结果如果显示＂连接建立成功＂,那么表示你建立的数据库连接是有效的。数据连接一旦建立，我们就可以通过连接对象的State属性获得此连接的状态，连接状态用一个ConnectionState枚举表示，常用的连接状态只有两种：

      Open：表示连接处于打开状态

      Close:表示连接处于关闭状态

我们可以通过判断连接状态来合理的打开或关闭数据库连接，如：

if(cn.State==ConnectionState.Open)

   cn.Close();

if(cn.State==ConnectionState.Closed)

   cn.Open();

       要注意ConnectionState枚举属于System.Data命名空间，在使用这个枚举时别忘了导入这个命名空间。

       Sql Server数据库有两种登录方式，一种是SqlServer账户登录方式，一种是Windows集成登录方式。前面我们已经使用过Sql Server账户登录方式了，还可以通过Windows集成验证方式登录，这种登录会把Windows集成验证的安全证书传给数据库服务器，所以不需要密码，要使用Windows登录验证只需修改数据库连接字符串为如下即可：

SqlConnection cn=new SqlConnection(＂server=.;database=School;uid=sa;pwd=123456;IntergratedSecurity=SSPI＂);

    如前所述，数据库一旦建立，也就是开通了一条存取数据库连接的通道。以数据库连接为基础再结合数据库的操作对象（如下面将介绍的命令对象）就可以存取数据库里的数据了。

2.2 对数据库进行增、删、改、查操作

抽水机：Command

      通过Ado.Net中的Command命令对象可以向数据库服务器发送一个操作命令。这些操作命令包括执行某个SQL语句或调用某个存储过程。有了命令对象我们就可以对数据库进行增、删、改、查等操作了。Command命令对象时建立在数据库连接基础上的，所以在调用命令对象操作数据库时要保证数据库连接已经打开。

      Ado.Net为我们提供SqlCommand类也在System.Data.SqlClient命名空间：

2.2.1使用Command步骤：

1、创建数据库连接

2、定义 SQL 语句

3、创建 Command 对象（执行命令前，必须打开数据库连接！）

4、执行命令

        SqlCommand类为我们提供了两个常用的方法可以执行查询操作，一个方法是ExecuteScalar()方法，它返回一个值；另外一个是ExecuteReader()方法，它返回一个SqlDataReader()类型的结果集。

要使用ExecuteScalar()方法在构造Sql语句时一般要带聚合函数如：

Select Count(*) From Admin  //计算有多少人

Select Average(age) From UserInfo  //计算平均年龄

      ExecuteScalar()方法一般用在某些特殊的情况下，比如用户验证（判断这个用户是否在该数据库中），再比如求平均值。其返回类型为Object类型的，所以，需要对结果进行强制转换才能得到你想到的值。

部分代码：

SqlConnection connection = newSqlConnection(connString);
string sql = "SELECT COUNT(*) FROMStudent";
connection.Open();// 打开数据库连接
SqlCommand command = newSqlCommand(sql, connection);
int num = (int)command.ExecuteScalar();

综合示例

完成系统登录功能

1、验证管理员的用户名和密码是否存在

2、验证通过，显示管理员主窗体

分析：

      处理登录按钮的 Click 事件

      定义一个 ValidateUser() 方法

      需要 Connection 和 Command 对象

      分两步实现验证:

1、建立数据库连接

2、验证用户是否存在

ValidateUser() 方法框架:

ValidateUser() 方法具体实现

第一步：建立数据库连接

-增加 DBHelper 类

第二步：查询用户是否存在

-SELECT COUNT(*)

-ExecuteScalar() 方法查询

// 查询 Student 表使用的 SQL 语句

string sql = string.Format(

    "SELECT COUNT(*) FROM Student WHERE LogInId='{0}'

    AND LogInPwd='{1}'",txtLogInId, txtLogInPwd);

// 使用的 Command 对象

SqlCommand command = newSqlCommand(sql, DBHelper.connection);

DBHelper.connection.Open();

// 执行查询，返回找到的个数

count = (int)command.ExecuteScalar();

小结：

      Connection 对象的什么方法用来打开和关闭数据库连接？

      ExecuteScalar()方法返回什么？

2.2.2如何对数据库的数据进行增删改？

    对于四大操作来说，增、删、改三大操作都是单向的，之所以叫单向操作就是因为这些操作只是修改数据库而不返回数据。只有查是双向操作，因为既需要数据库提交查询命令又需要操作从数据库返回的结果集。所有单向操作使用SqlCommand类的ExecuteNonQuery()方法来执行。

  以教务信息系统增加学员为例：

处理“保存”按钮的 Click事件，使用ExecuteNonQuery() 方法向数据库增加记录

部分参考代码：

stringconnStr = "…";
SqlConnection connection = newSqlConnection(connStr);
// 插入记录用的 SQL 语句        
string sql = string.Format( "INSERTINTO Grade (GradeName) VALUES ('{0}')",
       txtGradeName.Text);
// 创建 Command 对象
try
{
SqlCommand command= new SqlCommand(sql, connection);
// 打开数据库连接
connection.Open();
// 执行插入命令
int result =command.ExecuteNonQuery();
}catch(SqlException sqle)
{
    Console.WriteLine(＂连接数据库出错＂+sqle.Message);
}
finally
{
    cn.Close();// 关闭数据库连接       
}​​​​​​​ 
//如果是删除数据，则将sql语句改写为Delete语句即可,例如从人员表中删除某条记录
string sql=＂Delete from Personwhere Id=’ha002.mobile’＂;
//如果是更新数据，则将sql语句改写为Update语句即可
string strSql2 = "update P_Product set Name='数码4' where Id=53";  

2.3 如何从数据库读取数据：输水管--DataReader

     通过调用SqlCommand对象的ExecuteReader()可以得到一个SqlDataReader结果集对象。SqlDataReader是一个只进式的记录读取器，它可以一条一条读取检索到的数据。如果数据库是水池，连接对象是管道，那么SqlDataReader就是水龙头，打开水龙头就会不断的流出。

       因为SqlDataReader每次只能读取一条记录，所以你需要借助一个循环来读取所有的记录。你可以通过SqlDataReader的Read方法来读取下一条记录。同时Read方法还会返回一个bool型的值，借助这个值就可以判断是否已经读完，如果Read返回true,表示没有读完，否则表示读完。

   通过SqlDataReader获取当前记录后，有四种方式获取该记录的列值。

2.3.1案例：使用 DataReader 查询数据

分析：

1.   需要自动添加“年级”组合框中的年级名称；

2.   处理窗体 Load 事件

3.   使用 DataReader 读取年级名称

关键代码：

// 执行查询省略…

SqlDataReader dataReader =command.ExecuteReader();
string gradeName ="";     // 年级名称
// 循环读出所有的年级名，并添加到年级列表框中
while (dataReader.Read())
{    /*循环读取数据行添加到组合框中*/
        gradeName = (string)dataReader[0];
        cboGrade.Items.Add(gradeName);
}
dataReader.Close();

2.3.2 DataReader 的主要成员 

DataReader 使用步骤小结

1、创建 Command 对象

2、调用 ExecuteReader() 创建DataReader 对象

3、使用 DataReader 的Read() 方法逐行读取数据

4、读取某列的数据，(type)dataReader[ i]

5、关闭 DataReader 对象

//注意：DataReader使用后必须关闭

//第4步获取某列值的方法有如下四种方式：

ü       第一种：通过列号

(type)dataReader[i]; //i:表示该列的列号,取值为0…n

ü       第二种：通过列名

(type)dataReader[＂列名＂]

ü       第三种：通过GetValue()和列号

(type)dataReader.GetValue(i);  //i: 表示该列的列号,取值为0…n

ü       通过GetX方法和列号

dataReader.GetString(i),dataReader.GetInt32(i)等;

       这四种读取方式的主要区别在于效率的问题，前三种返回的都是object类型的数据，你需要进行拆箱操作（强制类型转换）才能使用该列的值。只有第四种返回的是具体数据类型的值，效率会高些。第四种方式应该根据数据库列的具体类型调用不同的Get方法，比如数据库类型是DateTime，则需要调用GetDateTime(i)方法。另外，第二种方法也比较常用。

2.3.3得到DataReader的列信息

dataReader.FieldCount    获取当前行中的列数

dataReader.GetFieldType(序号)   获取是对象的数据类型的 Type

dataReader.GetDataTypeName(序号)  获取源数据类型的名称

dataReader.GetName(序号)    获取指定列的名称

dataReader.GetOrdinal(序号)   在给定列名称的情况下获取列序号

2.3.4得到数据表的信息

   DataTable dt=dr.GetSchemaTable();

2.3.5使用DataReader获得最佳性能的技巧

在使用带参数的Command前，必须关闭DataReader。

       完成读数据之后一定要关闭DataReader。如果使用Connection只返回DataReader，那么关闭DataReader之后立刻关闭它。另外一个显式关闭Connection的方法是将CommandBehavior.CloseConnection传递给ExecuteReader方法，以确保关闭DataReader时相应的连接也被关闭。如果从一个方法返回DataReader，而且不能控制DataReader的相关连接的关闭，则这样做特别有用。

       不能在层之间远程访问DataReader。DataReader是为已连接好的数据访问而设计的。

       当访问列数据时，使用类型化访问器，例如GetString、GetInt32等。这使你不用将GetValue返回的Object强制转换成特定类型。

       一个单一连接每次只能打开一个DataReader。如果想在相同的数据存储区上同时打开两个DataReader，则必须显式创建两个连接，每个DataReader一个。这是ADO.NET为池化连接的使用提供更多控制的一种方法。

       在默认情况下，DataReader每次Read时都要将整行加载到内存。这允许在当前行内随机访问列。如果不需要这种随机访问，为了提高性能，则将CommandBehavior.SequentialAccess传递给ExecuteReader调用。这将DataReader的默认行为更改为仅在请求时将数据加载到内存。注意，CommandBehavior.SequentialAccess要求顺序访问返回的列。也就是说，一旦读过返回的列，就不能再读它的值了。

       如果已经读取了来自DataReader的数据，但仍然有大量挂起的未读结果，则在关闭DataReader之前先要取消Command。因为取消Command可使服务器放弃这些结果，从而释放服务器的资源。

2.4 随用随关，释放资源

       对于C#程序员来说，确保始终关闭Connection和DataReader对象的一个方便的方法就是使用using语句。using语句在离开自己的作用范围时，会自动调用被"使用"的对象的Dispose。例如：1.    

 string connectionString = "data source=127.0.0.1;Database=codematic; 
2.     user id=sa;password=";  
3.     using (SqlConnection myConnection = new SqlConnection(connectionString)) 
4.     {  
5.         SqlCommand cmd = myConnection.CreateCommand(); 
6.         cmd.CommandText = "SELECT * FROM P_Product";  
7.         myConnection.Open();  
8.         using (SqlDataReader dr = cmd.ExecuteReader())  
9.         {  
10.          while (dr.Read())  
11.          { 
12.              Response.Write(dr.GetInt32(0).ToString()+","+dr.GetString(1)
+"<br>");  
13.          } 
14.  }  
}