第八讲数据库编程.ppt

第8讲数据库编程 数据库基础知识数据库访问技术ADO NET数据库访问 8 1数据库基础知识 基本概念 数据库关系模型表主关键字MSSQLServer MSAccess MySql OracleSQL语句 增删改查SELECT FROMContactWHERE Sex True ORDERBYNameASCINSERTINTOContact Name Sex Values Mary false UPDATEContactSETBirthDate 1980 2 2 WHEREName Mary DELETEFROMContactWhereName Mary 存储过程 CREATEPROCEDUREUpDateContactByName Name ASUPDATEContactSETContact BirthDate 1982 5 6 WHERE Contact Name Name Go 8 2数据库访问技术 ADO NET作用与ADO相同 但与 NETFrameWork无缝集成 能访问XML 提供断开式的数据访问模型 8 3ADO NET数据库访问 ADO NET组成 分 NET数据提供程序和DataSet两部分 前者用以实现各类数据操作 后者是ADO NET断开式数据访问模型的核心 依靠DataAdapter类与数据库通信 NETFramework数据提供程序的具体实现有两个 分别是SQLServer NET数据提供程序和OLEDB NET数据提供程序 前者可高效访问SQLServer7 0以上版本的数据库 后者更通用 使用两种 NETDataProvider访问数据库 ADO NET访问ACCESS举例 控制台输出数据 usingSystem usingSystem Data OleDb 人工引入usingSystem Data 人工引入 classProgram staticvoidMain string args OleDbConnectionconn newOleDbConnection stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False conn ConnectionString connStr conn Open if conn State ConnectionState Open Console WriteLine ok OleDbCommandcmd conn CreateCommand cmd CommandType CommandType Text cmd CommandText select fromcontactorderbyNameASC OleDbDataReaderrd cmd ExecuteReader while rd Read Console WriteLine 0 1 2 3 rd GetInt32 0 rd GetString 1 rd GetBoolean 2 男 女 rd GetDateTime 3 ToShortDateString rd Close conn Close ADO NET访问MSSQLServer数据库举例 SqlConnectionconn SqlDataReaderreader try conn newSqlConnection stringconnStr DataSource local InitialCatalog students userId bb password bb 也可用saconn ConnectionString connStr conn Open if conn State ConnectionState Open MessageBox Show 接通 SqlCommandcmd conn CreateCommand cmd CommandType CommandType Text cmd CommandText SelectID sNamefromstudent reader cmd ExecuteReader stringoutput while reader Read output string Format 学生 0 t学号是 1 n reader GetString 1 reader GetInt32 0 lblOutput Text lblOutput Text output catch Exceptionex MessageBox Show 操作失败 ex Message finally reader Close conn Close 再例 注意命令的创建与异常的处理 usingSystem usingSystem Data OleDb usingSystem Data classProgram staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False using OleDbConnectionconn newOleDbConnection conn ConnectionString connStr conn Open if conn State ConnectionState Open Console WriteLine ok OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText select fromcontactorderbyNameASC OleDbDataReaderrd cmd ExecuteReader while rd Read Console WriteLine 0 1 2 3 rd GetInt32 0 rd GetString 1 rd GetBoolean 2 男 女 rd GetDateTime 3 ToShortDateString rd Close Command详解 向数据库插入一条记录 usingSystem usingSystem Data OleDb usingSystem Data classProgram staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False using OleDbConnectionconn newOleDbConnection conn ConnectionString connStr conn Open OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText INSERTINTOContact Name Sex BirthDate Values Mary false 1980 5 5 intcount cmd ExecuteNonQuery Console WriteLine 共 0 行被改变 count 更新 UPDATEContactSETtelephone 123456789 Where name Mary 删除 cmd CommandText DELETEFROMContactWhereName Mary 利用用带参数的SQL语句 处理复杂表和赋变量值更清晰 staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False using OleDbConnectionconn newOleDbConnection conn ConnectionString connStr conn Open OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText INSERTINTOContact Name Sex BirthDate Values Name Sex BirthDate OleDbParameter parms newOleDbParameter newOleDbParameter Name OleDbType VarChar 50 newOleDbParameter Sex OleDbType Boolean newOleDbParameter BirthDate OleDbType Date parms 0 Value Mary parms 1 Value true parms 2 Value Convert ToDateTime 1977 5 6 可隐式转换cmd Parameters AddRange parms intcount cmd ExecuteNonQuery Console WriteLine 共 0 行被改变 count 对比 将文本框中的数据放入sql语句 cmd CommandText INSERTINTOContact Name Sex BirthDate Values textBox1 Text textBox2 Text textBox3 Text 用带参数的SQL语句更清晰cmd CommandText INSERTINTOContact Name Sex BirthDate Values Name Sex BirthDate OleDbParameter parms newOleDbParameter newOleDbParameter Name OleDbType VarChar 50 newOleDbParameter Sex OleDbType Boolean newOleDbParameter BirthDate OleDbType Date parms 0 Value textBox1 Text parms 1 Value Convert ToBoolean textBox2 Text 可隐式转换parms 2 Value Convert ToDateTime textBox3 Text 可隐式转换cmd Parameters AddRange parms DataReader详解 关于数据集DataSet和绑定 前者对于复杂数据库操作不适用 后者简化了操作 但破坏了三层结构 通常不提倡使用 自学 回顾 连接数据库并执行查询命令 usingSystem usingSystem Data OleDb 自行引入usingSystem Data 自行引入 classProgram staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False using OleDbConnectionconn newOleDbConnection conn ConnectionString connStr conn Open if conn State ConnectionState Open Console WriteLine ok OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText select fromcontactorderbyNameASC OleDbDataReaderrd cmd ExecuteReader while rd Read 实际应先判断是否有行 Console WriteLine 0 1 2 3 rd GetInt32 0 rd GetString 1 rd GetBoolean 2 男 女 rd GetDateTime 3 ToShortDateString rd Close 连接数据库并执行增删改命令 usingSystem usingSystem Data OleDb usingSystem Data classProgram staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False using OleDbConnectionconn newOleDbConnection conn ConnectionString connStr conn Open OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText INSERTINTOContact Name Sex BirthDate Values Mary false 1980 5 5 intcount cmd ExecuteNonQuery Console WriteLine 共 0 行被改变 count 更新 UPDATEContactSETtelephone 123456789 Where name Mary 删除 cmd CommandText DELETEFROMContactWhereName Mary 带参数的SQL语句 staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False using OleDbConnectionconn newOleDbConnection conn ConnectionString connStr conn Open OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText INSERTINTOContact Name Sex BirthDate Values Name Sex BirthDate OleDbParameter parms newOleDbParameter newOleDbParameter Name OleDbType VarChar 50 newOleDbParameter Sex OleDbType Boolean newOleDbParameter BirthDate OleDbType Date parms 0 Value Mary parms 1 Value true parms 2 Value Convert ToDateTime 1977 5 6 可隐式转换cmd Parameters AddRange parms intcount cmd ExecuteNonQuery Console WriteLine 共 0 行被改变 count 对比 将文本框中的数据放入sql语句 cmd CommandText INSERTINTOContact Name Sex BirthDate Values textBox1 Text textBox2 Text textBox3 Text 用带参数的SQL语句更清晰cmd CommandText INSERTINTOContact Name Sex BirthDate Values Name Sex BirthDate OleDbParameter parms newOleDbParameter newOleDbParameter Name OleDbType VarChar 50 newOleDbParameter Sex OleDbType Boolean newOleDbParameter BirthDate OleDbType Date parms 0 Value textBox1 Text parms 1 Value Convert ToBoolean textBox2 Text 可隐式转换parms 2 Value Convert ToDateTime textBox3 Text 可隐式转换cmd Parameters AddRange parms 三层结构开发 表现层 Client MainForm窗体 常用DataGridView 参考教材 或ListView控件 参考视频 通过Columns属性添加字段名 设置FullRowSelect和Gridlines为Ture 设置View属性为Details 业务逻辑层 Business ContactBiz类 提供静态方法数据服务层 DataAccess ContactDaol类 提供静态方法实体模型类 Model ContactInfo类 封装各字段 对应一行 实体类 注意字段 属性修饰符及可空类型 classContactInfo privateint id publicintId 重构 封装字段 get return id set id value privatestring name publicstringName privatebool sex 可空类型 因数据库中此字段可空publicbool Sex 可空类型 get return sex set sex value privateDateTime birthDate publicDateTime BirthDate privatestring telePhone 引用类型本身就可为空publicstringTelePhone get return telePhone set telePhone value 数据服务层 classContactDao privateconststringCONN STRING Provider Microsoft Jet OLEDB 4 0 DataSource D 教学 dotnet ContactDb mdb PersistSecurityInfo False privateconststringINSERT STRING INSERTINTOContact Name Sex BirthDate TelePhone Values Name Sex BirthDate TelePhone privateconststringDELETE STRING DELETEFROMContactWHEREID ID privateconststringUPDATE STRING UPDATEContactSETName Name Sex Sex BirthDate BirthDate TelePhone TelePhoneWhereID ID privateconststringSELECT ALL STRING SELECT FROMContact privateconststringSELECT BY NAME STRING SELECT FROMContactWhereName Name publicstaticintInsertInfo ContactInfocontactInfo publicstaticintDeleteInfo intId publicstaticintUpdateInfo ContactInfocontactInfo publicstaticIListGetAllContact publicstaticIListGetByName stringname publicstaticintInsertInfo ContactInfocontactInfo using OleDbConnectionconn newOleDbConnection conn ConnectionString CONN STRING conn Open OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText INSERT STRING OleDbParameter parms newOleDbParameter newOleDbParameter Name OleDbType VarChar 50 newOleDbParameter Sex OleDbType Boolean newOleDbParameter BirthDate OleDbType Date newOleDbParameter TelePhone OleDbType VarChar 50 parms 0 Value contactInfo Name parms 1 IsNullable true if contactInfo Sex HasValue parms 1 Value contactInfo Sex elseparms 1 Value DBNull Value parms 2 IsNullable true if contactInfo BirthDate HasValue parms 2 Value contactInfo BirthDate elseparms 2 Value DBNull Value parms 3 IsNullable true if String IsNullOrEmpty contactInfo TelePhone parms 3 Value DBNull Value elseparms 3 Value contactInfo TelePhone cmd Parameters AddRange parms return cmd ExecuteNonQuery publicstaticintDeleteInfo intId using OleDbConnectionconn newOleDbConnection conn ConnectionString CONN STRING conn Open OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText DELETE STRING OleDbParameter parms newOleDbParameter newOleDbParameter ID OleDbType Integer parms 0 Value Id cmd Parameters AddRange parms return cmd ExecuteNonQuery publicstaticIListGetAllContact ListcontactList newList using OleDbConnectionconn newOleDbConnection conn ConnectionString CONN STRING conn Open OleDbCommandcmd newOleDbCommand cmd Connection conn cmd CommandType CommandType Text cmd CommandText SELECT ALL STRING using OleDbDataReaderrd cmd ExecuteReader if rd HasRows while rd Read ContactInfocontactInfo featch rd contactList Add contactInfo returncontactList privatestaticContactInfofeatch OleDbDataReaderrd ContactInfocontactInfo newContactInfo contactInfo Id rd GetInt32 0 contactInfo Name rd GetString 1 if rd IsDBNull 2 contactInfo Sex rd GetBoolean 2 if rd IsDBNull 3 contactInfo BirthDate rd GetDateTime 3 if rd IsDBNull 4 contactInfo TelePhone rd GetString 4 returncontactInfo 业务逻辑层 classContactBiz publicstaticintInsertInfo ContactInfocontactInfo returnContactDao InsertInfo contactInfo 之前应判断是否重名 publicstaticintDelteInfo intId returnContactDao DeleteInfo Id publicstaticintUpdateInfo ContactInfocontactInfo returnContactDao UpdateInfo contactInfo publicstaticIListGetAllContact returnContactDao GetAllContact publicstaticIListGetByName stringname return ContactDao GetByName name 表现层 控制台界面 测试使用 staticvoidMain string args ContactInfocontactInfo newContactInfo contactInfo Name Mary contactInfo Sex true contactInfo BirthDate Convert ToDateTime 1987 5 6 contactInfo TelePhoneintresult ContactBiz InsertInfo contactInfo Console WriteLine 插入 0 行 result Console WriteLine 删除 0 行 ContactBiz DelteInfo 16 Console WriteLine 更新 0 行 ContactBiz UpdateInfo contactInfo ListcontactList List ContactBiz GetAllContact foreach ContactInfocontactincontactList Console WriteLine 0 1 2 3 4 contact Id contact Name contact Sex contact BirthDate contact TelePhone contactList List ContactBiz GetByName Mary foreach ContactInfocontactincontactList Console WriteLine 0 1 2 3 4 contact Id contact Name contact Sex contact BirthDate contact TelePhone 表现层 Windows窗体界面 菜单 可继续添加分组管理等项DataGridView控件 RowHeadersVisible SelectionMode ReadOnly DataSource Rows Columns外观属性 见P279 如交替行等事件