如鵬網學習筆記（六）ADO.Net基礎

標籤：system   建表   服務   信任   資料類型   告訴   var   database   number   

ADO.Net基礎

一、ADO.Net簡介
　　1，程式要通過SQL語句自動化的操作資料庫，必須要用一個類庫，
　　　　類庫要提供execute("insert into ...")/executeQuery("select * from ...")類似的方法

　　2，ADO.Net是.Net中提供的標準訪問資料庫的介面，訪問不同的DBMS的底層方法是不一樣的，ADO.Net把訪問資料庫的方法進行了統一，
　　　　訪問MYSql、Oracle、SqlServer等不同資料庫的方法幾乎是一樣的

　　3，ADO.Net是規範，被不同的資料庫廠商提供ADO.Net的實現，稱之為ADO.Net驅動，每個廠商提供的驅動可以用來操作自己的資料庫

二、ADO.Net串連MYSQL

　　1，安裝MySql的.Net驅動mysql-connector-net-***.msi添加到項目的庫中。
　　　　如果安裝遇到問題，則直接下載mysqlnetconnection（V4.5）.zip。

　　2，建立項目，添加引用——“擴充”，添加Mysql.Data;如果是直接解壓版，然後直接添加對MySql.Data.dll的檔案的引用
　　　　static void Main(string[] args)
　　　　{
　　　　　　string connStr = ConfigurationManager.ConnectionStrings["connStr"].ToString();
　　　　　　using (SqlConnection conn = new SqlConnection(connStr))
　　　　　　using (SqlCommand cmd = conn.CreateCommand())
　　　　　　{
　　　　　　　　conn.Open();
　　　　　　　　cmd.CommandText = "insert into t_fuxi ";
　　　　　　　　int count = cmd.ExecuteNonQuery();
　　　　　　　　Console.WriteLine(count+"受到影響");
　　　　　　　　Console.ReadKey();
　　　　　　}
　　　　}

　　　　代碼解釋：
　　　　1，MySQLConnection、MySQLCommend實現了IDisposable介面，因此使用using進行資源回收

　　　　2，"Server=localhost;Database=study1;uid=root;pwd=root;Charset=utf8"叫連接字串，
　　　　　　Server是Mysql伺服器的地址，Database是串連的資料庫，uid、pwd是使用者名稱和密碼，採用utf8編碼

　　　　3，conn.Open();在執行MySqlCommand之前一定要先開啟資料庫連接，否則會報錯

　　　　4，ExecuteNoQuery是執行Update、Insert、Delete等非查詢語句，傳回值為受影響的行數


　　3，ExecuteScalar
　　　　執行查詢，並返回查詢所返回的結果集中第一行的第一列，忽略其他行列。一般用來簡單的獲得自由一行一列的查詢結果的值

　　　　案例1
　　　　cmd.CommandText = "Select count(*) from T_Users";
　　　　long count = (long)cmd.ExecuteScalar();

　　　　案例2
　　　　cmd.CommandText = "Select Password from T_Users where UserName = ‘admin‘";
　　　　string pwd = (string)cmd.ExecuteScalar();
　　　　if(string.isNullOrEmpty(pwd))
　　　　{
　　　　　　Console.WriteLine("找不到admin");
　　　　}
　　　　else
　　　　{
　　　　　　Console.WriteLine("admin的密碼："+pwd);
　　　　}

　　4，執行查詢MySqlDataReader

　　　　cmd.CommandText = "select * from T_Users";
　　　　using(MySqlDataReader reader = cmd.ExecuteReader())
　　　　{
　　　　　　while(reader.Read())
　　　　　　{
　　　　　　　　long id = reader.GetInt64("Id");
　　　　　　　　string userName = reader.GetString("UserName");
　　　　　　　　string passWord = reader.GetString("Password");
　　　　　　　　Console.WriteLine("id="+id+";UserName="+userName+";Password="+passWord);
　　　　　　}
　　　　}

　　　　注意：
　　　　　　Reader的遍曆、讀取時需要Connection保持串連，如果關閉了Connection，使用會出錯
　　　　　　也可以根據列序號擷取列的值，效率略高，不過程式不容易讀；通過reeder.GetOrdinal("Age")擷取列名對應的序號



三、SQL注入漏洞

　　1，是由於Sql語句的拼接造成的，
　　　　在程式接收使用者的輸入時，需要考慮使用者輸入的內容對資料庫操作產生的影響，

　　2，對應的解決方案，參數化查詢

