第9章数据库程序设计

第9章数据库程序设计命名空间：System.Data;System.Data.SqlClient;2主要内容ADO.NET概述数据库的连接直接访问模式，包括参数查询与存储过程调用数据集模式DataViewSQL的语句回顾1、SELECT[ALL|DISTINCT]<字段清单>FROM<表清单>[WHERE<表达式>][ORDERBY<字段名>[ASC|DESC]]说明：

ALL：显示所有满足条件的记录

Distinct：去掉重复的记录

ASC：升序显示记录（缺省为降序）

DESC：降序显示记录SQL的语句回顾例如：（1）Select*From[Users]Where[UserName]=‘lisi'（2）strName=textBox1.Text;select[UserName],[UserPswd]from[Users]where[UserName]='

"+strName+“

'UserIDUserNameUserPswd1Zhangsan123452LisiAbc3Sfzsfz假设表：UsersSQL的语句回顾2、InsertInto<表名>(<字段清单>)Values(<值清单>)例如：

(1)InsertInto[Users]([UserID],[UserName],[UserPswd])Values('4'，'成龙'，'123456‘)(2)insertinto[Users]([UserID],[UserName],[UserPswd])Values('"+id+"','"+name+"','"+pswd+"');id=this.txtUser.Text;name=this.txtUserName.Text;pswd=this.txtUserPswd,Text;SQL的语句回顾3、Update<表名>Set<字段名1>=<表达式1>[,<字段名2>=<表达式2>]….[Where<表达式>]例如：

（1）Update[Users]Set[UserPswd]=‘abc12’Where[UserName]=‘lisi’(2)

update[Users]set[UserID]='"+this.txtUserID.Text+"',[UserName]='"+this.txtUserName.Text+"',[UserPswd]='"+this.txtUserPswd.Text+"'where[UserID]='"+this.txtUserID.Text+"'SQL的语句回顾4、DeleteFrom<表名>[Where<表达式>]例如：DeleteFrom[Users]WhereUserName='lisi'deletefrom[Users]where[UserID]='3'9.1ADO.NETADO.NET满足了ADO无法满足的三个重要需求：提供了断开的数据访问模型提供了与XML的紧密集成提供了与.NET框架的无缝集成ADO.NET的设计目标是：简单地访问关系和非关系数据，统一XML和关系数据访问，支持Internet上的多层应用程序。9.1ADO.NETADO.NET结构模型ADO.NET包含两个核心组件：.NET数据提供程序（DataProvider）和DataSet。图9-1ADO.NET结构模型图表9-2数据提供程序主要对象对象名称说明Connection建立与特定数据源的连接Command数据命令对象，执行用于返回数据、修改数据、运行存储过程以及发送或检索参数信息的数据库命令DataReader数据读取对象，从数据库中读取记录集，包含四种版本：SqlDataReader、OleDbDataReader、OracleDataReader、OdbcDataReaderDataAdapter数据适配器，该对象是连接DataSet对象和数据库的桥梁，DataAdapter使用Command对象在数据库中查询数据，并将数据加载到DataSet中，对DataSet中数据的更改也由其更新回数据库中，使数据库与数据集数据保持一致CommandBuilder为DataAdapter对象创建命令属性或将从存储过程派生参数信息填充到Command对象的Parameters集合中Parameter为Command对象提供参数Transaction事务对象，实现事务操作DataSetDataSet是支持ADO.NET断开式、分布式数据方案的核心对象。DataSet允许从数据库中检索到的数据存放在内存中，可以看作是内存中的数据库。ConstrainConstraintColumnsColumnDataSetTablesTableRelationsRelationRowsRow图9-2DataSet对象模型129.1.2数据访问模式访问模式特点描述直接访问模式某些数据库操作只能通过执行数据命令完成，如创建一个数据表。减少系统开销。通过直接在数据库中读写，可以不必在数据集内存储数据。可以快速、高效的操作数据库。不适合同一时间会有很多并发连接的Web应用。数据集模式提供断开式的访问方式。可以操作多种数据源的数据，如不同数据库、XML文件、电子表格等。一个数据集可以包含多个结果表，并将这些表作为离散对象维护。在分布式应用程序中，方便层间移动数据。可以将数据集内的数据方便地与数据控件进行绑定。9.1.2数据访问模式从ADO.NET结构模型可以看出，直接访问模式（连接）和数据集访问模式（非连接）所使用的对象直接访问模式Connection-Command-返回操作结果Connection-Command-DataReader-返回结果集数据集访问模式Connection-(Command)-DataAdapter-DataSet9.1.3访问数据库的一般步骤：（1）根据使用的数据源，确定使用.NET框架数据提供程序。（2）使用Connection类，创建一个封装连接字符串的连接对象。（3）调用连接对象的Open方法打开连接。（4）使用Command类创建一个封装SQL语句的命令对象。（5）调用命令对象的方法来执行SQL语句。（6）若采用数据集模式，可使用数据集对获得的数据进行操作。若采用直接访问模式，又可分为两种情形：一是，直接执行SQL语句或存储过程完成修改数据库而不返回数据；二是，执行ExecuteReader方法将数据读到数据读取器中，再通过数据读取器来使用数据。这种情况需要使用DataReader对象。（7）有时，使用数据控件，如DataGridView显示数据。（8）用连接对象的Close方法关闭连接。159.2数据库的连接根据数据源的不同，连接对象有四种：SqlConnection、OleDbConnection、OdbcConnection和OracleConnection。连接SQLServer数据库

server=.\\SQLEXPRESS;database=Northwind;IntegratedSecurity=True

连接Oracel数据库server=.;InitialCatalog=Northwind;IntegratedSecurity=True连接Access数据库

Provider=Microsoft.Jet.OLEDB.4.0;DataSource=Test.mdb

9.2数据库的连接----

连接SQLServer数据库publicstaticSqlConnectionGetSQLConnetcion(){stringstrCN=@"server=.;database=Northwind;IntegratedSecurity=True";SqlConnectioncn=null;//声明一个数据库连接对象try{//抛异常cn=newSqlConnection(strCN);cn.Open();returncn;}catch(Exceptionex){throwex;}finally{cn.Close();}}表9-5App.Config配置文件部分代码<configuration>

……<appSettings><!--实例化类型--><addkey="ConnString"value="DataSource=.;InitialCatalog=Northwind;IntegratedSecurity=True"/><addkey="SystemName"value="**系统"/></appSettings>……</configuration>18//获取App.Config配置文件的数据库连接字符串privatestaticreadonlystringcnString=System.Configuration.ConfigurationSettings.AppSettings["ConnString"];publicstaticOracleConnectionGetOracleConnetcion(){OracleConnectioncn=null;try{cn=newOracleConnection(cnString);returncn;}catch(Exceptionex){throwex;}finally{cn.Close();}}199.3直接访问模式SqlCommand类的主要属性（表9-8）属性说明CommandText获取或设置要对数据源执行的Transact-SQL语句、表名或存储过程。CommandTimeout获取或设置在终止执行命令的尝试并生成错误之前的等待时间。CommandType获取或设置一个枚举值，该值指示如何解释CommandText属性。Connection获取或设置SqlCommand实例使用的SqlConnection。Parameters参数集合，用于设置参数，向SQL命令传递数据，执行参数查询。Transaction获取或设置将在其中执行SqlCommand的SqlTransaction。209.3直接访问模式SqlCommand类的主要方法（表9-10）方法名称说明CreateParameter创建SqlParameter对象的新实例。Dispose关闭有关对象，释放资源。ExecuteNonQuery对连接执行SQL语句并返回受影响的行数。ExecuteReader将CommandText发送到Connection并生成一个SqlDataReader。ExecuteScalar执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。ExecuteXmlReader将CommandText发送到Connection并生成一个XmlReader对象。9.3直接访问模式9.3.2ExecuteNonQuery方法ExecuteNonQuery方法用来执行INSERT、UPDATE、DELETE和其他没有返回值的SQL命令。【实例9-1】该实例使用ExecuteNonQuery方法，对Northwind数据库的商品类别表（Categories）进行增删改操作。classSqlDbHelper{publicstaticintExecuteSql(stringstrSql){SqlConnectioncn=null;try{//调用9.2.2节声明的数据库连接方法cn=GetSQLConnetcion();cn.Open();SqlCommandcmd=newSqlCommand(strSql,cn);returncmd.ExecuteNonQuery();}catch(Exceptionex){throwex;}finally{if(cn.State==ConnectionState.Open)cn.Close();}}}publicpartialclassForm1:Form{privatevoidbutton1_Click(objectsender,EventArgse){StringstrSql="INSERTINTO[Categories]([CategoryName],[Description])"+"VALUES('Tools','工具类')";

SqlDbHelper.ExecuteSql(strSql);}23

public

static

intexecuteSql(stringstrSql){

SqlConnectioncn=getSQLConnetcion();

try{

//cn.Open();SqlCommandcmd=newSqlCommand();cmd.CommandText=strSql;cmd.Connection=cn;cmd.CommandTimeout=30;//0cmd.CommandType=CommandType.Text;

returncmd.ExecuteNonQuery();}

…..}9.3直接访问模式9.3.3ExecuteScalar方法ExecuteScalar方法执行一个SQL命令并返回结果集的第1列第1行通常用来执行SQL的Count\AVG\SUM\MIN\MAX等聚合函数【实例9-2】利用ExecuteScalar方法输出商品表（Products）中最高单价（UnitPrice）。publicstaticintExecuteScalar(stringstrSql){SqlConnectioncn=GetSQLConnetcion();//调用9.2.2节声明的数据库连接方法

try{cn.open();SqlCommandcmd=newSqlCommand(strSql,cn);returnConvert.ToInt32(cmd.ExecuteScalar());}catch(Exceptionex){throwex;}finally{if(cn.State==ConnectionState.Open)cn.Close();}}9.3直接访问模式9.3.4ExecuteReader方法DataReader类没有构造函数，所以不能直接实例化它ExecuteReader方法返回一个SqlDataReader对象。该对象是一个简单的数据集，用于从数据源中检索只读、仅向前数据集。读取数据速度快，常用于检索数据量较大的场合。用于执行Select语句【实例9-3】该实例使用ExecuteReader方法和作为结果的SqlDataReader，把Northwind数据库里的Categories表所有类别名称（CategoryName）显示在窗体的listBox1控件中。publicstaticSqlDataReaderExecuteReader(stringstrSql){SqlConnectioncn=GetSQLConnetcion();//调用9.2.2节声明的数据库连接方法try{cn.open();SqlCommandcmd=newSqlCommand(strSql,cn);SqlDataReaderrd=cmd.ExecuteReader();returnrd;}catch(Exceptionex){throwex;}}9.3.5参数查询参数查询参数查询是指在SQL语句中以占位符表示要查询的值，称为参数，执行时再传入要查询的实际值，这将给程序带来更大的灵活性。使用cmd.Parameters.Add方法创建参数对象

参数查询应用实例对数据库进行重复操作时，适合使用参数查询参数化的SQL语句中使用@，如@CategoryName【实例9-4】使用参数查询方式修改商品类别表（Categories）中的记录。stringstrSql="INSERTINTO[Categories]([CategoryName])VALUES(@p)";

SqlCommandcmd=newSqlCommand(strSql,cn);cmd.Parameters.Add("@p",SqlDbType.NVarChar);cmd.Parameters["@p"].Value="abc";cmd.ExecuteNonQuery();9.3.6存储过程数据库中的存储过程类似于C#中的方法存储过程可以带参数，也可以不带参数；可以有返回结果，也可以没有返回结果。1、设计存储过程①在SQLServerManagementStudioExpress环境中，在对象资源管理器窗口中展开Northwind数据库节点，在“可编程性|存储过程”节点上右击鼠标，在弹出菜单中选择“新建存储过程”，将会给出一个创建存储过程的模板②根据模板，创建存储过程9.3.6存储过程2、调用存储过程①创建与数据库的连接②建立命令对象，并指定其Connection属性为已建立的存储过程和连接对象③将CommandType属性设置为CommandType.StoredProcedure④如果命令采用参数，则设置参数⑤创建一个数据读取器对象⑥调用命令的ExecuteReader()，将结果设置到数据读取器中⑦使用DataReader的Read()方法依次读取DataReader中的数据⑧关闭DataReader、数据库连接【实例9-5】该实例利用命令对象调用前面设计的SalesByCate存储过程。输出按照商品类别（海产品，seafood）汇总的销售额。9.4数据集模式图9-1ADO.NET结构模型图首先使用DataAdapter将数据加载(Fill)到DataSet对象，断开和数据库的连接。然后对DataSet中的数据操作完成后，使用DataAdapter的Update方法将更新写回数据库。339.4DataSetDataSet作为数据库的临时数据容器，可以实现数据库的断开式访问。对于DataSet而言，可以一次性将需要的数据装入DataSet中，等操作完成后一次性更新到数据库中。DataSet的数据源并不一定是关系数据库，还可以是文本、XML文件等，无论什么样的数据源，DataSet都提供了一致的编程模型。类型化和非类型化的DataSet

。9.4.2DataAdapter的作用有2个：(1)从数据源中检索数据并填充DataSet表(Tables)(2)将DataSet中数据的更改解析回数据库，达到更新数据库的目的DataSetDataTableDataTableDataAdapterDataAdapter数据源填充更新填充更新9.4.3Fill方法和Update方法DataAdapter的主要方法①Fill()——把从数据源中选取的行添加到DataSet中。②Update()——将DataSet对象中的内容更新到数据库中【实例9-6】设计一个Windows应用程序，在窗体上需要添加一个DataGridView控件。当程序一运行时，将Northwind数据库中Categories表中的数据显示在窗体的DataGridView1控件中。publicstaticDataSetGetDataSet(stringstrSql){using(SqlConnectioncn=GetSQLConnetcion())//调用9.2.2节声明的数据库连接方法{try{SqlCommandcmd=newSqlCommand(strSql,cn);SqlDataAdapterad=newSqlDataAdapter(cmd);DataSetds=newDataSet();ad.Fill(ds);returnds;}catch(Exceptionex){throwex;}finally{cn.Close();}}}获得数据集的事务处理方法publicstaticDataSetGetDataSet(SqlConnectioncn,CommandTypecmdType,SqlTransactiontrans,stringcmdText,paramsSqlParameter[]parameters){using(SqlCommandcmd=newSqlCommand()){try{PrepareCommand(cmd,cn,cmdType,trans,cmdText,parameters);SqlDataAdapterad=newSqlDataAdapter(cmd);DataSetds=newDataSet();ad.Fill(ds);cmd.Parameters.Clear();ad.Dispose();returnds;}catch(SqlExceptionex){throwex;}}} 389.4.4DataTableDataTable是一个很重要的对象，DataSet对象是一个或多个DataTable的集合。在DataTable中插入记录//【实例9-7】DataRowrow=table.NewRow();

在DataTable中选择记录//【实例9-8】DataTable.Select()方法在DataTable中更新记录//【实例9-9】在DataTable中删除记录//【实例9-10】把更改写回数据库

39SqlCommandBuildercb=newSqlCommandBuilder(ad);//Insert