图书 合集 文章 工具与资源 本站查询
C#开发训练营

第14课：数据库操作基础——ADO.NET

接下来的几课会讨论数据库的操作，并讨论基于数据的应用开发问题；并封装基于SQL Server和MySQL数据库的操作操作组件。本课会回顾如何在C#代码中使用ADO.NET组件操作数据库。

在.NET Framework类库中，操作数据库的基本资源定义在System.Data命名空间；对于一些第三方数据库系统的操作，厂商也提供了对应的.NET Framework开发资源。比如MySQL数据库相关资源就定义在MySql命名空间，需要从MySQL或Oracle网站下载；下载相关资源后，将MySql.Data.dll文件复制到ASP.NET Web项目中的Bin目录即可。

接下来，我们以SQL Server数据库例，讨论如何使用ADO.NET组件操作数据库；后续课程会完成SQL Server和MySQL数据库常用组件的封装。

连接数据库

为方便测试，可以在SQL Server中创建cdb_demo数据库；然后执行如下代码创建user_main数据表。

SQL
use cdb_demo;
go

create table user_main(
userid bigint not null identity(1,1) primary key,
username nvarchar(30) not null unique,
userpwd nvarchar(64) not null check(len(userpwd)=64),
email nvarchar(30) not null,
locked int not null default 1
);

.NET Framework类库中，支持SQL Server数据库的资源定义在System.Data.SqlClient命名空间。

应用中操作数据库时，首先需要连接数据库服务器（或实例），此时使用IDbConnection接口组件，如连接SQL Server数据库时使用SqlConnection类，连接MySQL数据库时使用MySqlConnection类等。

连接数据库的参数使用一个字符串来设置，每个参数由参数名和参数值两部分组成，多个参数由分号（;）分隔。为方便生成数据库连接字符串，一些数据库还提供了连接字符串辅助生成类，如SQL Server数据库就可以使用SqlConnectionStringBuilder类，MySQL数据库使用MySqlConnectionStringBuilder类。

下面的代码（/app_code/data/sqlserver/tSqlHelper.cs），在tSqlHelper类中的GetCnnStr()方法中使用SqlConnectionStringBuilder类来构建连接字符串。

C#
using System.Data.SqlClient;

public static class tSqlHelper
{
    //
    public static string GetCnnStr(string server, string database)
    {
        SqlConnectionStringBuilder sb =
            new SqlConnectionStringBuilder();
        sb.DataSource = server;
        sb.InitialCatalog = database;
        sb.IntegratedSecurity = true;
        sb.Pooling = true;
        sb.AsynchronousProcessing = true;
        return sb.ConnectionString;
    }
    //
    public static string GetCnnStr(string server, string database,
        string uid, string pwd)
    {
        SqlConnectionStringBuilder sb =
            new SqlConnectionStringBuilder();
        sb.DataSource = server;
        sb.InitialCatalog = database;
        sb.IntegratedSecurity = false;
        sb.UserID = uid;
        sb.Password = pwd;
        sb.Pooling = true;
        sb.AsynchronousProcessing = true;
        return sb.ConnectionString;
    }
    //
}

tSqlHelper类中定义了两个版本的GetCnnStr()方法，分别用于连接SQL Server数据库的本机实例和远程实例。其中使用了SqlConnectionStringBuilder类进行操作，通过各种属性设置连接参数，并通过ConnectionString属性返回数据库连接字符串。

  • SqlConnectionStringBuilder类中常用的属性包括：
  • InitialCatalog，指定数据库名称。
  • DataSource，指定服务器或实例地址。
  • UserID，登录用户名，使用Windows身份验证登录时不指定。
  • Password，登录密码，使用Windows身份验证登录时不指定。
  • IntegratedSecurity，是否使用Windows用户登录，即是否使用当前Windows系统登录用户来登录数据库。
  • AsynchronousProcessing，是否开启异步处理。
  • ConnectTimeout，连接超时
  • ConnectionString，使用相关参数创建的数据库连接字符串。

使用数据库时，可以使用using语句结构，在数据库操作完成时，可以自动关闭数据库连接，如下面的代码。

C#
string CnnStr = tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
using(SqlConnection cnn = new SqlConnection(CnnStr))
{
    cnn.Open();
    // 使用数据库...
}

代码中使用的SQL Server服务器为本机，实例名为MSSQLSERVER1，数据库名称是cdb_demo，如果读者使用了不同的数据库服务器、实例和数据库，应修改相关参数。