　　　　cmd.CommandText = "select count(*) from TUsers where [email protected] and [email protected]";
　　　　cmd.Parameters.Add(new SqlParameter() { ParameterName = "@UserName", Value = username });
　　　　cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Password", Value = password });

　　　　為什麼這樣可以避免“SQL注入漏洞”，
　　　　因此使用參數化查詢，就可以對使用者輸入的內容進行判斷和處理了，本質上是參數賦值

　　　　注意：
　　　　1，所有SQL中都可以使用參數化查詢傳遞；表名、欄位名等不能使用參數化進行替換
　　　　2，不要用SqlParameter(string parameterName,object value)這個建構函式，
　　　　因為("Age",0)會被匹配成Sqlparameter(string parameterName,SqlDbType dbType)這個建構函式

四、基礎資料型別 (Elementary Data Type)為空白的問題

　　1，把T_Persons表的Name、Age列修改為“允許為空白”，插入一條Name、Age為空白的行
　　　　執行後發現，對於空資料reader.Get**方法會拋異常SqlNullValueException，
　　　　相當於問資料庫“當前行的Name”是什麼，資料庫告訴你“不知道”。

　　　　怎麼解決？
　　　　使用ISDBNull擷取指定序號的列的值是否為null

　　　　int? age = null;
　　　　if(!reader.IsDBNull(reader.GetOrdinal("Age")))
　　　　{
　　　　　　age = reader.GetInt32("Age");
　　　　}

五、離線結果集DataSet

　　DATAReader是伺服器結果集遊標的體現，所有查詢出來的資料都在MySql伺服器上。
　　好處是：當查詢結果資料量打的時候避免佔用本地記憶體。

　　不過大部分項目中都會避免大查詢結果，因此缺點就明顯了
　　讀取的時候必須保持Connection，不僅用起來麻煩，而且會較長時間佔用MySql伺服器的串連資源

　　DataSet是一個離線結果集容器，它把結果資料放到本地記憶體中。
　　因為查詢結果可能會包含多個表，因此DataSet包含若干DataTable（ds.Tables），
　　DataTable包含若干DataRow(dt.Rows)

　　用法1：
　　DataSet ds = new DataSet();
　　MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
　　adapter.Fill(ds);
　　DataTable table = ds.Tables[0];

六、DataTable
　　DataSet可以盛放多個查詢結果集到DataTable

　　DataAdapter還可以對結果進行傻瓜化更新、刪除、修改。

　　我們一般查詢結果集就一個DataTable，DataAdapter的傻瓜化更新不適合正式的項目，因此有更簡單的用法

　　DataTable dt = new DataTable();
　　dt.Load(reader);

　　注意：把DataTable聲明到using外，using外再使用查詢結果

　　遍曆DataTable

　　for(int i =0;i<dt.Rows.Count;i++)
　　{
　　　　DataRow row = dt.Rows[i];
　　　　string name = row.IsNull("name")?null:(string)row["Name"];//NULL處理
　　　　Console.WriteLine("name="+name);
　　}

七、封裝一個庫：分析

　　1，ADO.Net的連接字串寫到設定檔中。

　　2，每次操作資料庫都要寫一坨代碼，太累，因此封裝一個簡化ADO.Net操作的庫出來：
　　　　設定檔的設定連接字串；簡化串連的建立；簡化SQL的執行

　　3，如果一個操作要執行多條SQL語句，如果每條都開啟一個串連——執行——關閉串連的話，效率會非常低，而且會有“事務”的問題。
　　　　因此應該提供“開啟、執行、關閉”這樣的方法，也要提供“使用後現有串連執行的方法”。

　　4，參數化查詢的查詢參數個數不確定，可變長度參數會更方便

　　5，為了方便大部分情況下的小結果集，執行查詢返回DataTable


八、MySqlHelper方法規劃

　　1，public static MySqlConnection CreateConnection()

　　2，public static int ExecuteNonQuery(MySqlConnection conn,string sql,params MySqlParameter[] parameters)

　　3，public static int ExecuteNonQuery(string sql,params MySqlParameter[] parameters)

　　4，public static object ExecuteScalar(MySqlConnection conn,string sql,params MySqlParameter[] parameters)

　　5，public static object ExecuteScalar(string sql,params MySqlParameter[] parameters)

　　6，public static DataTable ExecuteQuery(MySqlConnection conn, string sql, params MySqlParameter[] parameters)

　　7，public static DataTable ExecuteQuery(string sql, params MySqlParameter[] parameters)

