第八講數據庫編程.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 再例 注意命令的創(chuàng)建與異常的處理 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和綁定 前者對于復雜數據庫操作不適用 后者簡化了操作 但破壞了三層結構 通常不提倡使用 自學 回顧 連接數據庫并執(zhí)行查詢命令 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 連接數據庫并執(zhí)行增刪改命令 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 三層結構開發(fā) 表現層 Client MainForm窗體 常用DataGridView 參考教材 或ListView控件 參考視頻 通過Columns屬性添加字段名 設置FullRowSelect和Gridlines為Ture 設置View屬性為Details 業(yè)務邏輯層 Business ContactBiz類 提供靜態(tài)方法數據服務層 DataAccess ContactDaol類 提供靜態(tài)方法實體模型類 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 業(yè)務邏輯層 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窗體界面 菜單 可繼續(xù)添加分組管理等項DataGridView控件 RowHeadersVisible SelectionMode ReadOnly DataSource Rows Columns外觀屬性 見P279 如交替行等事件