执行SQL语句

执行SQL语句时，使用IDbCommand接口组件，SQL Server数据库使用的是SqlCommand类，定义在System.Data.SqlClient命名空间，常用的成员包括：

  • CommandText属性，执行的SQL语句、存储过程名或表名。
  • CommandType属性，指定CommandText属性中的内容是什么类型，定义为CommandType枚举类型，枚举值包括Text（SQL语句）、StoredProcedure（存储过程）、TableDirect（表名，只用于OLEDB数据源）。
  • CommandTimeout属性，命令执行的超时时间，默认为30秒。
  • Parameters属性，指定命令中包含的参数数据。
  • Transaction属性，命令关联的事务，SQL Server数据库中定义为SqlTransaction类型。
  • ExecuteScalar()方法，执行命令，并获取标量数据（object类型），方法会返回查询结果中第一行记录第一个字段的值。
  • ExecuteNonQuery()方法，执行命令，返回影响的记录数量（int类型）。此方法有异步版本，其中，BeginExecuteNonQuery()方法返回IAsyncResult对象，EndExecuteNonQuery()方法使用此对象作为参数，并返回操作结果。
  • ExecuteReader()方法，执行命令，并返回查询结果，操作SQL Server数据库时返回SqlDataReader类型。此方法同样包含异步版本，包括BeginExecuteReader()和EndExecuteReader()方法。

首先来看ExecuteNonQuery()方法的应用，如下面的代码，会向user_main表添加一条记录。

C#
using System;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr = 
            tSqlHelper.GetCnnStr( @".\MSSQLSERVER1","cdb_demo");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = 
@"insert into user_main(username,userpwd,email,locked)
values('user01','"+tStr.Sha256("123456")+"','user01@aaa.bbb',0);";
            int result = cmd.ExecuteNonQuery();
            tWeb.WriteLine(result);
        }
    }
}

创建SqlCommand对象时使用了已打开连接的SqlConnection对象中的CreateCommand()方法，然后指定了CommandText属性的SQL语句，最后调用ExecuteNonQuery()方法执行语句。如果页面显示1，表示已成功添加了一条记录。

使用参数

为了防止SQL注入等有不安全操作，对于客户端提交的数据，或者是文本数据，应使用参数进行传递。SQL语句中参数的数据，可以使用SqlCommand对象中的Parameters属性处理，它定义为一个参数集合。

下面的代码，我们使用参数向user_main表添加一条记录。

C#
using System;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr = 
            tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText =
                @"insert into user_main(username,userpwd,email,locked)
values(@username,@userpwd,@email,@locked);";
            cmd.Parameters.AddWithValue("@username", "user02");
            cmd.Parameters.AddWithValue("@userpwd", tStr.Sha256("123456"));
            cmd.Parameters.AddWithValue("@email", "user02@aaa.bbb");
            cmd.Parameters.AddWithValue("@locked", 1);
            int result = cmd.ExecuteNonQuery();
            tWeb.WriteLine(result);
        }
    }
}

成功添加记录后，页面同样会显示1。代码中，如果需要清除SqlCommand中的参数数据，可以使用cmd.Parameters.Clear()方法

异步方法

使用异步语句执行方法时，还需要IAsyncResult对象配合，如下面的代码，同样是向user_main表添加记录。

C#
using System;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr = 
            tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText =
                @"insert into user_main(username,userpwd,email,locked)
values(@username,@userpwd,@email,@locked);";
            cmd.Parameters.AddWithValue("@username", "user03");
          cmd.Parameters.AddWithValue("@userpwd", tStr.Sha256("123456"));
            cmd.Parameters.AddWithValue("@email", "user03@xxx.yyy");
            cmd.Parameters.AddWithValue("@locked", 0);
            IAsyncResult ar = cmd.BeginExecuteNonQuery();
            int result = cmd.EndExecuteNonQuery(ar);
            tWeb.WriteLine(result);
        }
    }
}

读取标量数据

ExecuteScalar()方法用于返回查询结果中的第一条记录中的第一个字段的值；下面的代码，会向user_main表中添加记录，并返回新记录的ID字段值。

C#
using System;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr = 
            tSqlHelper.GetCnnStr("cdb_demo", @".\MSSQLSERVER1");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText =
                @"insert into user_main(username,userpwd,email,locked)
