9.5 直接数据操纵.docx

9.5 直接数据操纵视频精讲：光盘videobaseVideo09直接数据操纵.swf当创建了同数据库的连接之后，就可以执行SQL命令实现对数据库数据的查询、插入、更新、删除等操作。ADO.NET为开发人员提供的数据操纵主要包括两种模式：直接数据操纵模式和脱机数据操纵模式。本节主要介绍直接数据操纵模式，下一节介绍脱机数据操纵模式。9.5.1 直接数据操作流程同数据库交互最简单的方式是使用直接数据访问。当使用直接数据访问时，可以创建一个SQL命令，然后执行它。当使用直接数据访问查询数据时，没有将信息复制到内存中。而是在数据库连接打开时，执行数据查询，其工作在一个相对较短的时间周期内，然后关闭连接。其流程如图9-10所示。图9-10 使用ADO.NET执行直接数据访问为了使用直接数据查询访问数据库，需要执行如下步骤。（1）创建Connection、Command和DataReader对象。（2）使用DataReader对象从数据库中提取数据。（3）关闭连接。（4）发送数据给用户。此时，用户看到的数据和数据库中的信息没有任何连接，所有的ADO.NET对象被释放。当需要更新数据时，执行如下步骤。（1）创建一个新的Connection和Command对象。（2）执行带对应SQL命令的Command对象。Command类支持查询任何类型的SQL语句。同数据提供程序相关的Command类实现了一些标准功能，就像Connection类一样。其中IDbCommand接口定义了一些关键属性和核心方法，用于在连接打开的情况下执行命令。下面给出了SqlCommand对象的一些常用属性，如表9-3所示。表9-3 SqlCommand对象的常用属性名称说明CommandText 获取或设置要对数据源执行的Transact-SQL语句、表名或存储过程CommandTimeout 获取或设置在终止执行命令的尝试并生成错误之前的等待时间CommandType 获取或设置一个值，该值指示如何解释CommandText属性Connection 获取或设置SqlCommand 的此实例使用的SqlConnectionDbConnection 获取或设置此DbCommand使用的DbConnectionDbParameterCollection 获取DbParameter对象的集合DbTransaction 获取或设置将在其中执行此DbCommand对象的DbTransactionParameters 获取SqlParameterCollectionTransaction 获取或设置将在其中执行SqlCommand的SqlTransaction下面给出了SqlCommand对象的一些常用方法，如表9-4所示。表9-4 SqlCommand对象的常用方法名称说明BeginExecuteNonQuery 启动此 SqlCommand 描述的 Transact-SQL 语句或存储过程的异步执行BeginExecuteReader 启动此 SqlCommand 描述的 Transact-SQL 语句或存储过程的异步执行，并从服务器中检索一个或多个结果集BeginExecuteXmlReader 启动此 SqlCommand 描述的 Transact-SQL 语句或存储过程的异步执行，并将结果作为 XmlReader 对象返回Cancel 尝试取消 SqlCommand 的执行CreateDbParameter 创建 DbParameter 对象的新实例CreateParameter 创建 SqlParameter 对象的新实例EndExecuteNonQuery 完成 Transact-SQL 语句的异步执行EndExecuteReader 完成 Transact-SQL 语句的异步执行并返回请求的 SqlDataReaderEndExecuteXmlReader 完成 Transact-SQL 语句的异步执行，将请求的数据以 XML 形式返回ExecuteDbDataReader 对连接执行命令文本ExecuteNonQuery 对连接执行 Transact-SQL 语句并返回受影响的行数ExecuteReader 将 CommandText 发送到 Connection 并生成一个 SqlDataReaderExecuteScalar 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行ExecuteXmlReader 将 CommandText 发送到 Connection 并生成一个 XmlReader 对象Prepare 在 SQL Server 的实例上创建命令的一个准备版本ResetCommandTimeout 将 CommandTimeout 属性重置为其默认值下面给出了SqlDataReader对象的一些常用属性，如表9-5所示。表9-5 SqlDataReader对象的常用属性名称说明Connection 获取与 SqlDataReader 关联的 SqlConnectionFieldCount 获取当前行中的列数HasRows 获取一个值，该值指示 SqlDataReader 是否包含一行或多行IsClosed 检索一个布尔值，该值指示是否已关闭指定的 SqlDataReader 实例RecordsAffected 获取执行 Transact-SQL 语句所更改、插入或删除的行数下面给出了SqlDataReader对象的一些常用方法，如表9-6所示。表9-6 SqlDataReader对象的常用方法名称说明Close 关闭 SqlDataReader 对象GetBoolean 获取指定列的布尔值形式的值GetByte 获取指定列的字节形式的值GetBytes 从指定的列偏移量将字节流读入缓冲区，并将其作为从给定的缓冲区偏移量开始的数组GetChar 获取指定列的单个字符串形式的值GetChars 从指定的列偏移量将字符流作为数组从给定的缓冲区偏移量开始读入缓冲区GetDataTypeName 获取一个表示指定列的数据类型的字符串GetDateTime 获取指定列的 DateTime 对象形式的值GetDateTimeOffset 检索指定列的 DateTimeOffset 对象形式的值GetDbDataReader 返回被请求的列序号的 DbDataReader 对象，可以使用提供程序特定的实现对该对象进行重写GetDecimal 获取指定列的 Decimal 对象形式的值GetDouble 获取指定列的双精度浮点数形式的值GetEnumerator 返回循环访问 SqlDataReader 的 IEnumeratorGetFieldType 获取指定对象的数据类型的 TypeGetFloat 获取指定列的单精度浮点数形式的值GetInt16 获取指定列的 16 位有符号整数形式的值GetInt32 获取指定列的 32 位有符号整数形式的值GetInt64 获取指定列的 64 位有符号整数形式的值GetLifetimeService 检索控制此实例的生存期策略的当前生存期服务对象GetName 获取指定列的名称GetSqlBinary 获取指定列的 SqlBinary 形式的值GetSqlBoolean 获取指定列的 SqlBoolean 形式的值GetSqlByte 获取指定列的 SqlByte 形式的值GetSqlBytes 获取指定列的 SqlBytes 形式的值GetSqlChars 获取指定列的 SqlChars 形式的值GetSqlDateTime 获取指定列的 SqlDateTime 形式的值GetSqlDecimal 获取指定列的 SqlDecimal 形式的值GetSqlDouble 获取指定列的 SqlDouble 形式的值GetSqlGuid 获取指定列的 SqlGuid 形式的值GetSqlInt16 获取指定列的 SqlInt16 形式的值GetSqlInt32 获取指定列的 SqlInt32 形式的值GetSqlInt64 获取指定列的 SqlInt64 形式的值GetSqlMoney 获取指定列的 SqlMoney 形式的值GetSqlSingle 获取指定列的 SqlSingle 形式的值GetSqlString 获取指定列的 SqlString 形式的值（续表）名称说明GetSqlValue 返回指定列中 SQL Server 类型的数据值GetSqlValues 填充包含记录中所有列的值的 Object 数组，这些值表示为 SQL Server 类型GetSqlXml 获取指定列的 XML 值形式的值GetString 获取指定列的字符串形式的值GetValue 获取以本机格式表示的指定列的值GetValues 获取当前行的集合中的所有属性列NextResult 当读取批处理 Transact-SQL 语句的结果时，使数据读取器前进到下一个结果Read 使 SqlDataReader 前进到下一条记录上面给出的是针对SQL Server数据提供程序的相应常用属性和方法说明。有关其他类型的Command对象和DataReader对象的属性和方法，请读者参考Visual Studio 2008 MSDN的相关介绍。本节后续内容介绍中，将使用到上述属性和方法。9.5.2 演练：使用直接模式数据查询图书列表信息从前面的介绍可知，使用Command对象和DataReader对象可以实现对数据的直接操纵。在使用Command之前，首先需要选择特定的Command类型，然后设置Command的属性，并将Command对象同某个连接绑定起来。为此，可以设置Command对象的一些属性（CommandType、CommandText和Connection），或者将这些信息作为参数传递给构造函数。例如，下面的代码示例显示了如何使用默认的构造函数创建一个数据查询的Command对象并设置其属性：SqlCommand cmd = new SqlCommand();cmd.Connection = con;cmd.CommandType = CommandType.Text;cmd.CommandText = SELECT * FROM User;当然，也可以使用带参数的Command的构造函数来创建Command对象，下面的代码使用了带SQL查询语句的构造函数来创建Command对象：SqlCommand cmd = new SqlCommand(SELECT * FROM User, con)同样，可以使用在Command构造函数中传递一个存储过程，例如下面的示例：SqlCommand cmd = new SqlCommand(GetUsers, con);cmd.CommandType = CommandType.StoredProcedure;上述这些示例只是创建了Command对象，实际上并没有执行对应的SQL语句。Command对象提供了3种方法，用于执行命令。执行什么样的命令依赖于是否需要提供所有的数据集、提取单个数据还是执行一个非查询的命令。下面给出了执行SQL语句的Command方法：（具体说明参考【表11-4】）。l ExecuteNonQuery()。l ExecuteScalar()。l ExecuteReader()。当使用了Command对象执行了SQL命令之后，如果有返回结果，就可以使用DataReader对象读取数据。DataReader对象是以顺序的、只读的方式读取用Command对象获得的数据结果集。由于 DataReader只执行读操作，并且每次只在内存缓冲区里存储结果集中的一条数据，所以效率比较高。如果要查询大量数据，同时不需要随机访问和修改数据，DataReader是最好的选择。DataReader对象的常用属性如下所示：l FieldCount属性，该属性用来表示由DataReader得到的一行数据中的字段数。l HasRows属性，该属性用来表示DataReader是否包含数据。l IsClosed属性，该属性用来表示DataReader对象是否关闭。同Command对象在SQL Server数据提供程序中的SqlCommand对象类似，在SQL Server数据提供程序中的DataReader对象，称为SqlDataReader，而在OLE DB 数据提供程序中称为OleDbDataReader。DataReader对象使用指针的方式来管理所连接的结果集，它的常用方法如下所示。l Close()方法：该方法不带参数，无返回值，用来关闭DataReader对象。由于DataReader在执行 SQL命令时一直要保持同数据库的连接，所以在DataReader对象开启的状态下，该对象所对应的Connection连接对象不能用来执行其他操作。所以，在使用完DataReader对象时，一定要使用Close()方法关闭它，否则不仅会影响到数据库连接的效率，而且还会阻止其他对象使用Connection连接对象来访问数据库。l bool Read()方法：该方法会让记录指针指向本结果集中的下一条记录，返回值为True或False。当Command的ExecuteReader方法返回DataReader对象后，需要用Read()方法来获得第一条记录。当读好一条记录想获得下一条记录时，也可以用Read()方法。如果当前记录已经是最后一条，调用Read()方法将返回False。也就是说，只要该方法返回True，则可以访问当前记录所包含的字段。l bool NextResult()方法：该方法会让记录指针指向下一个结果集。当调用该方法获得下一个结果集后，依然要用Read()方法开始访问该结果集。l Object GetValue(int i)方法：该方法根据传入的列的索引值，返回当前记录行里指定列的值。由于事先无法预知返回列的数据类型，所以该方法使用Object类型来接收返回数据。l int GetValues (Object values)方法：该方法会把当前记录行里所有的数据保存到一个数组中并返回。可以使用FieldCount属性来获知记录里字段的总数，据此定义接收返回值的数组长度。l bool IsDBNull(int i)方法：该方法的参数用来指定列的索引号及判断指定索引号的列的值是否为空，返回True或False。下面的示例显示了如何执行直接数据操纵。1查询数据当需要查询数据时，可以使用ExecuteReader()方法。例如，下面的代码示例说明了如何创建一个简单的Command对象：protected void Page_Load(object sender, EventArgs e) / 创建Command 和Connection对象string connectionString = Data Source=(local); + Integrated security=SSPI;Initial Catalog=BookShopOnlineDB; SqlConnection con = new SqlConnection(connectionString); string sql = SELECT * FROM Book; SqlCommand cmd = new SqlCommand(sql, con); .然后打开连接，通过ExecuteReader()方法执行命令，返回SqlDataReader，如下所示：. / 打开连接，获得DataReader对象 con.Open(); SqlDataReader reader = cmd.ExecuteReader(); .一旦得到了DataReader对象，就可以在while循环中调用Read()方法循环访问结果记录。Read()方法向前移动行游标，读取下一条记录。而且，Read()方法同时返回一个Boolean值，指示是否到达结果集的最后。每条记录的信息构成了单个大的字符串。为了保证字符串操作能够高效执行，通常使用StringBuilder对象，而不是使用传统的String对象。例如下面的示例： . / 对记录执行循环操作，创建HTML字符串 StringBuilder htmlStr = new StringBuilder(); while (reader.Read() htmlStr.Append(); htmlStr.Append(readerBookName); htmlStr.Append( ); htmlStr.Append(reader.GetString(1); htmlStr.Append(, ); htmlStr.Append(); .最后一步是关闭Reader和连接对象，生成服务器控件的文本：. reader.Close(); con.Close(); HtmlContent.Text = htmlStr.ToString();如果运行该页面，则可以看到如图9-11所示的执行结果。图9-11 图书信息上述示例使用了SQL Server数据提供程序的SqlCommand和SqlDataReader对象，实现从网上书店应用查询数据。使用DataReader获得最佳性能，下面是一些实践建议：l 在访问相关Command的任何输出参数之前，必须关闭DataReader。l 完成读数据之后总是要关闭DataReader。如果使用Connection只是用于返回DataReader，那么关闭DataReader之后立刻关闭它。另外一个显式关闭Connection的方法是将CommandBehavior.CloseConnection传递给ExecuteReader方法，以确保相关的连接在关闭DataReader时被关闭。如果从一个方法返回DataReader，而且不能控制DataReader或相关连接的关闭，则这样做特别有用。l 不能在层之间远程访问DataReader。DataReader是为已连接好的数据访问设计的。l 当访问列数据时，使用类型化访问器，例如，GetString、GetInt32等。这使你不用进行将GetValue返回的Object强制转换成特定类型所需的处理。l 一个单一连接每次只能打开一个DataReader。在ADO中，如果打开一个单一连接，并且请求两个使用只进、只读游标的记录集，那么ADO会在游标生存期内隐式打开第二个、未池化的到数据存储区的连接，然后再隐式关闭该连接。对于ADO.NET，“秘密”完成的动作很少。如果想在相同的数据存储区上同时打开两个DataReaders，就必须显式创建两个连接，每个DataReader一个。这是ADO.NET为池化连接的使用提供更多控制的一种方法。l 在默认情况下，DataReader每次Read时都要将整行加载到内存。这允许在当前行内随机访问列。如果不需要这种随机访问，为了提高性能，就将CommandBehavior.SequentialAccess传递给ExecuteReader调用。这将DataReader的默认行为更改为仅在请求时将数据加载到内存。需要注意的是，CommandBehavior.SequentialAccess要求顺序访问返回的列。也就是说，一旦读过返回的列，就不能再读它的值了。l 如果已经完成读取来自DataReader的数据，但仍然有大量挂起的未读结果，就在调用DataReader的Close之前先调用Command的Cancel。调用DataReader的Close会导致在关闭游标之前检索挂起的结果并清空流。调用Command的Cancel会放弃服务器上的结果，这样，DataReader在关闭的时候就不必读这些结果了。如果要从Command返回输出参数，还要调用Cancel放弃它们。如果需要读取任何输出参数，不要调用Command的Cancel，只要调用DataReader的Close即可。l 用DataReader检索二进制大对象（BLOB）时，应该将CommandBehavior. SequentialAccess传递给ExecuteReader方法调用。因为DataReader的默认行为是每次Read都将整行加载到内存，又因为BLOB值可能非常大，所以结果可能由于单个BLOB而使大量内存被用光。SequentialAccess将DataReader的行为设置为只加载请求的数据。然后还可以使用GetBytes或GetChars控制每次加载多少数据。记住，在使用SequentialAccess时，不能不按顺序访问DataReader返回的不同字段。也就是说，如果查询返回三列，其中第三列是BLOB，并且想访问前两列中的数据，就必须在访问BLOB数据之前先访问第一列的值，然后访问第二列的值。这是因为现在数据是按顺序返回的，并且DataReader一旦读过该数据，该数据就不再可用了。2使用ExecuteNonQuery()方法当需要执行数据更新、插入和删除操作时，可以使用ExecuteNonQuery()方法。ExecuteNonQuery()方法用于执行不需要返回结果集的SQL命令，返回所影响的记录数。例如，下面的代码显示了如何使用ExecuteNonQuery()方法执行INSERT命令，实现数据的插入操作：SqlConnection con = new SqlConnection(connectionString);string sql = DELETE FROM Users WHERE UserID= + userid.ToString();SqlCommand cmd = new SqlCommand(sql, con);try con.Open(); int numAff = cmd.ExecuteNonQuery(); HtmlContent.Text += string.Format( 删除 0 记录, numAff);catch (SqlException exc) HtmlContent.Text += string.Format( 错误消息: 0, exc.Message);finally con.Close();对于INSERT、UPDATE命令，可以类似定义，将所定义的SQL命令字符串作为Command对象的参数，并调用ExecuteNonQuery()方法执行该SQL命令，实现数据的直接插入和更新操作。3执行带参数的命令很多时候，要根据特定的查询参数执行查询、更新、插入和删除操作。为此，可以使用带参数化的SQL命令。参数化SQL命令是在SQL文本中使用一个位置占位符。位置占位符指示通过Command对象的Parameter集合动态赋予参数值。例如，对于如下SQL语句：SELECT * FROM Book WHERE BookID = 1上述SQL语句，可以使用如下形式：SELECT * FROM Book WHERE BookID = BID对于不同的数据提供程序，参数化命令的语法略有不同。对于SQL Server数据提供程序，参数化命令使用命名的位置占位符（具有唯一的名称）。对于OLE DB数据提供程序，每个硬性编码的值使用一个问号代替。在这两种情况下，都需要为每个参数化提供一个Parameter对象，将其插入到Command.Parameters集合中。使用OLE DB数据提供程序，必须保证所添加的参数化同SQL字符串中的参数化顺序保持一致，而对于SQL Server数据提供程序，则不需要，因为参数是基于它们的名字进行匹配的。修改前面的示例，根据输入的出版社名称，查询某个出版社的图书信息，窗体页面设计如图9-12所示。下面的代码给出了其参数化查询SQL语句的定义，注意使用了位置占位符“”，并对参数进行了命名“bookpress”，利用Command对象的Parameters集合的AddWithValue()方法添加参数及其值： protected void btnQuery_Click(object sender, EventArgs e) string connectionString = Data Source=(local); + Integrated security=SSPI;Initial Catalog=BookShopOnlineDB; SqlConnection con = new SqlConnection(connectionString); string sql = SELECT * FROM Book WHERE Press =bookpress; SqlCommand cmd = new SqlCommand(sql, con); cmd.Parameters.AddWithValue(bookpress, txbPress.Text); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); StringBuilder htmlStr = new StringBuilder(); while (reader.Read() htmlStr.AppendLine(readerBookName.ToString(); reader.Close(); con.Close(); lbPress.Text = txbPress.Text; HtmlContent1.Value = htmlStr.ToString(); 执行结果如图9-13所示。图9-12 窗体页面设计 图9-13 执行结果从执行结果可以看出，根据参数的值对数据进行过滤，显示指定出版社的图书信息。对于INSERT、UPDATE、DELETE语句，可以采用类似的方式进行定义，然后调用Command对象的ExecuteNonQuery执行插入、更新和删除操作，例如下面的示例。添加数据的代码示例： / 第一步: 格式化包含SQL语句的字符串 string insertString = INSERT INTO Customers ( + CustomerID, CompanyName, ContactName, Address + ) VALUES ( + CustomerID, CompanyName, ContactName, Address + ); / 第二步：创建SqlCommand对象保持SQL语句 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); / 第三步:设置SqlCommand对象的CommandText属性为SQL字符串 mySqlCommand.CommandText = insertString; / 第四步：通过SqlCommand对象参数属性的add()方法 / 添加参数到SqlCommand对象中 mySqlCommand.Parameters.Add(CustomerID, SqlDbType.NChar, 5); mySqlCommand.Parameters.Add(CompanyName, SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add(ContactName, SqlDbType.NVarChar, 30); mySqlCommand.Parameters.Add(Address, SqlDbType.NVarChar, 60); / 第五步：使用值属性设置参数值 mySqlCommand.ParametersCustomerID.Value = T1COM; mySqlCommand.ParametersCompanyName.Value = T1 Company; mySqlCommand.ParametersContactName.Value = Jason Price; mySqlCommand.ParametersAddress.Value = 1 Main Street; / 第六步：使用ExecuteNonQuery()方法执行SQL语句 mySqlCommand.ExecuteNonQuery(); 更新数据代码示例： / 第一步：格式化SQL语句 string updateString = UPDATE Customers + SET + CompanyName = CompanyName, + ContactName = ContactName, + Address = Address + WHERE CustomerID = CustomerID; / 第二步：创建SqlCommand对象保持SQL语句 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); /第三步:设置SqlCommand对象的CommandText属性为SQL字符串 mySqlCommand.CommandText = updateString; / / 第四步：通过SqlCommand对象参数属性的add()方法 / 添加参数到SqlCommand对象中 mySqlCommand.Parameters.Add(CustomerID, SqlDbType.NChar, 5); mySqlCommand.Parameters.Add(CompanyName, SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add(ContactName, SqlDbType.NVarChar, 30); mySqlCommand.Parameters.Add(Address, SqlDbType.NVarChar, 60); / 第五步：使用值属性设置参数值 mySqlCommand.ParametersCustomerID.Value = T1COM; mySqlCommand.ParametersCompanyName.Value = Widgets Inc.; mySqlCommand.ParametersContactName.Value = John Smith; mySqlCommand.ParametersAddress.Value = 1 Any Street; / 第六步：使用ExecuteNonQuery()方法执行SQL语句 mySqlCommand.ExecuteNonQuery(); 删除数据的代码示例： /格式化SQL语句 string deleteString = DELETE FROM Customers + WHERE CustomerID = CustomerID; / 第二步：创建SqlCommand对象保持SQL语句 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); / 第三步:设置SqlCommand对象的CommandText属性为SQL字符串 mySqlCommand.CommandText = deleteString; / 第四步：通过SqlCommand对象参数属性的add()方法 / 添加参数到SqlCommand对象中 mySqlCommand.Parameters.Add(CustomerID, SqlDbType.NChar, 5); /第五步：使用值属性设置参数值 mySqlCommand.ParametersCustomerID.Value = T1COM; /第六步：使用ExecuteNonQuery() 方法执行SQL语句 mySqlCommand.ExecuteNonQuery(); 4调用存储过程使用ExecuteNonQuery()方法可以实现对存储过程的调用，不过要求存储过程不返回值，如执行插入、更新和删除操作的存储过程。例如下面的示例：SqlConnection sqlConnection1 = new SqlConnection(Your Connection String);SqlCommand cmd = new SqlCommand();Int32 rowsAffected;cmd.CommandText = StoredProcedureName;cmd.CommandType = CommandType.StoredProcedure;cmd.Connection = sqlConnection1;sqlConnection1.Open();rowsAffected = cmd.ExecuteNonQuery();sqlConnection1.Close();上面的代码说明了如何执行存储过程。下面我们给出一个更为具体的示例。假定在SQL Server中创建如下存储过程，该存储过程带有3个参数：CREATE PROCEDURE InsertEmployee TitleOfCourtesy varchar(25), LastName varchar(20), FirstName varchar(10), EmployeeID int OUTPUTASINSERT INTO Employees (TitleOfCourtesy, LastName, FirstName, HireDate) VALUES (TitleOfCourtesy, LastName, FirstNam