数据库编程.ppt

MicrosoftVisualC 2005 信息系统开发工具 宋宁哲 06 15 第四部分使用数据 一 使用数据库 背景知识 ADO NET是在ADO ActiveXDataObjects 基础上发展的新一代数据存取技术 是微软 NET平台下提出的新的数据访问模型 ADO NET设计了一系列对各类数据的访问形式 并提供了对应的类 类中提供了与对应数据交互的属性和方法 我们可以通过这些属性和方法对各种数据进行存取操作 ADO ActiveXDataObjects 即ActiveX数据对象 是一组优化的访问数据库的专用对象集 在 NET之前的软件一般使用ADO对象 ADO NET 在 NET平台下重新设计的数据访问模型 它提供了一系列对各类数据的访问形式 并提供了对应的类 类中提供了与对应数据交互的属性和方法 编程者可以通过这些属性和方法方便的对各种数据进行存取操作 例如数据库 XML等 背景知识 续 背景知识 续 VS2005开发环境下使用的是ADO NET2 0 在ADO NET2 0中 SQL数据提供程序使用统一的SQL数据访问模型实现对各种使用SQL语句的数据库的数据访问支持 例如Oracle SQLServer DB2 Access等 ADO NET的最大特点 支持对数据的无连接方式的访问 减少与数据库的活动连接数目 背景知识 续 背景知识 续 VS2005开发环境下 可以直接使用SQLServerExpress管理本机数据库 背景知识 续 1 1创建数据库与数据库表 创建数据库的方式 在项目中直接创建数据库 选择菜单的 视图 服务器资源管理器 然后通过 添加数据连接 创建数据库 通过选择菜单的 数据 添加数据源 然后利用向导完成创建过程 例1 1 创建数据库MyDatabase mdf并将其保存在项目所在的文件夹中 然后在数据库中创建表MyTable1 MyTable2和MyTable3 并利用查询设计器创建和运行查询语句 体会系统提供的功能 1 1创建数据库与数据库表 续 1 2ADO NET数据访问对象 ADO NET提供了多种对象模型 比较典型的有 SqlConnection对象 SqlCommand对象 SqlDataReader对象 SqlDataAdapter对象 SqlParameter对象 SqlTransaction对象这些对象提供了对SqlServer数据源的各种不同的访问功能 全部归类在System Data SqlClient命名空间下 1 2 1SqlConnection对象 ADO NET使用SqlConnection对象与SQLServer进行连接 但是要在SqlConnection对象中指定一个连接字符串 SqlConnectionmySqlConn newSqlConnection server localhost database Northwind uid sa pwd sa Stringconnectionstring provider MSDAORA datasource orcl userid SCOTT password tiger OleDbConnectionmyLoeDbConnection newOleDbConnection connectionstring 1 2 2SqlCommand对象 在ADO NET中 有两种操作数据库的方式 1 采用无连接的方式 2 采用保持连接的方式 不论采用哪种方式 都可以通过SqlCommand对象提供的方法传递对数据库操作的命令 并返回命令执行的结果 1 2 2SqlCommand对象 续 在保持连接的方式下操作数据库的一般步骤为 1 创建SqlConnection的实例 2 创建SqlCommand的实例 3 打开连接 4 执行命令 5 关闭连接 1 2 2SqlCommand对象 续 SqlCommand对象提供了多种完成对数据库操作的方法 常用有 1 ExecuteNonQuery该方法执行SQL语句的结果 但不返回命令执行的表数据 仅返回操作所影响的行数 1 2 2SqlCommand对象 续 例1 2 将MyTable2中张三玉的成绩增加10分 1 2 2SqlCommand对象 续 stringconnectionString Properties Settings Default MyDatabaseConnectionString 根据连接字符串创建SqlConnection实例SqlConnectionconn newSqlConnection connectionString 创建SqlCommand实例 并设置SQL语句和使用的连接实例SqlCommandcmd newSqlCommand cmd CommandText update MyTable2 set 成绩 成绩 10where 姓名 张三玉 cmd Connection conn try 1 2 2SqlCommand对象 续 conn Open 将执行SQL语句影响的记录数赋值给numberintnumber cmd ExecuteNonQuery MessageBox Show string Format 修改了 0 条记录 number catch Exceptionerr MessageBox Show err Message 修改记录失败 finally 1 2 2SqlCommand对象 续 if conn State ConnectionState Open conn Close 1 2 2SqlCommand对象 续 2 ExecuteReaderExecuteReader方法提供了只向前的 顺序的快速读取数据库中数据的方法 该方法根据提供的SELECT语句 返回一个可以顺序读取的SqlDataReader对象 编程者可以使用Read方法循环依次读取每个记录中各字段 列 的内容 1 2 2SqlCommand对象 续 例1 3 在ListBox中按自定义格式显示MyTable1的编码对照表 1 2 2SqlCommand对象 续 privatevoidbuttonShowMyTable1 Click objectsender EventArgse SqlConnectionconn newSqlConnection Properties Settings Default MyDatabaseConnectionString SqlCommandcmd newSqlCommand select from MyTable1 conn try conn Open SqlDataReaderr cmd ExecuteReader while r Read true 1 2 2SqlCommand对象 续 listBox1 Items Add string Format 0 1 r 0 r 1 r Close catch Exceptionerr MessageBox Show err Message 读取记录失败 finally conn Close 1 2 2SqlCommand对象 续 3 ExecuteScaler 该方法用于执行SELECT查询 得到的返回结果为一个值的情况 比如使用count函数求表中记录个数或者使用sum函数求和等 1 2 2SqlCommand对象 续 例1 4 统计MyTable2中所有姓王的人数及成绩的和 privatevoidbuttonCount Click objectsender EventArgse SqlConnectionconn newSqlConnection Properties Settings Default MyDatabaseConnectionString SqlCommandcmd newSqlCommand cmd Connection conn try 1 2 2SqlCommand对象 续 conn Open cmd CommandText selectcount from MyTable2 where 姓名 like 王 intrecord int cmd ExecuteScalar cmd CommandText selectsum 成绩 from MyTable2 where 姓名 like 王 doublesumValue Convert ToDouble cmd ExecuteScalar MessageBox Show string Format 有 0 条姓王的记录 合计成绩为 1 record sumValue catch Exceptionerr 1 2 2SqlCommand对象 续 MessageBox Show err Message finally conn Close 1 2 3SqlDataAdapter对象 SqlDataAdapter对象通过无连接的方式完成数据库和本地DataSet之间的交互 一般步骤 1 创建SqlConnection的实例 2 创建SqlDataAdapter的实例 需要的话 根据select语句生成其他SQL语句 3 创建DataSet的实例 4 使用Fill方法将数据库中的表填充到DataSet表中 5 利用DataGridView或者其他控件对象编辑或显示数据 6 需要的话 使用Update方法更新数据库 1 2 3SqlDataAdapter对象 续 SqlDataAdapter对象通过SelectCommand InsertCommand UpdateCommand和DeleteCommand属性为后台数据库提供对应的操作命令 并传递需要的参数 一般情况下 只需要提供SELECT语句和连接字符串创建SqlDataAdapter对象 然后利用SqlCommandBuilder对象生成InsertCommand UpdateCommand和DeleteCommand属性 1 2 3SqlDataAdapter对象 续 例1 5 根据选择的表名称打开指定数据库中的表 并将表中数据通过DataGridView显示出来 1 2 3SqlDataAdapter对象 续 stringtableName radioButtonMyTable1 Checked true MyTable1 MyTable2 stringconnectionString Properties Settings Default MyDatabaseConnectionString SqlConnectionconn newSqlConnection connectionString adapter newSqlDataAdapter select from tableName conn SqlCommandBuilderbuilder newSqlCommandBuilder adapter adapter InsertCommand builder GetInsertCommand adapter DeleteCommand builder GetDeleteCommand adapter UpdateCommand builder GetUpdateCommand 1 2 3SqlDataAdapter对象 续 table newDataTable adapter Fill table dataGridView1 DataSource table privatevoidbuttonSave Click objectsender EventArgse dataGridView1 EndEdit try adapter Update table 1 2 3SqlDataAdapter对象 续 MessageBox Show 保存成功 catch SqlExceptionerr MessageBox Show err Message 保存失败 AssumesthatcustomerConnectionisavalidSqlConnectionobject AssumesthatorderConnectionisavalidOleDbConnectionobject SqlDataAdaptercustAdapter newSqlDataAdapter SELECT FROMdbo Customers customerConnection OleDbDataAdapterordAdapter newOleDbDataAdapter SELECT FROMOrders orderConnection DataSetcustomerOrders newDataSet custAdapter Fill customerOrders Customers ordAdapter Fill customerOrders Orders DataRelationrelation customerOrders Relations Add CustOrders customerOrders Tables Customers Columns CustomerID customerOrders Tables Orders Columns CustomerID foreach DataRowpRowincustomerOrders Tables Customers Rows Console WriteLine pRow CustomerID foreach DataRowcRowinpRow GetChildRows relation Console WriteLine t cRow OrderID usingSystem usingSystem Data usingSystem Data SqlClient classClass1 staticvoidMain Bydefault MARSisdisabledwhenconnecting toaMARS enabledhostsuchasSQLServer2005 Itmustbeenabledintheconnectionstring stringconnectionString GetConnectionString intvendorID SqlDataReaderproductReader null stringvendorSQL SELECTVendorId NameFROMPurchasing Vendor stringproductSQL SELECTProduction Product NameFROMProduction Product INNERJOINPurchasing ProductVendor ONProduction Product ProductID Purchasing ProductVendor ProductID WHEREPurchasing ProductVendor VendorID VendorId using SqlConnectionawConnection newSqlConnection connectionString SqlCommandvendorCmd newSqlCommand vendorSQL awConnection SqlCommandproductCmd newSqlCommand productSQL awConnection productCmd Parameters Add VendorId SqlDbType Int awConnection Open using SqlDataReadervendorReader vendorCmd ExecuteReader while vendorReader Read Console WriteLine vendorReader Name vendorID int vendorReader VendorId productCmd Parameters VendorId Value vendorID Thefollowinglineofcoderequires aMARS enabledconnection productReader productCmd ExecuteReader using productReader while productReader Read Console WriteLine productReader Name ToString Console WriteLine Pressanykeytocontinue Console ReadLine privatestaticstringGetConnectionString Toavoidstoringtheconnectionstringinyourcode youcanretriveitfromaconfigurationfile return DataSource local IntegratedSecurity SSPI InitialCatalog AdventureWorks MultipleActiveResultSets True 1 2 4DataTable对象 ADO NET可以在与数据库断开连接的方式下通过DataSet或DataTable对象进行数据处理 当需要更新数据时才重新与数据源进行连接 并更新数据源 DataTable对象表示保存在本机内存中的表 它提供了对表中行列数据对象的各种操作 可以直接将数据从数据库填充到DataTable对象中 也可以将DataTable对象添加到现有的DataSet对象中 1 2 4DataTable对象 续 例1 6 将SQLServer数据库中的表MyTable1填充到DataTable对象中 并在DataGridView中显示表数据 1 2 4DataTable对象 续 stringconnectionString Properties Settings Default MyDatabaseConnectionString SqlConnectionconn newSqlConnection connectionString SqlDataAdapteradapter newSqlDataAdapter select fromMyTable1 conn DataTabletable newDataTable adapter Fill table dataGridView1 DataSource table 1 2 5DataSet对象 1 创建DataSet对象使用创建的DataSet对象可以完成各种数据操作 注意 利用向导生成的数据库数据源是一个强类型的DataSet以及一对或多对强类型的DataTable和TableAdapter的组合 类型化的DataSet是一个生成的类 是从 NETFramework的一般DataSet类衍生来的 但提供了已定义的架构以及特定于该架构的属性和方法 同时 对于DataSet中的每个表 还生成了特定于该DataSet的附加衍生类 而且每个类都为相关的表提供了特定的架构 属性和方法 当然 也可以直接创建一般的DataSet对象如 DataSetmyDataset newDataSet 1 2 5DataSet对象 续 2 填充DataSet对象创建DataSet后 就可以使用SqlDataAdapter对象把数据导入到DataSet对象中 比如通过Fill方法将数据填充到DataSet中的某个表中 1 2 5DataSet对象 续 例1 7 将SQLServer数据库中的表MyTable2填充到DataSet的某个表中 并在DataGridView中显示表数据 1 2 5DataSet对象 续 privatevoidFormDataSet Load objectsender EventArgse stringconnectionString Properties Settings Default MyDatabaseConnectionString SqlConnectionconn newSqlConnection connectionString 1 2 5DataSet对象 续 SqlDataAdapteradapter newSqlDataAdapter select fromMyTable2 conn DataSetdataset newDataSet 如果不指定表名 则系统自动生成一个默认的表名adapter Fill dataset 可以使用索引引用生成的表dataGridView1 DataSource dataset Tables 0 SqlParameter对象SqlTransaction对象 usingSystem usingSystem Collections Generic usingSystem Text usingSystem Data usingSystem Data SqlClient classProgram staticvoidMain Bydefault MARSisdisabledwhenconnecting toaMARS enabledhostsuchasSQLServer2005 Itmustbeenabledintheconnectionstring stringconnectionString GetConnectionString SqlTransactionupdateTx null SqlCommandvendorCmd null SqlCommandprodVendCmd null SqlCommandupdateCmd null SqlDataReaderprodVendReader null intvendorID 0 intproductID 0 intminOrderQty 0 intmaxOrderQty 0 intonOrderQty 0 intrecordsUpdated 0 inttotalRecordsUpdated 0 stringvendorSQL SELECTVendorID NameFROMPurchasing Vendor WHERECreditRating 5 stringprodVendSQL SELECTProductID MaxOrderQty MinOrderQty OnOrderQty FROMPurchasing ProductVendor WHEREVendorID VendorID stringupdateSQL UPDATEPurchasing ProductVendor SETOnOrderQty OrderQty WHEREProductID ProductIDANDVendorID VendorID using SqlConnectionawConnection newSqlConnection connectionString awConnection Open updateTx awConnection BeginTransaction vendorCmd newSqlCommand vendorSQL awConnection vendorCmd Transaction updateTx prodVendCmd newSqlCommand prodVendSQL awConnection prodVendCmd Transaction updateTx prodVendCmd Parameters Add VendorId SqlDbType Int updateCmd newSqlCommand updateSQL awConnection updateCmd Transaction updateTx updateCmd Parameters Add OrderQty SqlDbType Int updateCmd Parameters Add ProductID SqlDbType Int updateCmd Parameters Add VendorID SqlDbType Int using SqlDataReadervendorReader vendorCmd ExecuteReader while vendorReader Read Console WriteLine vendorReader Name vendorID int vendorReader VendorID prodVendCmd Parameters VendorID Value vendorID prodVendReader prodVendCmd ExecuteReader using prodVendReader while prodVendReader Read productID int prodVendReader ProductID if prodVendReader OnOrderQty DBNull Value minOrderQty int prodVendReader MinOrderQty onOrderQty minOrderQty else maxOrderQty int prodVendReader MaxOrderQty onOrderQty int maxOrderQty 2 updateCmd Parameters OrderQty Value onOrderQty updateCmd Parameters ProductID Value productID updateCmd Parameters VendorID Value vendorID recordsUpdated updateCmd ExecuteNonQuery totalRecordsUpdated recordsUpdated Console WriteLine TotalRecordsUpdated totalRecordsUpdated ToString updateTx Rollback Console WriteLine TransactionRolledBack Console WriteLine Pressanykeytocontinue Console ReadLine privatestaticstringGetConnectionString Toavoidstoringtheconnectionstringinyourcode youcanretriveitfromaconfigurationfile return DataSource local IntegratedSecurity SSPI InitialCatalog AdventureWorks MultipleActiveResultSets True 数据库编程 1 建立连接Connection对象支持三种连接形式分别支持不同的数据库SqlConnection OleDbConnection OdbcConnectionSqlConnection的几个属性 ConnectionString String ConnectionTimeout int 15s 0s为永久等待PacketSize int 8192字节StringConnectionString server localhost database Northwind uid sa pwd sa StringConnectionString server localhost database Northwind integratedsecurity SSPI 数据库编程 2 生成命令Command类有三种 SqlCommand OleDbCommand OdbcCommand其方法 属性基本相同 以SqlCommand为例进行介绍 属性 CommandText String 操作内容CommandTextout int 30 执行命令前等待的时间CommandType CommandType TEXT text StoreProcedure TableDirect Parameters SqlParametersCollection Transaction SqlTransaction 数据库编程 2 生成命令方法 Cancel Void 取消操作CreateParameter SqlParameters 生成命令新参数ExecuteNonQuery int ExecuteReader SqlReader 返回结果集ExecuteScalar object 返回单个值ExecuteXmlReader XmlReader 构造函数 SqlCommand SqlCommand StringCommandText SqlCommand StringCommandText SqlConnectionMyconn SqlCommand StringCommandText SqlConnectionMyconn SqlTransactionmytr 数据库编程 2 生成命令举例 StringConnectionString server localhost database Northwind uid sa pwd sa SqlConnectionmyconn newSqlConnection ConnectionString SqlCommandmycmd newSqlCommand Mycmd Connection myconn Mycmd CommandText select SqlCommandmycmd newSqlCommand select myconn 还可以 SqlCommandmycmd myconn CreateCommand 数据库编程 3 执行命令四个方法 1 ExecuteReader Myconn open SqlDataReadermySqlReader mycmd ExecuteReader While mySqlReader read mySqlReader CustomerId mySqlReader 0 数据库编程 3 执行命令四个方法 1 ExecuteReader Myconn open SqlDataReadermySqlReader mycmd ExecuteReader while mySqlReader Read mySqlReader GetValues values values保存一行数据for inti 0 i values Length i Console Write values i ToString Console WriteLine 举例 绑定在datagridview 数据库编程 3 执行命令四个方法 1 ExecuteReader 举例 绑定在datagridviewExecuteReader 属性 方法 mySqlReader FieldCount 获取结果行中的列数ExecuteReader CommandBehavior SingleRow 只返回一行 ExecuteReader CommandBehavior SchemaOnly 只返回列信息 ColumnName列名ColumnOrdinal列序号ColumnSize长度BaseColumnName数据库中的列名DataType类型 数据库编程 3 执行命令四个方法 2 ExecuteScalar 返回单个值只查汇总信息人为合并为单个信息举例 查登陆信息 数据库编程 3 执行命令四个方法 3 ExecuteXMLReader 在select语句后加入forxml RAW AUTO EXPLICIT XMLDATA ELEMENTS BINARY 返回xml数值举例 返回xml 数据库编程 3 执行命令四个方法 4 ExecuteNonQuery 修改数据库A 插入数据B 修改数据C 删除数据D 生成表E 删除表F 显示所有表G 显示视图 数据库编程 4 使用事务Transaction类有三种 SqlTransaction OleDbTransaction OdbcTransaction其方法 属性基本相同 以SqlTransaction为例进行介绍 步骤 生成SqlTransaction 并用BeginTransaction 方法开始事务将SqlCommand对象的Transaction属性设为已生成的SqlTransaction对象设置命令执行用SqlTransaction的Commit 方法提交事务举例 数据库编程 4 使用事务Transaction类有三种 SqlTransaction OleDbTransaction OdbcTransaction其方法 属性基本相同 以SqlTransaction为例进行介绍 步骤 生成SqlTransaction 并用BeginTransaction 方法开始事务将SqlCommand对象的Transaction属性设为已生成的SqlTransaction对象设置命令执行用SqlTransaction的Commit 方法提交事务在出错时用Sqltransaction Rollback 回滚事务举例 connection Open SqlC