output inserted.userid
values(@username,@userpwd,@email,@locked);";
            cmd.Parameters.AddWithValue("@username", "user04");
            cmd.Parameters.AddWithValue("@userpwd", tStr.Sha256("123456"));
            cmd.Parameters.AddWithValue("@email", "user04@xxx.yyy");
            cmd.Parameters.AddWithValue("@locked", 0);
            tWeb.WriteLine(cmd.ExecuteScalar());
        }
    }
}

执行会显示4，也就是新记录中ID字段的数据，在user_main表中，也就是userid字段的数据。代码中使用了SQL Server数据库中的inserted表返回userid字段数据，这是SQL Server 2005以后添加的新特性，可以通过inserted表返回新添加记录的数据，如果返回记录的全部分数据，可以使用inserted.*。

此外，获取新记录ID数据的传统方式是通过@@identity系统变量，稍后，在讨论事务的执行时会演示相关操作。

使用SqlDataReader对象读取查询结果

前面的示例，我们已经向user_main表添加了4条记录，内容如图1。

图1

下面，我们通过select语句查询这些数据，并使用SqlDataReader对象读取数据。SqlDataReader类中定义了一系列成员，方便读取查询结果中字段信息和记录数据，如：

  • FieldCount属性，返回查询结果中的字段数量。
  • GetName()方法，使用整数索引返回相应的字段名。
  • GetFieldType()方法，使用整数索引返回相应字段的类型（Type类型）。
  • HasRows属性，判断对象中是否包含数据记录。
  • Read()方法，读取下一条记录，成功时返回true，否则返回false。
  • Item属性（索引器），使用整数索引访问当前记录的字段数据。
  • GetInt32()方法，使用整数索引返回Int32（int）类型数据。此外，还有一系列的方法可以返回不同类型的字段数据，它些方法的名称使用System命名空间中定义的类型名称，如返回long类型数据使用GetInt64()方法、返回float类型数据使用GetSingle()方法、返回double类型数据使用GetDouble()方法等。
  • GetOrdinal()方法，根据字段名返回索引值。
  • IsDBNull()方法，判断指定字段中的数据是否为数据库中的空值（NULL）。

下面的代码，我们读取user_main表中userid字段为1的记录，并显示在页面中。

C#
using System;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr = 
            tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = @"select * from user_main where userid=1;";
            IAsyncResult ar = cmd.BeginExecuteReader();
            using (SqlDataReader dr = cmd.EndExecuteReader(ar))
            {
                if (dr.Read())
                {
                    for(int col = 0; col < dr.FieldCount; col++)
                    {
                        tWeb.WriteLine(dr.GetName(col) + 
                            " : " + tStr.Parse(dr[col]));
                    }
                }
            }
        }
    }
}

页面显示结果如图2。

图2

代码中，使用了ExecuteReader()方法的异步版本来获取查询结果，然后，使用dr.Read()方法读取记录，并通过一个for循环访问了所有字段名和对应的数据。

应用中，如果SqlDataReader对象中包含多条记录，可以使用while循环调用Read()方法逐条读取，如下面的代码就会显示user_main表中的所有记录。

C#
using System;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr = 
            tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = @"select * from user_main;";
            IAsyncResult ar = cmd.BeginExecuteReader();
            using (SqlDataReader dr = cmd.EndExecuteReader(ar))
            {
                while (dr.Read())
                {
                    for(int col = 0; col < dr.FieldCount; col++)
                    {
                        tWeb.WriteLine(dr.GetName(col) + 
                            " : " + tStr.Parse(dr[col]));
                    }
                    tWeb.WriteLine("***************");
                }
            }
        }
    }
}

本例显示的结果中，每条记录会使用15个星号（*）分隔。

适配器与脱机组件

除了使用SqlDataReader读取数据查询结果，还可以使用SqlDataAdapter对象将数据填充到DataSet对象中。

DataSet属于脱机组件（非连接组件），应用时不需要连接数据库，而SqlConnection、SqlCommand、SqlDataReader、SqlDataAdapter等类型属于连接组件，使用时必须保持数据库连接。

DataSet中主要包括两种对象，即DataTable和DataRelation，表示数据表和数据表之间的关系。在DataSet类中，使用Tables属性获取所有表的集合，使用Relations属性获取所有关系的集合。接下来主要关注数据表的使用。

在表中主要包括了列（Column）信息和行（Row）信息，其中，列包括名称、数据类型等信息，每一行都包含了与列相对应的数据。

下面的示例，我们会创建一个DataTable对象，其中包括了三列、两行数据，然后将这些数据绑定到GridView控件中。首先，在Test.aspx文件中创建GridView控件，如下面的代码。

