用ADO.NET向数据库发送操作指令.ppt

第4讲 用ADO.NET向数据库发 送指令 事务 系统分析数据库设计UI设计数据库连接数据库查询 离线数据管理安全性设计XML数据交换程序发布部署 -1- 数据库技术应用数据库技术应用袁志袁志 课程回顾 l上次课，介绍了ADO.NET组件和建立数据库连接 的方法。 l在BookStore系统中，我们使用SqlConnection对象 连接SQL Server 数据库文件。为了能够在程序完 成后可以修改连接字符串以适应数据库位置的改 变，把连接字符串保存在配置文件中。把连接对 象封装在一个公共类DAC中，供各所有功能模块共 享。 l现在我们知道了应用程序如何连接数据库，下面 开始学习应用程序如何向数据库发送操作命令。 2 课程内容 l本次课，讲述使用Command对象向数据库发送增 删改查的操作指令。我们将了解Command对象的 主要属性和方法。对于不同的操作目标，我们为 Command对象选用不同的执行方式。最后通过 BookStore系统中的和“新书登记”和“图书信息”两 个模块的代码对本课的知识点进行综合演示。 l目录： 1. Command对象的属性和方法 2. Command对象的执行方式 3. 在查询语句中使用变量 4. 案例分析 3 1.Command对象的属性和方法 l数据库连接之后，可以向 数据库发送操作指令了， 此时用到Command对象。 l在数据库中，增删查改数 据的操作称为DML，创建 、修改数据库、表、视图 等操作称之为DDL。 Command对象可以发送 DML和DDL。 lCommand对象属于数据提 供程序的一部分，有四个 版本，分别有不同的前缀 ，比如SqlCommand, OleDbCommand。 这里只 讲述SqlCommand。 4 1.Command对象的属性和方法 Command对象的属性 CommandText要对数据源执行的SQL语句或存储过程或表 名 CommandType有三个可取值： CommandType.Text(默认值)：表示 CommandText是一条SQL语句， CommandType.Procedure：表示 CommandText是一个存储过程名， CommandType.TableDirect：表示 CommandText是一个表名 Connection要使用的Connection Transaction所在的事务 ParametersParameter的集合 Command对象的方法 ExecuteNonQ uery() 执行命令并返回受影响的行数 ExecuteScal ar() 执行命令并返回查询结果集中第一行的第 一列。忽略额外的列或行 ExecuteRead er() 执行命令并返回一个DataReader对象 ExecuteXmlR eader() 执行命令并生成一个XmlReader对象 5 1.Command对象的属性和方法 l创建Command对象有三种方式 1，使用new关键字，调用Command对象的无参 构造函数，再进行属性设置，代码示例： SqlCommand cmd=new SqlCommand(); cmd.CommandText=“Select * From TBook”； cmd.CommandType=CommandType.Text; /缺省值可省略 cmd.Connection=conn; /conn是一个SqlConnection对象 2，使用new关键字，但使用构造参数来指定查询 字符串和Connection对象，代码示例： SqlCommand cmd=new SqlCommand(“Select * From TBook”,conn)； 3，通过Connection对象的CreateCommand方法 。代码示例： SqlCommand cmd=cn.CreateCommand(); 6 1.Command对象的属性和方法 l讨论：关于ComandType lCommandType说明CommandText的含义，它是一个枚举 值，有三个可能： 1，CommandType.Text(默认值)：表示CommandText是 一条SQL语句，例如执行一条SQL命令，代码如下： SqlCommand cmd=new SqlCommand(“Delete TBook Where BookId=798,cn); cmd.CommandType=CommandType.Text; 2，CommandType.Procedure：表示CommandText是一 个存储过程名，例如执行存储过程sp1，代码如下： SqlCommand cmd=new SqlCommand(“sp1”,cn); cmd.CommandType=CommandType.Procedure; 3，CommandType.TableDirect：表示CommandText是一 个表名，例如，获取一个表的全部数据，代码如下: SqlCommand cmd=new SqlCommand(“TBook”,cn); cmd.CommandType=CommandType.TableDirect; 7 2. Command对象的执行方式 lCommand对象构造之后， 就要选择它的执行方法了。 Command对象有四种方法: pExecuteScalar() pExecuteNonQuery() pExecuteDataReader() pExecuteXMLDataReader( ) 8 2. Command对象的执行方式 l2.1 ExecuteNonQuery()更改查询 l有2种用途。 p一是使用数据操纵语言(DML)查询 例：Update TBook SET Status=借出 WHERE BookId=798 例：INSERT INTO TLend（BookId，ReaderId，LendDate） Values (798,0111,2010-5-6) 例：DELETE FROM TBook WHERE BookId=798 p二是数据定义语言(DDL)查询，此种类型常用于更改数 据库结构 例：CREATE TABLE table1 (Field1 int NOT NULL CONSTRAINT PK_Table1 PRIMARY KEY, Field2 varchar(32) 例：ALTER VIEW View1 AS SELECT Field1,Field2 FROM table1 例：DROP PROCEDURE StoredProcedure1 9 2.Command对象的执行方式 l2.1 ExecuteNonQuery() p代码演示 SqlConnection cn=new SqlConnection； cn.ConnectionString=； cn.Open()； SqlCommand cmd=cn.CreateCommand() cmd.CommandText= “DELETE FROM TBook WHERE BookId=798” ； cmd.ExecuteNonQuery()； cn.Close(); 10 2.Command对象的执行方式 l2.1 ExecuteNonQuery() l讨论 p不返回结果集，但是返回操作所影响的行数 pSQL语法错误可能会导致方法执行失败。 p更改查询并不总能执行，例：“Update TBook SET Status=借出 WHERE BookId=798”无法 正确执行可能会因为表中没有满足条件的行， 对于数据库来说，这样的结果并不表示失败. p如何辨别更改查询是否生效了呢?答案是通过获 取受影响的行数来判断。 11 2.Command对象的执行方式 l2.1 ExecuteNonQuery() l通过获取受影响的行数来判断更新查询是否做生效，代码 演示： cmd.CommandText= “Update TBook SET Status=借 出 WHERE BookId=798”; int recAffected=cmd.ExecuteNonQuery(); if(recAffected=1) Console.WriteLine(“Update Succeeded“); else Console.WriteLine(“Update Failed“); l特别注意：如果执行的是DDL查询，那么 ExecuteNonQuery方法返回值是-1 12 l2.2 Command.ExecuteReader()返回结果 集的查询 l返回值是DataReader对象，可以通过DataReader 对象检查查询结果， p 代码演示 .(省略-打开Connection对象cn的过程). SqlCommand cmd=new SqlCommand(“SELECT Title,Author FROM TBook“,cn) SqlDataReader dr=cmd.ExecuteReader(); while (dr.read() Console.WriteLine(dr“Title“+ “-“ + dr“Author“) dr.Close(); cn.Close(); 2.Command对象的执行方式 13 2.Command对象的执行方式 l2.2 Command.ExecuteReader() l讨论 p关于DataReader对象的Read方法，只有在你调用 Read()方法之后，数据的第一行才可用，所以循环中要 在显示之前就调用Read方法，调用一次Read方法， DataReader会自动移动到下一行。Read()方法返回值 为布尔类型，表示是否读到一行。 pdr“Title”访问的是当前行的Title列的值。 p要尽可能快速的关闭DataReader对象。一个带着开放 的DataReader的Connection对象仍是锁定的，如果在 关闭DataReader对象之前，试图打开第二个 DataReader对象，会导致异常：”需要一个开放并可用 的连接” 14 2.Command对象的执行方式 l2.2 Command.ExecuteReader() l处理多结果集 l某些数据库(比如SQL SERVER)支持执行返回多个 结果的批查询.注意，Access不支持 l假设有如下查询: SELECT CustomerID,CompanyName,ContactName FROM Customers; SELECT OrderID,CustomerID,EmployeeID,OrderData FROM Orders; SELECT OrderID,ProcedureID,Quantity,UnitPrice FROM Order Details l使用前面的方法,用DataReader对象在循环中 Read()只能在批查询中循环第一个查询的结果。 解决这个问题需要使用DataReader对象提供的 NextResult方法。该方法可以使你移动到下一个返 回行的查询结果上去。该方法返回一个布尔值， 表示是否成功转移到下一结果集。 15 2.Command对象的执行方式 l 2.2 Command.ExecuteReader() l使用NextResult()处理多结果集，代码演示 cn.Open(); string strsql =” SELECT * From TBook；SELECT * FROM TReader” ; SqlCommand cmd=new SqlCommand(sql,cn); SqlDataReader dr=cmd.ExecuteReader(); do while (dr.Read() Console.WriteLine(dr0 + “_” + dr1); Console.WriteLine(); while dr.NextResult(); 16 2.Command对象的执行方式 l2.3 Command.ExecuteScalar（）单值查询 l查询举例: Select COUNT(*) from TBook Select Title from Customers where BookId=798 l如上查询,用DataReader有点大材小用。ExecuteScalar()专 门获取单值查询，本质上是返回查询结果的第一行第一列 的值，由于值的类型实现不可知，因此其返回值类型设计 为Object。 l代码演示 cn.Open(); SqlCommand cmd=cn.CreateCommand(); cmd.CommandText=”select COUNT(*) from TBook”; int count=Covert.ToInt32(cmd.ExecuteScalar(); /获得查询值 cmd.CommandText=”select Title from TBook where BookId=798”; string title=Convert.ToString(cmd.ExecuteScalar(); /获得查询值 17 3. 在查询语句中使用变量 l参数化查询关于 Command对象的 Parameters属性 l我们注意到前面写的SQL 语句中没有变量。比如： Delete From TBook Where BookId=798 l在实际应用程序中，798 是事先不可知的，它的值 由一个变量提供。更复杂 的操作可能涉及到多个变 量。这些变量就成为SQL 语句的参数。 18 3.在查询语句中使用变量 l第一种方法：用字符串连接，来输入参数值。 l举例：有一个整形变量bookId，从数据库的TBook 表中删除BookId等于该bookId的行。 string sql=“Delete From TBook Where BookId=“+bookId; SqlCommand cmd=new SqlCommand(sql,cn) cn.Open(); cmd.ExecuteNonQuery(); cn.Close() 19 3. 在查询语句中使用变量 l第二种方法：用string.Format输入参数值 l举例，有3个变量，userId,userName,pwd,构成数 据库的TUser的一行，要求插入这一行。代码如下 string sql=string.Format(“Insert Into TUser(UserId,UserName,Pwd) values (0,1,2)”,userId,userName,pwd); SqlCommand.cmd=new (sql,cn); cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); 第一行当然也可以写成下面这样，只是显得不够规整 string sql=“Insert Into TUser(UserId,UserName,Pwd) values (”+userId+”,”+userName+”,”+pwd+”)”; 20 3. 在查询语句中使用变量 l第三种方法：使用的Parameter对象 l在查询字符串中预留参数符号，向Command的 Parameters集合属性中添加若干Parameter，并给 它提供值。使用Parameter要比通过编程连接查询 字符串更清晰。 l代码演示 int bookId=798; cn.Open(); string strSql=”Select * From TBook where BookId=bookId”; SqlCommand cmd=new SqlCommand(strSql,cn); cmd.Parameters.Add(“bookId”, SqlDbT) cmd.Parameters0.Value=bookId; SqlDataReader dr =cmd.ExecuteReader(); cn.Close(); 21 4，案例分析 l结合BookStore系统的两个 功能窗体FrmAddNewBook 和FrmBookInfo的实现过 程，对Command对象、 DataReader对象进行应用 分析。 lFrmAddNewBook用于新书 登记 lFrmBookInfo用于查询修 改图书的详细信息。 22 4. 案例分析 l案例1 ：新书登记窗体FrmAddNewBook 23 4. 案例分析 FrmAddNewBook的代码-1： private void FrmNewBook_Load(object sender, EventArgs e) SqlCommand cmd = D.CreateCommand(); cmd.CommandText = “Select BookType From TBookType“; Dac.Cn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read() cbBookType.Items.Add(Convert.ToString(dr“BookType“); Dac.Cn.Close(); private void btnCancel_Click(object sender, EventArgs e) this.Close(); 24 FrmAddNewBook的代码-2： private void btnConfirm_Click(object sender, EventArgs e) if (checkInput() addNewBook(); private bool checkInput() if( txtISBN.Text=“”) return false; if (txtTitle.Text=“”) return false; if (txtAuthor.Text=“”) return false; if (txtPrice.Text=“”)return false; if (txtPublisher.Text=“”)return false; if (txtPublisherYear.Text=“”) return false; if (cbBookType.Text=“”) return false; return true; 25 lFrmAddNewBook的代码-3： private void addNewBook() Dac.Conn.Open(); SqlCommand cmd = Dac.Conn.CreateCommand(); /取得新的图书编号 cmd.CommandText = “Select Max(BookId) From TBook“; int bookId = Convert.ToInt32(cmd.ExecuteScalar() + 1; /增加图书 cmd.CommandText = string.Format(“Insert Into TBook(ISBN,Title,Author,Publisher,PublishYear,Price,Status,BookType) Values(0,1,2,3,4,5,6,7)“,txtISBN.Text, txtBookTitle.Text, txtAuthor.Text, txtPublisher.Text, txtPublishYear.Text, Convert.ToDouble(txtPrice.Text), “在库“, cbBookType.Text); cmd.ExecuteNonQuery(); Dac.Conn.Close(); 26 4. 案例分析 l案例2：修改图书信息窗体 27 4. 案例分析 FormBookInfo的代码-1： /私有变量 int bookId; /构造函数用于接收BookId public FrmBookInfo(int _bookId) bookId = _bookId; InitializeComponent(); /取消 private void btnCancel_Click(object sender, EventArgs e) this.Close(); 28 4. 案例分析 FrmBookInfo的代码-2： /窗体启动 private void FrmUpdateBookInfo_Load(object sender, EventArgs e) SqlCommand cmd = Dac.Cn.CreateCommand(); cmd.CommandText = “Select * From TBook Where BookId=“ + bookId; Dac.Cn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); txtISBN.Text = Convert.ToString(dr“ISBN“); txtBookTitle.Text = Convert.ToString(dr“Title“); txtAuthor.Text = Convert.ToString(dr“Author“); txtPublisher.Text = Convert.ToString(dr“Publisher“); txtPublishYear.Text = Convert.ToString(dr“PublishYear“); txtPrice.Text = Convert.ToString(dr“Price“); cbBookType.Text = Convert.ToString(dr“BookType“); dr.Close(); Dac.Cn.Close(); 29 4. 案例分析 FrmBookInfo的代码-3： /确认修改 private void btnConfirm_Click(object sender, EventArgs e) if (checkInput() modifyBookInfo(); /检查输入 private bool checkInput() if( txtISBN.Text=“”) return false; if (txtTitle.Text=“”) return false; if (txtAuthor.Text=“”) return false; if (txtPrice.Text=“”)return false; if (txtPublisher.Text=“”)return false; if (txtPublisherYear.Text=“”) return false; if (cbBookType.Text=“”) return false; return true; 30 4. 案例分析 FrmBookInfo的代码-4： /修改图书信息 private void