.NET Framework类库中用于操作数据库的资源称为ADO.NET,主要定义在System.Data命名空间及其下级命名空间,其中,System.Data.SqlClient等命名空间中的资源可以用于SQL Server数据库操作;此外,还可以使用OLEDB或ODBC方式进行数据库操作。本课,我们将结合SQL Server数据库讨论ADO.NET中常用组件的应用。

ADO.NET组件按照工作时是否连接数据库可以分为两类,即连接组件和非连接组件(脱机组件)。连接组件是指在工作时必须与数据库进行连接,如IDbConnection、IDbCommand、IDataReader、IDataAdapter等组件;非连接组件是指可以不与数据库连接,以脱机的方式进行数据处理,如DataSet等组件。

连接数据库

在应用中使用数据库,第一步就是与数据库进行连接,这里使用IDbConnection组件,用于连接SQL Server数据库的类型就是SqlConnection类。此外,连接数据库时,还需要一个连接字符串(ConnectionString)指定一系列的数据库连接参数,ADO.NET中提供了SqlConnectionStringBuilder类,用于SQL Server数据库连接字符串的生成,下面的代码演示了SQL Server数据的连接操作。

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // 连接字符串
            SqlConnectionStringBuilder sb =
                new SqlConnectionStringBuilder();
            sb.DataSource = @".\MSSQLSERVER1";
            sb.InitialCatalog = @"Cdb_Test";
            sb.IntegratedSecurity = true;
            sb.AsynchronousProcessing = true;
            sb.Pooling = true;
            string cnnStr = sb.ConnectionString;
            // 连接测试
            try
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    cnn.Open();
                    Console.WriteLine("数据库已成功打开");
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

首先,数据库连接字符串的生成使用了SqlConnectionStringBuilder类的一些成员,主要包括:

  • DataSource属性,指定数据库的服务器和实例,@".\MSSQLSERVER1"中的圆点(.)表示本机,如果是网络服务器,应指定IP地址和SQL Server服务的TCP/IP端口。MSSQLSERVER1表示连接的数据库实例,大家可以根据上一课记录的内容指定。此外,如果连接本机的默认实例,可以只使用一个圆点表示。
  • InitialCatalog属性,指定连接的数据库名称,本例使用上一课创建的Cdb_Test数据库。
  • IntegratedSecurity属性,是否使用集成安全(也就是我们在SSMS中使用的“Windows 身份验证”),本例使用true,使用Windows身份验证方式,此时会使用Windows当前登录的用户信息登录数据库。如果是网络数据库,则此属性应该设置为false,并通过UserID和Password属性设置登录数据库的用户和密码。
  • AsynchronousProcessing属性,是否允许进行异步操作。
  • Pooling属性,是否记动数据库连接池,大多数情况下应该启动。
  • ConnectionString属性,通过一系列参数生成的数据库连接字符串,这里赋值给cnnStr对象,稍后会使用。如果大家想看数据库连接串中的内容,可以使用Console.WriteLine()方法显示。

接下来是连接数据库的操作,我们知道,SqlConnection类实现了IDisposable接口,所以, 这里使用using语句结构进行数据库的连接操作。创建SqlConnection对象后,使用Open()方法打开数据库连接,如果一切顺利会显示“数据库已成功打开”,如果连接的参数有问题,则会显示异常情况的描述信息。确认可以正确连接Cdb_Test数据库以后,就可以进行接下来的测试了。

请注意,接下来的代码只截取关键的部分,即using(SqlConnection cnn = new SqlConnection(cnnStr)){...}部分,其他代码不需要改变。

执行SQL

执行SQL时,可以使用SqlCommand类型,需要注意的是创建SqlCommand对象应使用SqlConnection对象的CreateCommand()方法,如下面的代码。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "select UserID from UserMain where UserName='Jerry';";
    Console.WriteLine(cmd.ExecuteScalar());
}

代码中,使用CommandText属性设置SqlCommand执行的SQL语句,然后,使用ExecuteScalar()方法返回查询结果第一行的第一个字段的数据;如果没有返回结果,则返回null值。正常情况下,执行此代码会显示2,即显示用户名(UserName)为Jerry的UserID值。

返回执行结果

在SqlCommand对象中,除了ExecuteScalar()方法,还有一些方法可以返回执行结果,如:

  • ExecuteNonQuery()方法,返回SQL执行影响的记录行数,如添加、更新、删除等操作。
  • ExecuteReader()方法,返回一个记录集,稍后会讨论如何从中读取数据。
  • BeginExecuteNonQuery()和EndExecuteNonQuery()方法,ExecuteNonQuery()方法的异步版本。
  • BeginExecuteReader()和EndExecuteReader()方法,ExecuteReader()方法的异步版本。

下面的代码,我们将Jerry用户的密码修改为5678910。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "update UserMain set UserPwd = '5678910' where UserName='Jerry';";
    Console.WriteLine(cmd.ExecuteNonQuery());
}

执行代码显显示1,即修改了一条记录的数据。

下面的代码会读取所有用户的UserName、UserPwd和IsLocked字段数据。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "select UserName,UserPwd,IsLocked from UserMain;";
    IAsyncResult ar = cmd.BeginExecuteReader();
    using (SqlDataReader dr = cmd.EndExecuteReader(ar))
    {
        // 显示字段名
        for (int i = 0; i < dr.FieldCount; i++)
            Console.Write(dr.GetName(i).PadRight(16));
        Console.WriteLine();
        // 显示记录
        while(dr.Read())
        {
            for (int i = 0; i < dr.FieldCount; i++)
                Console.Write("{0}".PadRight(16), dr[i]);
            Console.WriteLine();
        }
    }
}

代码执行结果如下图所示。

enter image description here

