Phoenix.net 迷之殿

燃燒吧 .NET ~~~ 燃燒吧 我的鬥魂~~~

2008-08-11

C# 3.0 ~ 物件導向 多層式架構 Plus+ (Object Initializers)

摘要:C# 3.0 ~ 物件導向 多層式架構 Plus+

好幾天沒打文章了= =、

感覺有點怠惰了ˋˊ ..

其實 不是 ...

再寫新的架構 ...

突然發現 原來 SQL 放在 資料存取層 有他的道理在 ...

決定 把之前的架構都打碎ˋˊ

基本上 物件模型 單純就是 物件的模型

不包含任何其他的東西 單純對應資料庫資訊

再來 DAL層 <-->BLL層

這樣做之後 BLL曾 可以作好多事情 ...

例如 LIST的排序 篩選 等等 蠻多的 ...

再來 負責資料庫存取 還有連線字串 都變成 靜態類別

方便存取 不然每次都要NEW ...

附帶一提 ... 為了一個非常特殊的理由 ...我要逼自己 用C# ...感覺比較專業一點ˋˊ+

就像 人家都講中文 你講英文 感覺就是比較猛 ...(沒啦 開玩笑的= =、 是為了要證照考試)

廢話不多說 進入我們的主題 ...

首先是 -靜態類別- 取得連線字串(歸類在 命名空間 DAL)

01 using System.Web.Configuration;
02
03
04 namespace DAL
05 {
06     public static class ConnectionString
07     {
08         private static string _ConnStr;
09
10         public static string ConnStr
11         {
12             get { return _ConnStr; }
13         }
14         public static string GetConnStr()
15         {
16             _ConnStr = WebConfigurationManager.ConnectionStrings[0].ConnectionString.ToString();
17             return _ConnStr;
18         }
19         public static string GetConnStr(int index)
20         {
21             _ConnStr = WebConfigurationManager.ConnectionStrings[index].ConnectionString.ToString();
22             return _ConnStr;
23         }
24         public static string GetConnStr(string name)
25         {
26             _ConnStr = WebConfigurationManager.ConnectionStrings[name].ConnectionString.ToString();
27             return _ConnStr;
28         }
29         static ConnectionString()
30         {
31             _ConnStr = WebConfigurationManager.ConnectionStrings[0].ConnectionString.ToString();
32         }
33     }
34 }

 

 