九、實現MySqlHelper

　　1，連接字串一般配置到App.config（網站是Web.config）中的<connectionStrings>段中
　　　　然後使用ConfigurationManager類(需要添加對System.Configuration的引用)讀取
　　　　string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString
　　　　

　　　　注意：
　　　　一定要保證代碼中的名字和設定檔中的名字是一致的

　　2，其他方法的實現

　　3，測試幾個方法，調用不需要自己控制串連的，再調用公用一個串連的


十、獲得自動成長欄位的值

　　1，不能用插入後擷取最大值的方法，有並發問題

　　2，要在同一個串連中：select LAST_INSERT_ID()

　　3，可以Insert、LAST_INSERT_ID()在同一個串連中單獨執行，也可以把LAST_INSERT_ID()放到insert語句後面用；
　　　　分割（使用ExecuteScalar執行即可）

十一、事務基礎

　　1，有一個需求，類似於轉賬，從Tom的工資上轉走10元，轉到Jerry的工資上增加10元

　　　　Update T_Employees Set Salary = Salary - 10 where Name = ‘Tom‘
　　　　Update T_Employees Set Salary = Salary + 10 where Name = ‘Jerry‘

　　　　如果執行從Jerry賬上加10元的時候執行出錯（使用SQL文法寫錯誤類比），那麼就會出現總體丟失10元的問題，如果是轉賬呢？

　　2，事務（Transaction）有四大特徵：
　　　　原子性、一致性、隔離性、持久性
　　　　原子性指的是：幾個操作要麼都成功，要麼都失敗

十二、ADO.Net事務

　　1，要在一個串連中（否則要涉及到分散式交易）
　　　　MySqlTransaction tx = conn.BeginTransaction();
　　　　操作結束後執行tx.Commit()提交事務；
　　　　如果執行出錯，則tx.Rollback()復原（當前事務的操作全部取消）

　　　　範例程式碼：
　　　　MySqlTransaction tx = conn.BeginTransaction();
　　　　try
　　　　{
　　　　　　MySqlHelper.ExecuteNonQuery(conn,"Update T_Accounts set Amount = Amount-1000 where Number =‘0001‘");
　　　　　　string s = null;
　　　　　　s.ToLower();//製造異常
　　　　　　MySqlHelper.ExecuteNonQuery(conn, "Update t_accounts Set Amount=Amount+1000 where Number=‘0002‘");
　　　　　　tx.Commit();
　　　　}
　　　　catch(Exception ex)
　　　　{
　　　　　　tx.Rollback();
　　　　}

　　　　事務還有隔離等級、嵌套事務等問題

十三、SQLServer的使用

　　1，安裝，版本：2008有相容性的問題，有bug、因此推薦安裝SQLServer 2008 R2

　　2，Management Studio的使用

　　　　SQLServer的兩種串連方式：
　　　　Windows 身分識別驗證（互相信任的區域網路中）；
　　　　SQLServer身分識別驗證（使用SQLServer使用者名稱密碼驗證，密碼要複雜一點）。
　　　　Windows 身分識別驗證還有一個用途：忘了sa密碼，可以本機進去改。

　　3，建立資料庫、建立表
　　　　SQLServer的資料類型
　　　　（varchar和nvarchar；nvarchar(n)和nvarchar(MAX)；
　　　　long是bigint；
　　　　擷取前10條資料： select top 10 * from t_persons ）、
　　　　SQLServer的自動成長（是標識）、不需要特殊指定編碼

　　4，儲存表設計修改的時候，如果報錯“不允許儲存更改”：
　　　　工具→選項→Designers→把“阻止儲存要求重新建立表的更高”勾選掉。
　　　　遇到報錯資訊：要先仔細閱讀。

　　5，執行SQL語句（資料庫上點右鍵“建立查詢”，不要點【調試】）

十四、ADO.Net串連SQLServer
　　1，ADO.Net如何串連SQLServer：
　　　　SQLServer驅動內建

　　2，連接字串：
　　　　server=ip;user id =sa;passWord = 密碼;database = db1

　　3，SQLHelper：
　　　　把MySql替換成Sql就可以了

　　4，獲得自動成長列的值：
　　　　Insert into t1(...) output insert.Id values(....)

　　5，如果基於介面編程，只要改動CreateConnection就可以了，查詢參數以Directory<string,object>傳遞
　　　　如果使用Provider，連代碼都不用改，改設定檔即可

 

 

 

 

 

如鵬網學習筆記（六）ADO.Net基礎