第八講數(shù)據(jù)庫編程.ppt

第8講數(shù)據(jù)庫編程 數(shù)據(jù)庫基礎(chǔ)知識數(shù)據(jù)庫訪問技術(shù)ADO NET數(shù)據(jù)庫訪問 8 1數(shù)據(jù)庫基礎(chǔ)知識 基本概念 數(shù)據(jù)庫關(guān)系模型表主關(guān)鍵字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數(shù)據(jù)庫訪問技術(shù) ADO NET作用與ADO相同 但與 NETFrameWork無縫集成 能訪問XML 提供斷開式的數(shù)據(jù)訪問模型 8 3ADO NET數(shù)據(jù)庫訪問 ADO NET組成 分 NET數(shù)據(jù)提供程序和DataSet兩部分 前者用以實現(xiàn)各類數(shù)據(jù)操作 后者是ADO NET斷開式數(shù)據(jù)訪問模型的核心 依靠DataAdapter類與數(shù)據(jù)庫通信 NETFramework數(shù)據(jù)提供程序的具體實現(xiàn)有兩個 分別是SQLServer NET數(shù)據(jù)提供程序和OLEDB NET數(shù)據(jù)提供程序 前者可高效訪問SQLServer7 0以上版本的數(shù)據(jù)庫 后者更通用 使用兩種 NETDataProvider訪問數(shù)據(jù)庫 ADO NET訪問ACCESS舉例 控制臺輸出數(shù)據(jù) usingSystem usingSystem Data OleDb 人工引入usingSystem Data 人工引入 classProgram staticvoidMain string args OleDbConnectionconn newOleDbConnection stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教學(xué) 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數(shù)據(jù)庫舉例 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 學(xué)生 0 t學(xué)號是 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 教學(xué) 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詳解 向數(shù)據(jù)庫插入一條記錄 usingSystem usingSystem Data OleDb usingSystem Data classProgram staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教學(xué) 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 利用用帶參數(shù)的SQL語句 處理復(fù)雜表和賦變量值更清晰 staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教學(xué) 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 可隱式轉(zhuǎn)換cmd Parameters AddRange parms intcount cmd ExecuteNonQuery Console WriteLine 共 0 行被改變 count 對比 將文本框中的數(shù)據(jù)放入sql語句 cmd CommandText INSERTINTOContact Name Sex BirthDate Values textBox1 Text textBox2 Text textBox3 Text 用帶參數(shù)的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 可隱式轉(zhuǎn)換parms 2 Value Convert ToDateTime textBox3 Text 可隱式轉(zhuǎn)換cmd Parameters AddRange parms DataReader詳解 關(guān)于數(shù)據(jù)集DataSet和綁定 前者對于復(fù)雜數(shù)據(jù)庫操作不適用 后者簡化了操作 但破壞了三層結(jié)構(gòu) 通常不提倡使用 自學(xué) 回顧 連接數(shù)據(jù)庫并執(zhí)行查詢命令 usingSystem usingSystem Data OleDb 自行引入usingSystem Data 自行引入 classProgram staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教學(xué) 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 實際應(yīng)先判斷是否有行 Console WriteLine 0 1 2 3 rd GetInt32 0 rd GetString 1 rd GetBoolean 2 男 女 rd GetDateTime 3 ToShortDateString rd Close 連接數(shù)據(jù)庫并執(zhí)行增刪改命令 usingSystem usingSystem Data OleDb usingSystem Data classProgram staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教學(xué) 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 帶參數(shù)的SQL語句 staticvoidMain string args stringconnStr Provider Microsoft Jet OLEDB 4 0 DataSource D 教學(xué) 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 可隱式轉(zhuǎn)換cmd Parameters AddRange parms intcount cmd ExecuteNonQuery Console WriteLine 共 0 行被改變 count 對比 將文本框中的數(shù)據(jù)放入sql語句 cmd CommandText INSERTINTOContact Name Sex BirthDate Values textBox1 Text textBox2 Text textBox3 Text 用帶參數(shù)的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 可隱式轉(zhuǎn)換parms 2 Value Convert ToDateTime textBox3 Text 可隱式轉(zhuǎn)換cmd Parameters AddRange parms 三層結(jié)構(gòu)開發(fā) 表現(xiàn)層 Client MainForm窗體 常用DataGridView 參考教材 或ListView控件 參考視頻 通過Columns屬性添加字段名 設(shè)置FullRowSelect和Gridlines為Ture 設(shè)置View屬性為Details 業(yè)務(wù)邏輯層 Business ContactBiz類 提供靜態(tài)方法數(shù)據(jù)服務(wù)層 DataAccess ContactDaol類 提供靜態(tài)方法實體模型類 Model ContactInfo類 封裝各字段 對應(yīng)一行 實體類 注意字段 屬性修飾符及可空類型 classContactInfo privateint id publicintId 重構(gòu) 封裝字段 get return id set id value privatestring name publicstringName privatebool sex 可空類型 因數(shù)據(jù)庫中此字段可空publicbool Sex 可空類型 get return sex set sex value privateDateTime birthDate publicDateTime BirthDate privatestring telePhone 引用類型本身就可為空publicstringTelePhone get return telePhone set telePhone value 數(shù)據(jù)服務(wù)層 classContactDao privateconststringCONN STRING Provider Microsoft Jet OLEDB 4 0 DataSource D 教學(xué) 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è)務(wù)邏輯層 classContactBiz publicstaticintInsertInfo ContactInfocontactInfo returnContactDao InsertInfo contactInfo 之前應(yīng)判斷是否重名 publicstaticintDelteInfo intId returnContactDao DeleteInfo Id publicstaticintUpdateInfo ContactInfocontactInfo returnContactDao UpdateInfo contactInfo publicstaticIListGetAllContact returnContactDao GetAllContact publicstaticIListGetByName stringname return ContactDao GetByName name 表現(xiàn)層 控制臺界面 測試使用 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 表現(xiàn)層 Windows窗體界面 菜單 可繼續(xù)添加分組管理等項DataGridView控件 RowHeadersVisible SelectionMode ReadOnly DataSource Rows Columns外觀屬性 見P279 如交替行等事件