Phoenix.net 迷之殿

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

2008-08-08

ASP.NET 2.0 物件導向設計 多層式架構 - C# 對應

ASP.NET 2.0 物件導向設計 多層式架構 - VB.NET 的 C# 對照版

ASP.NET 2.0 物件導向設計 多層式架構 - VB.NET 的 C# 對照版

厄 為了區別 ... 我把它加入成另一個命名空間 DOM

Enum.vb -->Enum.cs

01 using System;
02 using System.Collections.Generic;
03 using System.Linq;
04 using System.Text;
05
06 namespace DOM
07 {
08     public enum dbType
09     {
10         SqlServer = 0,
11         OleDb = 1,
12     }
13 }

我把MSSQL 改成 SqlServer(請不要問我WHY ...)

objADU.vb-->Query.cs

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 DOM
09 {
10     public class Query
11     {
12         private DAL.DataCommand dc = new DAL.DataCommand();
13         public bool ADU(ref string SqlQuery, ref DbParameter Parameter, dbType Type)
14         {
15             bool result = false;
16             switch (Type)
17             {
18                 case dbType.SqlServer:
19                     SqlParameter SP = (SqlParameter)Parameter;
20                     result = SqlServer(ref SqlQuery, ref SP);
21                     Parameter = null;
22                     break;
23             }
24             return result;
25         }
26         public bool ADU(ref string SqlQuery, ref DbParameter[] Parameters, dbType Type)
27         {
28             bool result = false;
29             switch (Type)
30             {
31                 case dbType.SqlServer:
32                     SqlParameter[] SP = new SqlParameter[Parameters.Length];
33                     for (int i = 0; i < Parameters.Length; i++)
34                         SP[i] = (SqlParameter)Parameters[i];
35                     result = SqlServer(ref SqlQuery, ref SP);
36                     Parameters = null;
37                     break;
38
39             }
40             return result;
41         }
42
43         private bool SqlServer(ref string SqlQuery, ref SqlParameter Parameter)
44         {
45             try
46             {
47                 if (dc.MSSqlExecuteNonQuery(ref SqlQuery, ref Parameter) > 0)
48                     return true;
49                 else
50                     return false;
51             }
52             catch (Exception)
53             {
54                 return false;
55             }
56         }
57         private bool SqlServer(ref string SqlQuery,ref SqlParameter[] Parameters)
58         {
59             try
60             {
61                 if (dc.MSSqlExecuteNonQuery(ref SqlQuery, ref Parameters) > 0)
62                     return true;
63                 else
64                     return false;
65             }
66             catch(Exception)
67             {
68                 return false;
69             }
70         }
71     }
72 }

做了些許修正 傳回是否成功

objNews -->News.cs (厄 ... foreach 的參數 不可以用 ref傳遞 所以修正一下)

01 using System;
02 using System.Collections.Generic;
03 using System.Linq;
04 using System.Text;
05 using System.Data;
06 namespace DOM
07 {
08     public class News:Query
09     {
10         private int _ID;
11         private DateTime _Date;
12         private string _Subject;
13         private string _Content;
14
15         public int NewsID
16         {
17             get { return _ID; }
18             set { _ID = value; }
19         }
20         public DateTime NewsDate
21         {
22             get { return _Date; }
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 News() { }
35         public News(DataRow row)
36         {
37             GetNews(ref row);
38         }
39         private void GetNews(ref DataRow row)
40         {
41             _ID = int.Parse(row[0].ToString());
42             _Date = DateTime.Parse(row[1].ToString());
43             _Subject = row[2].ToString();
44             _Content = row[3].ToString();
45         }
46     }
47 }

 

objConn-->ConnectionString.cs

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

 objData --> DataCommand.cs

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

daoNews -->BLL.News

01 using System;
02 using System.Collections.Generic;
03 using System.Linq;
04 using System.Text;
05 using System.Data;
06 using System.Data.Common;
07 using System.Data.SqlClient;
08
09 namespace BLL
10 {
11     public class News
12     {
13         private DAL.DataCommand dc = new DAL.DataCommand();
14         public List<DOM.News> GetNews()
15         {
16             try
17             {
18                 List<DOM.News> rNews = new List<DOM.News>();
19                 rNews.Clear();
20                 string SqlQuery = "SELECT * From [News] ORDER BY [N_Date] DESC";
21                 DataTable dt;
22                 if (dc.MSSqlSelectQuery(ref SqlQuery, out dt))
23                 {
24                     foreach (DataRow row in dt.Rows)
25                         rNews.Add(new DOM.News(row));
26                 }
27                 return rNews;
28             }
29             catch(Exception)
30             {
31                 return new List<DOM.News>();
32             }
33         }
34         public List<DOM.News> GetNews(int NID)
35         {
36             try
37             {
38                 List<DOM.News> rNews = new List<DOM.News>();
39                 rNews.Clear();
40                 string SqlQuery = "SELECT * From [News] WHERE N_ID = @N_ID";
41                 DataTable dt;
42                 SqlParameter Parameter = new SqlParameter("@NID", NID);
43                 if (dc.MSSqlSelectQuery(ref SqlQuery,ref Parameter, out dt))
44                         rNews.Add(new DOM.News(dt.Rows[0]));
45                 return rNews;
46             }
47             catch (Exception)
48             {
49                 return new List<DOM.News>();
50             }
51         }
52         public bool NewsUpdate(DOM.News oNews)
53         {
54             try
55             {
56                 string SqlQuery = "UPDATE News SET N_Subject = @N_Subject, N_Content = @N_Content WHERE N_ID = @Original_N_ID";
57                 DbParameter[] Parameters = new DbParameter[3];
58                 Parameters[0] = new SqlParameter("@N_Subject", oNews.NewsSubject);
59                 Parameters[1] = new SqlParameter("@N_Content", oNews.NewsContent);
60                 Parameters[2] = new SqlParameter("@Original_N_ID", oNews.NewsID);
61                 return oNews.ADU(ref SqlQuery, ref Parameters, DOM.dbType.SqlServer);
62             }
63             catch (Exception)
64             {
65                 return false;
66             }
67         }
68     }
69 }

以上

Phoenix 8/8