29行 靜態類別的建構函式 有興趣請看靜態建構函式 (C# 程式設計手冊)

其他不再多做解釋 ...

再來 -靜態類別- 負責對資料作存取的(歸類在命名空間DAL)

001 using System;
002 using System.Data;
003 using System.Data.SqlClient;
004
005 namespace DAL
006 {
007     public static class DataCommand
008     {
009         public static bool MSSqlSelectQuery(ref string SqlQuery, out DataTable dt)
010         {
011             dt = new DataTable();
012             SqlQuery = SqlQuery.Trim();
013             if (SqlQuery.Contains("@") || SqlQuery.Contains("'") || SqlQuery.Contains("--"))
014                 return false;
015             else if(! SqlQuery.StartsWith("SELECT",true,null))
016                 return false;
017             try
018             {
019                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
020                 {
021                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
022                     SqlConn.Open();
023                     dt.Load(SqlCmd.ExecuteReader());
024                     return true;
025                 }
026             }
027             catch (Exception)
028             {
029                return false;
030             }
031             finally
032             {
033                 SqlQuery = null;
034             }
035         }
036         public static bool MSSqlSelectQuery(ref string SqlQuery, out DataSet ds)
037         {
038             ds = new DataSet();
039             SqlQuery = SqlQuery.Trim();
040             if (SqlQuery.Contains("@") || SqlQuery.Contains("'") || SqlQuery.Contains("--"))
041                 return false;
042             else if (!SqlQuery.StartsWith("SELECT", true, null))
043                 return false;
044             try
045             {
046                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
047                 {
048                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
049                     SqlDataAdapter da = new SqlDataAdapter(SqlCmd);
050                     da.Fill(ds);
051                     return true;
052                 }
053             }
054             catch (Exception)
055             {
056                 return false;
057             }
058             finally
059             {
060                 SqlQuery = null;
061             }
062         }
063         public static bool MSSqlSelectQuery(ref string SqlQuery,ref SqlParameter Parameter,out DataTable dt)
064         {
065             dt = new DataTable();
066             SqlQuery = SqlQuery.Trim();
067             if (SqlQuery.Contains("'") || SqlQuery.Contains("--"))
068                 return false;
069             else if (!SqlQuery.StartsWith("SELECT", true, null))
070                 return false;
071             try
072             {
073                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
074                 {
075                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
076                     SqlCmd.Parameters.Add(Parameter);
077                     SqlConn.Open();
078                     dt.Load(SqlCmd.ExecuteReader());
079                     return true;
080                 }
081             }
082             catch (Exception)
083             {
084                 return false;
085             }
086             finally
087             {
088                 SqlQuery = null;
089                 Parameter = null;
090             }
091         }
092         public static bool MSSqlSelectQuery(ref string SqlQuery, ref SqlParameter Parameter, out DataSet ds)
093         {
094             ds = new DataSet();
095             SqlQuery = SqlQuery.Trim();
096             if (SqlQuery.Contains("'") || SqlQuery.Contains("--"))
097                 return false;
098             else if (!SqlQuery.StartsWith("SELECT", true, null))
099                 return false;
100             try
101             {
102                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
103                 {
104                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
105                     SqlCmd.Parameters.Add(Parameter);
106                     SqlDataAdapter da = new SqlDataAdapter(SqlCmd);
107                     da.Fill(ds);
108                     return true;
109                 }
110             }
111             catch (Exception)
112             {
113                 return false;
114             }
115             finally
116             {
117                 SqlQuery = null;
118                 Parameter = null;
119             }
120         }
121         public static bool MSSqlSelectQuery(ref string SqlQuery, ref SqlParameter[] Parameters, out DataTable dt)
122         {
123             dt = new DataTable();
124             SqlQuery = SqlQuery.Trim();
125             if (SqlQuery.Contains("'") || SqlQuery.Contains("--"))
126                 return false;
127             else if (!SqlQuery.StartsWith("SELECT", true, null))
128                 return false;
129             try
130             {
131                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
132                 {
133                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
134                     SqlCmd.Parameters.AddRange(Parameters);
135                     SqlConn.Open();
136                     dt.Load(SqlCmd.ExecuteReader());
137                     return true;
138                 }
139             }
140             catch (Exception)
141             {
142                 return false;
143             }
144             finally
145             {
146                 SqlQuery = null;
147                 Parameters = null;
148             }
149         }
150         public static bool MSSqlSelectQuery(ref string SqlQuery, ref SqlParameter[] Parameters, out DataSet ds)
151         {
152             ds = new DataSet();
153             SqlQuery = SqlQuery.Trim();
154             if (SqlQuery.Contains("'") || SqlQuery.Contains("--"))
155                 return false;
156             else if (!SqlQuery.StartsWith("SELECT", true, null))
157                 return false;
158             try
159             {
160                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
161                 {
162                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
163                     SqlCmd.Parameters.AddRange(Parameters);
164                     SqlDataAdapter da = new SqlDataAdapter(SqlCmd);
165                     da.Fill(ds);
166                     return true;
167                 }
168             }
169             catch (Exception)
170             {
171                 return false;
172             }
173             finally
174             {
175                 SqlQuery = null;
176                 Parameters = null;
177             }
178         }
179         public static int MSSqlExecuteNonQuery(ref string SqlQuery)
180         {
181             int result = 0;
182             SqlQuery = SqlQuery.Trim();
183             if (SqlQuery.Contains("@") || SqlQuery.Contains("'") || SqlQuery.Contains("--"))
184                 return result;
185             try
186             {
187                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
188                 {
189                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
190                     SqlConn.Open();
191                     result = SqlCmd.ExecuteNonQuery();
192                     return result;
193                 }
194             }
195             catch (SqlException)
196             {
197                 return result;
198             }
199             finally
200             {
201                 SqlQuery = null;
202             }
203
204         }
205         public static int MSSqlExecuteNonQuery(ref string SqlQuery, ref SqlParameter Parameter)
206         {
207             int result = 0;
208             SqlQuery = SqlQuery.Trim();
209             if (SqlQuery.Contains("'") || SqlQuery.Contains("--"))
210                 return result;
211             try
212             {
213                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
214                 {
215                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
216                     SqlCmd.Parameters.Add(Parameter);
217                     SqlConn.Open();
218                     result = SqlCmd.ExecuteNonQuery();
219                     return result;
220                 }
221             }
222             catch (SqlException)
223             {
224                 return result;
225             }
226             finally
227             {
228                 SqlQuery = null;
229             }
230
231         }
232         public static int MSSqlExecuteNonQuery(ref string SqlQuery, ref SqlParameter[] Parameters)
233         {
234             int result = 0;
235             SqlQuery = SqlQuery.Trim();
236             if (SqlQuery.Contains("'") || SqlQuery.Contains("--"))
237                 return result;
238             try
239             {
240                 using (SqlConnection SqlConn = new SqlConnection(ConnectionString.GetConnStr()))
241                 {
242                     SqlCommand SqlCmd = new SqlCommand(SqlQuery, SqlConn);
243                     SqlCmd.Parameters.AddRange(Parameters);
244                     SqlConn.Open();
245                     result = SqlCmd.ExecuteNonQuery();
246                     return result;
247                 }
248             }
249             catch (SqlException)
250             {
251                 return result;
252             }
253             finally
254             {
255                 SqlQuery = null;
256             }
257         }
258     }
259 }

 

附帶一提 ... 靜態類別 在MSND上 的說明

靜態類別和靜態類別成員 (C# 程式設計手冊)

 

 

 

 

 

  • 只包含靜態成員。

  • 無法執行個體化 (Instantiated)。

  • 為密封類別。

  • 無法包含執行個體建構函式 (C# 程式設計手冊)。

    因此，建立靜態類別就像是建立只包含靜態成員和私用建構函式 (Private Constructor) 的類別。私用建構函式可讓類別避免執行個體化。

    使用靜態類別的好處是，編譯器可進行檢查，確定不會在不小心的情況下加入執行個體成員。編譯器將可確保這個類別不會建立執行個體。

    靜態類別是密封的，因此無法繼承。靜態類別無法包含建構函式，不過您仍可以宣告靜態建構函式，以指定初始值或設定靜態狀態。如需詳細資訊，請參閱靜態建構函式 (C# 程式設計手冊)。

    由於 無法使用繼承 以及不可以宣告執行個體

    所以 原本 DataCommand 繼承自 ConnectionString

    被我拿掉了 ... 建立執行個體的敘述 也拿掉了

    再來 物件模型的部份 (以NEWS 新聞公告版 做為範例) 資料庫架構如下

    (歸類在命名空間 DOM 資料物件模型)

    01 using System;
    02 namespace DOM
    03 {
    04     public class News
    05     {
    06         private int _ID;
    07         private DateTime _Date;
    08         private string _Subject;
    09         private string _Content;
    10         private bool _Del;
    11         private string _Chger;
    12         private DateTime _ChgTime;
    13
    14         public int NewsID
    15         {
    16             get { return _ID; }
    17             set { _ID = value; }
    18         }
    19         public DateTime NewsDate
    20         {
    21             get { return _Date; }
    22             set { _Date = value; }
    23         }
    24         public string NewsSubject
    25         {
    26             get { return _Subject; }
    27             set { _Subject = value; }
    28         }
    29         public string NewsContent
    30         {
    31             get { return _Content; }
    32             set { _Content = value; }
    33         }
    34         public bool Del
    35         {
    36             get { return _Del; }
    37             set { _Del = value; }
    38         }
    39         public string Chger
    40         {
    41             get { return _Chger; }
    42             set { _Chger = value; }
    43         }
    44         public DateTime ChgTime
    45         {
    46             get { return _ChgTime; }
    47             set { _ChgTime = value; }
    48         }
    49
    50         public News() { }
    51     }
    52 }

    單純就是物件的模型 ...對應到資料庫

    再來是 增刪改的Query 類別

    01 using System;
    02 using System.Collections.Generic;
    03 using System.Linq;
    04 using System.Text;
    05 using System.Data.Common;
    06 using System.Data.SqlClient;
    07
    08 namespace DAL
    09 {
    10     public class Query
    11     {
    12         protected int IDU(ref string SqlQuery, DOM.dbType Type)
    13         {
    14             int result = 0;
    15             switch (Type)
    16             {
    17                 case DOM.dbType.SqlServer:
    18                     result = SqlServer(ref SqlQuery);
    19                     break;
    20             }
    21             return result;
    22         }
    23         protected int IDU(ref string SqlQuery, ref DbParameter Parameter, DOM.dbType Type)
    24         {
    25             int result = 0;
    26             switch (Type)
    27             {
    28                 case DOM.dbType.SqlServer:
    29                     SqlParameter SP = (SqlParameter)Parameter;
    30                     result = SqlServer(ref SqlQuery, ref SP);
    31                     Parameter = null;
    32                     break;
    33             }
    34             return result;
    35         }
    36         protected int IDU(ref string SqlQuery, ref DbParameter[] Parameters, DOM.dbType Type)
    37         {
    38             int result = 0;
    39             switch (Type)
    40             {
    41                 case DOM.dbType.SqlServer:
    42                     SqlParameter[] SP = new SqlParameter[Parameters.Length];
    43                     for (int i = 0; i < Parameters.Length; i++)
    44                         SP[i] = (SqlParameter)Parameters[i];
    45                     result = SqlServer(ref SqlQuery, ref SP);
    46                     Parameters = null;
    47                     break;
    48
    49             }
    50             return result;
    51         }
    52         private int SqlServer(ref string SqlQuery)
    53         {
    54             return DataCommand.MSSqlExecuteNonQuery(ref SqlQuery);
    55         }
    56         private int SqlServer(ref string SqlQuery, ref SqlParameter Parameter)
    57         {
    58             return DataCommand.MSSqlExecuteNonQuery(ref SqlQuery, ref Parameter);
    59         }
    60         private int SqlServer(ref string SqlQuery,ref SqlParameter[] Parameters)
    61         {
    62             return DataCommand.MSSqlExecuteNonQuery(ref SqlQuery, ref Parameters);
    63         }
    64     }
    65 }

     

    再來 DAL 資料存取層 繼承 增刪改查 ...

    001 using System;
    002 using System.Collections.Generic;
    003 using System.Data;
    004 using System.Data.Common;
    005 using System.Data.SqlClient;
    006 using System.Linq;
    007 using System.Text;
    008
    009 namespace DAL
    010 {
    011     public class News:Query
    012     {
    013         public List<DOM.News> GetAll()
    014         {
    015             List<DOM.News> rNews = new List<DOM.News>();
    016             rNews.Clear();
    017             string SqlQuery = "SELECT NewsID, NewsDate, NewsSubject, NewsContent, Del, Chger, ChgTime From News";
    018             DataTable dt;
    019             if (DataCommand.MSSqlSelectQuery(ref SqlQuery, out dt))
    020             {
    021                 foreach (DataRow row in dt.Rows)
    022                     rNews.Add(new DOM.News()
    023                     {
    024                         NewsID = (int)row[0],
    025                         NewsDate = (DateTime)row[1],
    026                         NewsSubject = row[2].ToString(),
    027                         NewsContent = row[3].ToString(),
    028                         Del = (bool)row[4],
    029                         Chger = row[5].ToString(),
    030                         ChgTime = (DateTime)row[6]
    031                     }    );
    032
    033             }
    034             return rNews;
    035         }
    036         public List<DOM.News> GetBy(ref int NID)
    037         {
    038             List<DOM.News> rNews = new List<DOM.News>();
    039             rNews.Clear();
    040             string SqlQuery = "SELECT NewsID, NewsDate, NewsSubject, NewsContent, Del, Chger, ChgTime From News WHERE (NewsID = @NewsID)";
    041             DataTable dt;
    042             SqlParameter Parameter = new SqlParameter("@NewsID", NID);
    043             if (DataCommand.MSSqlSelectQuery(ref SqlQuery, ref Parameter, out dt))
    044             {
    045                 rNews.Add(new DOM.News()
    046                 {
    047                     NewsID = (int)dt.Rows[0][0],
    048                     NewsDate = (DateTime)dt.Rows[0][1],
    049                     NewsSubject = dt.Rows[0][2].ToString(),
    050                     NewsContent = dt.Rows[0][3].ToString(),
    051                     Del = (bool)dt.Rows[0][4],
    052                     Chger = dt.Rows[0][5].ToString(),
    053                     ChgTime = (DateTime)dt.Rows[0][6]
    054                 }    );
    055             }
    056             return rNews;
    057         }
    058         public int Insert(ref DOM.News oNews)
    059         {
    060             string SqlQuery = "INSERT INTO [News] ([NewsSubject], [NewsContent], [Chger]) VALUES (@NewsSubject, @NewsContent, @Chger)";
    061             DbParameter[] Parameters = new SqlParameter[]
    062             {
    063                 new SqlParameter("@NewsSubject", oNews.NewsSubject),
    064                 new SqlParameter("@NewsContent", oNews.NewsContent),
    065                 new SqlParameter("@Chger", oNews.Chger)
    066
    067             }    ;
    068             return base.IDU(ref SqlQuery, ref Parameters, DOM.dbType.SqlServer);
    069         }
    070         public int SetDelete(ref int NID)
    071         {
    072             string SqlQuery = "UPDATE News SET Del = 1 WHERE (NewsID = @Original_NewsID)";
    073             DbParameter Parameter = new SqlParameter("@Original_NewsID", NID);
    074             return base.IDU(ref SqlQuery, ref Parameter, DOM.dbType.SqlServer);
    075         }
    076         public int RevDelete(ref int NID)
    077         {
    078             string SqlQuery = "UPDATE News SET Del = 0 WHERE (NewsID = @Original_NewsID)";
    079             DbParameter Parameter = new SqlParameter("@Original_NewsID", NID);
    080             return base.IDU(ref SqlQuery, ref Parameter, DOM.dbType.SqlServer);
    081         }
    082         public int Delete()
    083         {
    084             string SqlQuery = "DELETE FROM News WHERE (Del = 1)";
    085             return base.IDU(ref SqlQuery, DOM.dbType.SqlServer);
    086         }
    087         public int Update(ref DOM.News oNews)
    088         {
    089             string SqlQuery = "UPDATE News SET NewsSubject = @NewsSubject, NewsContent = @NewsContent, Del = @Del, Chger = @Chger, ChgTime = @ChgTime WHERE (NewsID = @Original_NewsID)";
    090             DbParameter[] Parameters = new SqlParameter[]  
    091             {
    092                 new SqlParameter("@NewsSubject", oNews.NewsSubject),
    093                 new SqlParameter("@NewsContent", oNews.NewsContent),
    094                 new SqlParameter("@Del", oNews.Del),
    095                 new SqlParameter("@Chger", oNews.Chger),
    096                 new SqlParameter("@ChgTime", oNews.ChgTime),
    097                 new SqlParameter("@Original_NewsID", oNews.NewsID)
    098             }    ;
    099             return base.IDU(ref SqlQuery, ref Parameters, DOM.dbType.SqlServer);
    100         }
    101         public News() { }
    102     }
    103 }

     

    看到了嗎 ...22行的地方 C# 3.0的 Object Initializers 超級好用~

    70 76行  下面再做解釋

    再來 BLL層 

    01 using System;
    02 using System.Collections.Generic;
    03
    04 namespace BLL
    05 {
    06     public class News
    07     {
    08         private DAL.News dNews;
    09         public List<DOM.News> GetAll()
    10         {
    11             return dNews.GetAll();
    12         }
    13         public List<DOM.News> GetBy(int NID)
    14         {
    15             return dNews.GetBy(ref NID);
    16         }
    17         public int Insert(DOM.News oNews)
    18         {
    19             if (oNews.Chger == null)
    20                 return 0;
    21             else
    22             return dNews.Insert(ref oNews);
    23         }
    24         public int Delete(DOM.News oNews)
    25         {
    26             int ID = oNews.NewsID;
    27             if (oNews.Del)
    28                 return dNews.SetDelete(ref ID);
    29             else
    30                 return dNews.RevDelete(ref ID);
    31         }
    32         public int Delete()
    33         {
    34             return dNews.Delete();
    35         }
    36         public int Update(ref DOM.News oNews)
    37         {
    38             if (oNews.Chger == null)
    39                 return 0;
    40             else
    41             {
    42                 oNews.ChgTime = DateTime.Now;
    43                 return dNews.Update(ref oNews);
    44             }
    45         }
    46         public News()
    47         {
    48             dNews = new DAL.News();
    49         }
    50     }
    51 }

    基本上 是和 DAL進行溝通 ...還有處理一些資訊

    例如 19行的地方 如果 傳進來的 Chger ( 修改者 )為空 ...

    就不做任何事情 直接傳回 0

    再來 有關於刪除的部份 做了一些小變動 ...

    再資料庫裡面 加入一個 叫做 Del的值 型態為 bit

    如果 Del = 1 就代表 待刪除的資料

    我在這邊的用意是 在UI的部份 讓使用者 選擇刪除 但是不會真的刪除

    只會把 Del 設定成1 (避免誤刪)

    只有在呼叫到 Delete(); 才會執行 真正的刪除動作 (Del = 1的全部刪掉)

    好像也可以做到 整批刪除的效果 ...^^

    再來是36行的部份 也是檢查 Chger 是不是空的 (空的當然不允許)

    如果不是，就把ChgTime(修改時間) 加入要寫進資料庫的物件 再傳回給DAL層 做SQL命令

    其實也在想 要不要把DAL 寫成靜態類別呢 ...(有機會再試試看)

    以上 ...

    Phoenix 8/11