ADO.NET隨筆

2018-02-09

平常在Service會使用到的ado.net ，有可以用DataReader或用DataSet的方式，這邊紀錄使用方法

範例都用北風資料庫操作，沒有的請去這裡下載
https://onedrive.live.com/?authkey=%21AEZb2lql49fYT78&cid=7C5615A2345B2795&id=7C5615A2345B2795%21722348&parId=7C5615A2345B2795%21335787&o=OneUp

ADo.NET連結資料庫的步驟

1.連接資料庫(ConnetString)

2.寫SQL指令存取資料(讀取或寫入) CRUD

3.WebForm可以DataBind() 或把資料取出來轉成List做處理

4.關閉資源

簡單說就是Connect>CRUD>Bind>Close

連接字串可以用sqldatasource控制項連接，連好之後會在web.config出現一段

<connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=(localdb)\Sampledb;Initial Catalog=Northwind;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Initial Catalog:資料庫名稱

Data Source:連接資料庫的伺服器名稱 (.是本機)

如果有帳號密碼的話要加上User ID=XXX;Password=XXX

在頁面上連接的話可以如下寫法

using System.Web.Configuration;

using System.Data.SqlClient;

 SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

ADO.NET有兩個重點分別是DataAdaper跟DataReader，DataReader是從資料來源讀取順向且唯獨的資料流，DataAdaper是把資料填到DataSet，或是更新後的資料回填到資料來源

以下做個表格比較

	DataAdaper	DataReader
連結資料庫	不用，會自動開啟關閉	SqlConnection.Open()
執行SQL指令	SqlDataAdapter .Fill() .Open()	SqlCommand ExecuteReader() ExecuteNonQuery()
資料指標移動	讀到記憶體裡	順向且唯獨
如何處理資料	可以處理複雜資料庫關聯跟多個DataTable	適合單一資料表
消耗資源	較大	較小
分頁	有	無

以上表格來自ASP.NET專題實務

1.DataReader

只提供唯獨且順向的資料，就是只會一行一行一直往下讀，沒有辦法往上回溯

只要執行Read()方法，每讀完一筆資料就會把指標指向下一筆，

範例

 SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
            SqlDataReader dr = null;

            SqlCommand cmd=new SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandTimeout = 300;
            cmd.Connection = conn;
            cmd.CommandText = "SELECT * FROM [Suppliers] ORDER BY [CompanyName]";

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader();
                //資料繫結或做其他處理
                GridView1.DataSource = dr;
                GridView1.DataBind();
            }
            catch(Exception ex)
            {
                throw ex;
             }
            finally//關閉資源
            {
                if (dr != null)
                {
                    cmd.Cancel();
                    dr.Close();
                }
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();

                }

            }

記得要先呼叫SqlCommand的.Cancel()方法在呼叫DataReader.Close()方法，否則會浪費資源喔!

也可以用using自動關閉資源，這樣就不用呼叫Close()方法了

2.DataAdaper

範例

SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
            SqlDataAdapter da = null;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandTimeout = 300;
            cmd.Connection = conn;
            cmd.CommandText = "SELECT * FROM [Suppliers] ORDER BY [CompanyName]";
            da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);//把資料庫資料填入到DataSet
            //資料繫結
            GridView1.DataSource = ds;
            GridView1.DataBind();

填入DataSet之後不一定要直接做資料繫結，如果是MVC，可以取出來做一些處理，例如篩選資料或判斷條件，可以用Linq to DataSet，個人用過覺得算很好用，也可以把DataSet透過反射轉成List

3.ADO.NET 使用預存程序

其實這算是公司內用最多的一種方式了，因為不一定每個RD SQL都很強，所以有些公司會請DBA(資料庫管理師)負責開資料庫Table，複雜的邏輯也會由DBA那邊寫好Store Procedure，我們RD就直接call就好了，也不失為一個方便的方式，只是debug的時候會需要判斷是RD程式錯還是SP錯

先來個範例吧

 SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand("自己寫的預存程序", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 300;
            cmd.Parameters.Add("@StartDate", SqlDbType.VarChar, 8).Value = StartDate;
            cmd.Parameters.Add("@EndDate", SqlDbType.VarChar, 8).Value = EndDate;
            cmd.Parameters.Add("@Type", SqlDbType.Int).Value = Type; 
            cmd.Parameters.Add("@Account", SqlDbType.VarChar, 6).Value = Account;
            SqlParameter Price = new SqlParameter("@myValue", SqlDbType.Decimal);
            Price.Precision = 18;
            Price.Scale = 2;
            cmd.Parameters.Add(Price);

            SqlParameter ReturnMsg = cmd.Parameters.Add("@ReturnMsg", SqlDbType.NVarChar, 15);
            ReturnMsg.Direction = ParameterDirection.Output;
            
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {

                conn.Open();
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
               
            }
            finally
            {
                conn.Close();
            }

比較特別的應該是參數的型別，decimal要去另外設定他的總位元數跟小數後幾位，如果是DataTime型別的，則依定要轉型成SQL可以讀的DateTime格式

今天就先分享到這邊吧

回首頁