Asp.net三层架构之数据访问层(MS SQL 2000版)

1. DataAccess.cs
3. using System;
4. using System.Data;
5. using System.Configuration;
6. using System.Web;
7. using System.Web.Security;
8. using System.Web.UI;
9. using System.Web.UI.WebControls;
10. using System.Web.UI.WebControls.WebParts;
11. using System.Web.UI.HtmlControls;
12. using System.Data.SqlClient;
13. using System.Text;
15. /// <summary>
16. /// DataAccess 的摘要说明
17. /// </summary>
18. public class DataAccess
19. {
20.     private SqlConnection conn = null;
21.     private SqlCommand comm = null;
22.     private SqlDataAdapter da = null;
24. public DataAccess()
25. {
26.         conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
27. }
29.     // 执行非查询语句,并返回受影响的记录行数
30.     public int ExecuteCommand(string sql)
31.     {
32.         int result = 0;
33.         comm = new SqlCommand(sql, conn);
34.         try
35.         {
36.             conn.Open();
37.             result = comm.ExecuteNonQuery();
38.         }
39.         catch
40.         {
41.             return 0;
42.         }
43.         finally
44.         {
45.             this.conn.Close();
46.         }
47.         return result;
48.     }
50.     // 执行非查询语句,并返回受影响的记录行数
51.     public int ExecuteCommand(string sql, CommandType cmdtype, SqlParameter[] parameters)
52.     {
53.         int result = 0;
54.         comm = new SqlCommand();
55.         try
56.         {
57.             conn.Open();
58.             comm.Connection = conn;
59.             comm.CommandText = sql;
60.             comm.CommandType = cmdtype;
62.             if (parameters != null && parameters.Length > 0)
63.             {
64.                 foreach (SqlParameter param in parameters)
65.                 {
66.                     comm.Parameters.Add(param);
67.                 }
68.             }
69.             
70.             result = comm.ExecuteNonQuery();
71.         }
72.         catch
73.         {
74.             return 0;
75.         }
76.         finally
77.         {
78.             this.conn.Close();
79.         }
80.         return result;
81.     }
82.         
83.     public int ExecuteCommand(string sql, CommandType cmdtype, SqlParameter[] parameters, ref String[] output)
84.     {
85.         int _result = 0;
86.         
87.         comm = new SqlCommand();
88.         try
89.         {
90.             conn.Open();
91.             comm.Connection = conn;
92.             comm.CommandText = sql;
93.             comm.CommandType = cmdtype;
95.             if (parameters != null && parameters.Length > 0)
96.             {
97.                 foreach (SqlParameter param in parameters)
98.                 {
99.                     comm.Parameters.Add(param);
100.                 }
101.             }
103.             _result = comm.ExecuteNonQuery();
105.             for (int i = 0; i < output.Length; i++)
106.             {
107.                 output[i] = parameters[0].Value.ToString().Trim();
108.             }
109.         }
110.         catch (Exception ex)
111.         {
112.             throw new Exception(ex.Message);
113.         }
114.         finally
115.         {
116.             conn.Close();
117.         }
119.         return _result;
120.     }
122.     //生成参数
123.     public SqlParameter CreateParameter(string field, DbType dbtype, string value)
124.     {
125.         SqlParameter p = new SqlParameter();
126.         p.ParameterName = field;
127.         p.DbType = dbtype;
128.         p.Value = value;
129.         return p;
130.     }
132.     
133.     public SqlParameter CreateParameter(string field, DbType dbtype, string value, ParameterDirection direction)
134.     {
135.         SqlParameter p = new SqlParameter();
136.         p.ParameterName = field;
137.         p.DbType = dbtype;
138.         p.Value = value;
139.         p.Direction = direction;
140.         return p;
141.     }
142.     
143.     // 执行查询，并以DataReader返回结果集
144.     public SqlDataReader ExecuteReader(string sql)
145.     {
146.         SqlDataReader result = null;
147.         comm = new SqlCommand(sql, conn);
148.         try
149.         {
150.             conn.Open();
151.             result = comm.ExecuteReader();
152.         }
153.         catch
154.         {
155.             return null;
156.         }
157.                         
158.         return result;
159.     }
161.     // 执行查询，并以DataSet返回结果集
162.     public DataSet GetDataSet(string sql)
163.     {
164.         DataSet ds = new DataSet();
166.         try
167.         {
168.             conn.Open();
169.             da = new SqlDataAdapter(sql, conn);
170.             da.Fill(ds);
171.         }
172.         catch
173.         {
174.             return null;
175.         }
176.         finally
177.         {
178.             this.conn.Close();
179.         }
180.         return ds;
181.     }
183.     // 执行查询，并以DataView返回结果集
184.     public DataView GetDataView(string sql)
185.     {
186.         DataView dv = this.GetDataSet(sql).Tables[0].DefaultView;
187.         return dv;
188.     }
190.     // 执行查询，并以DataTable返回结果集
191.     public DataTable GetDataTable(string sql)
192.     {
193.         DataTable dt = GetDataSet(sql).Tables[0];
194.         return dt;
195.     }
197.     //返回首行首列的值
198.     public object ExecuteScalar(string sql)
199.     {
200.         object result = null;
201.         SqlCommand command = new SqlCommand(sql, conn);
202.         try
203.         {
204.             conn.Open();
205.             result = command.ExecuteScalar();
206.         }
207.         catch
208.         {
209.             return null;
210.         }
211.         finally
212.         {
213.             this.conn.Close();
214.         }
215.         return result;
216.     }
217. }

常用的工具类