本例使用了ExecuteReader()的异步版本,其中,BeginExecuteReader()方法会返回IAsyncResult对象,而对应的EndExecuteReader()方法需要此对象作为参数。读取后的数据集为SqlDataReader对象,使用的主要成员包括:

  • FieldCount属性,记录集中的字段数量。
  • GetName()方法,按索引值给出字段名。
  • Read()方法,读取下一条记录,如果读取成功返回true,否则返回false。代码中正是使用此方法的结果判断是否读取了有效的数据。
  • 索引器,SqlDataReader对象的索引器是从0开始的数值索引,用于读取当前记录中相应字段的数据,读取的结果是object类型,如果可以确认字段数据的类型,还可以使用GetInt32()、GetInt64()、GetFloat()、GetDecimal()等方法获取相应类型的数据。

使用参数

前面的示例中,都直接使用SQL语句来完成数据操作,而实际工作中,如果需要组合SQL,比如,将用户输入的数据连接到SQL语句,此时,可能造成SQL注入,即使用满足SQL语法的数据组合成具有破坏性的操作语句。

避免数据注入的一个好办法就是使用参数,在SqlCommand中有一个Parameters属性,它是一个参数集合,可以使用其中的AddWithValue()方法添加参数和数据,如下面的代码,会在UserMain表中添加一条记录。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = @"insert into UserMain(UserName,UserPwd,IsLocked) output inserted.UserID values(@UserName,@UserPwd,@IsLocked);";
    cmd.Parameters.AddWithValue("@UserName", "Smith");
    cmd.Parameters.AddWithValue("@UserPwd", "123456");
    cmd.Parameters.AddWithValue("@IsLocked", 0);
    //
    Console.WriteLine(cmd.ExecuteScalar());
}

请注意SQL语句,在values关键字后的数据列表中,使用了以@字符开始的参数;然后,通过SqlCommand中Parameters属性的AddWithValue()方法分别添加这些参数及数据。代码中,还使用SQL Server数据库的另一个特点,即inserted表,此表会保存会话中新添加的数据,使用“output inserted.UserID”子语可以让insert语句直接返回新记录的UserID字段值;代码的最后就是使用cmd.ExecuteScalar()方法返回这个新添加记录的UserID字段值。

执行存储过程

为了方便测试,我们先在SQL Server中的Cdb_Test数据库中添加一个名为usp_login的存储过程;在SSMS中新建一个查询,并执行以下代码。

use Cdb_Test;
go

create procedure usp_login
    @username as nvarchar(50),@userpwd as nvarchar(50)
as
begin
    select UserID from UserMain where UserName=@username and UserPwd = @userpwd and IsLocked=0;
end;

此存储过程的作用就是验证用户的登录,其中,需要给入@username和@userpwd两个参数,而且用户并没被锁定(IsLocked=0)。执行存储过程,如果用户登录信息正确会返回UserID数据。

接下来,我们回到C#代码,通过SqlCommand对象调用usp_login存储过程,如下面的代码。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    cmd.CommandText = "usp_login";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@username", "Smith");
    cmd.Parameters.AddWithValue("@userpwd", "123456");
    //
    Console.WriteLine(cmd.ExecuteScalar());
}

本例中,直接将SqlCommand对象的CommandText属性设置为存储过程的名称,但同时需要将CommandType属性设置为StoredProcedure值(默认为Text,即按SQL语句执行);接下来,通过SqlCommand.Parameters对象的AddWithValue()方法添加存储过程所需要的参数和数据。如果显示的结果是大于0的整数,说明用户登录成功,否则会返回null值(显示为空白)。

执行事务

上一课,我们介绍过事务的特点,即一个事务中的一个或多个任务,要不全部完成,要不什么都不做。在ADO.NET组件中,使用SqlTransaction类执行SQL Server数据库的事务,如下面的代码。

using (SqlConnection cnn = new SqlConnection(cnnStr))
{
    cnn.Open();
    SqlCommand cmd = cnn.CreateCommand();
    using (SqlTransaction tran = cnn.BeginTransaction())
    {
        cmd.Transaction = tran;
        cmd.CommandText = "insert into UserMain(UserName,UserPwd) values(@username,@userpwd);";
        cmd.Parameters.AddWithValue("@username", "Frank");
        cmd.Parameters.AddWithValue("@userpwd", "123456");
        if(cmd.ExecuteNonQuery()==1)
        {
            cmd.CommandText = "select @@IDENTITY;";
            object obj = cmd.ExecuteScalar();
            tran.Commit();
            Console.WriteLine(obj);
        }
    }
}

本例中使用事务时,首先通过SqlConnection对象的BeginTransaction()方法开始事务,然后通过SqlCommand对象的Transaction属性与SqlTransaction对象关联,这样就可以在事务中执行命令了;代码中的事务是添加一条用户信息并返回新的ID值,这里共执行了两条语句,当两条语句都执行成功时,使用SqlTransaction对象的Commit()方法提交事务所做的修改。

实际应用中,如果有某条语句的执行结果不是预期的,还可以使用SqlTransaction对象的Rollback()方法进行回滚,即恢复事务执行前的状态。另一方面,如果执行事务中发生发生异常,在using语句结构中执行的事务会自动进行回滚操作。

小结

本课以SQL Server数据库为例,讨论了如果使用ADO.NET组件操作数据库;数据应用开发过程中,希望可以举一反三,找到ADO.NET组件在操作不同数据库时的共同点,以提高学习和工作效率。

另一方面,.NET Framework原生类库中已放弃对Oracle数据库的支持;在项目中使用Oracle数据库或MySQL等数据库时,应该从数据库的官方网站获取相应的ADO.NET组件。

CHY软件小屋原创作品!