HTML
<%@ Page Language="C#" AutoEventWireup="true" 
    CodeFile="Test.aspx.cs" Inherits="Test" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="grd1" runat="server"></asp:GridView>
        </div>
    </form>
</body>
</html>

接下来，修改Test.aspx.cs文件内容如下。

C#
using System;
using System.Data;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable tbl = new DataTable();
        for (int col = 0; col < 3; col++)
            tbl.Columns.Add("f" + col.ToString());
        for (int row = 0; row < 2; row++)
        {
            DataRow r = tbl.NewRow();
            r["f0"] = "user0" + row.ToString();
            r["f1"] = "user0" + row.ToString() + "@aaa.bbb";
            r["f2"] = 0;
            tbl.Rows.Add(r);
        }
        //
        grd1.DataSource = tbl;
        grd1.DataBind();
    }
}

代码中，首先使用DataTable的Columns属性操作列信息，其中的Add()方法用于添加列，方法的参数就是列名（ColumnName）；然后，使用DataTable对象的NewRow()方法创建一个与表关联的新行，并指定对应字段的数据，最后，使用DataTable对象的Rows属性中的Add()方法将新行添加到DataTable对象。

GridView对象的DataSource指定数据源，这里就是DataTable对象，DataBind()方法执行数据绑定操作；页面显示结果如图3。

图3

下面的代码，我们使用SqlDataAdapter（数据适配器）读取user_main表的全部数据，并填充到DataSet对象，最后绑定到GridView对象。

C#
using System;
using System.Data;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr =
           tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = @"select * from user_main;";
            using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                ada.Fill(ds);
                grd1.DataSource = ds.Tables[0];
                grd1.DataBind();
            }
        }
    }
}

代码中，使用SqlCommand对象作为参数构建了SqlDataAdapter对象，然后，使用SqlDataAdapter对象的Fill()方法将查询结果的数据填充到DataSet对象。DataSet中的Tables属性包含了所有表的集合，填充的数据就位于第一个表，使用ds.Tables[0]获取此表，并将其作为GridView控件的数据源（DataSource），最后调用GridView对象的DataBind()方法绑定数据。页面显示效果如图4。

图4

执行事务

数据库处理技术中，事务（Transaction）的基本特点是，事务中的任务要不全部完成，否则就什么也不做。一个最常见的例子就是转账操作，如执行A账户向B账户转账100元的事务，就必须从A账户扣除100元，然后B账户添加100元，两项操作缺一不可，即转账事务中的两个操作都必须成功执行，否则就不执行任何操作。

C#开发中执行SQL Server事务时使用SqlTransaction类。

下面的代码，我们再向user_main表中添加一条记录，并使用@@identity系统变量返回新记录的ID字段（userid）数据；这两项操作会在一个事务中完成。

C#
using System;
using System.Data.SqlClient;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string CnnStr =
            tSqlHelper.GetCnnStr("cdb_demo", @".\MSSQLSERVER1");
        using (SqlConnection cnn = new SqlConnection(CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            using (SqlTransaction tran = cnn.BeginTransaction())
            {
                cmd.Transaction = tran;
                cmd.CommandText =
                    @"insert into user_main(username,userpwd,email,locked)
values(@username,@userpwd,@email,@locked);";
                cmd.Parameters.AddWithValue("@username", "user05");
          cmd.Parameters.AddWithValue("@userpwd", tStr.Sha256("123456"));
          cmd.Parameters.AddWithValue("@email", "user05@xxx.yyy");
                cmd.Parameters.AddWithValue("@locked", 0);
                if (cmd.ExecuteNonQuery() == 1)
                {
                    cmd.CommandText = "select @@identity;";
                    object result = cmd.ExecuteScalar();
                    tran.Commit();
                    tWeb.WriteLine(result);
                }
            }
        }
    }
}

代码中，使用SqlConnection对象的BeginTransaction()方法构造SqlTransaction对象，并使用SqlCommand对象的Transaction属性将命令执行对象与事务对象关联；然后执行insert语句添加一条新的记录，成功执行后，SqlCommand对象的ExeucteNonQuery()方法会返回1，此时，调用select @@identity语句返回新记录的ID字段数据，并保存到result对象中；在使用事务的Commit()方法提交操作后，在页面中显示新记录的ID字段数据。

豫ICP备11002860-1

公安机关备案41152102000002号

caohuayu.com    本站内容均为原创，转载请注明出处