ASP.NET 数据访问类

using System;
using System.Data;
using System.Data.SqlClient;

namespace SysClassLibrary
{
///

1<summary>   
2/// DataAccess 的摘要说明。   
3/// <description>数据处理基类,调用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>   
4/// </summary>

public class DataAccess
{
#region 属性
protected static SqlConnection conn=new SqlConnection();
protected static SqlCommand comm=new SqlCommand();
#endregion
public DataAccess()
{
//init();
}
#region 内部函数 静态方法中不会执行DataAccess()构造函数

///

1<summary>   
2/// 打开数据库连接   
3/// </summary>

private static void openConnection()
{
if (conn.State == ConnectionState.Closed)
{
//SysConfig.ConnectionString 为系统配置类中连接字符串，如："server=localhost;database=databasename;uid=sa;pwd=;"

conn.ConnectionString = SysConfig.ConnectionString ;
comm.Connection =conn;
try
{
conn.Open();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
}
}
///

1<summary>   
2/// 关闭当前数据库连接   
3/// </summary>

private static void closeConnection()
{
if(conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
comm.Dispose();
}
#endregion
///

1<summary>   
2/// 执行Sql查询语句   
3/// </summary>

///

1<param name="sqlstr"/>

传入的Sql语句
public static void ExecuteSql(string sqlstr)
{
try
{
openConnection();
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
comm.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}

///

1<summary>   
2/// 执行存储过程   
3/// </summary>

///

1<param name="procName"/>

存储过程名
///

1<param name="coll"/>

SqlParameters 集合
public static void ExecutePorcedure(string procName,SqlParameter[] coll)
{
try
{
openConnection();
for(int i=0;i

  1<coll.length;i++) .add(coll[i]);="" ;="" <summary="" catch(exception="" closeconnection();="" comm.commandtext="procName;" comm.commandtype="CommandType.StoredProcedure" comm.executenonquery();="" comm.parameters="" comm.parameters.clear();="" e)="" exception(e.message);="" finally="" new="" throw="" {="" }="">   
  2/// 执行存储过程并返回数据集   
  3///    
  4/// <param name="procName"/>存储过程名称   
  5/// <param name="coll"/>SqlParameter集合   
  6/// <param name="ds"/>DataSet    
  7public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds)   
  8{   
  9try   
 10{   
 11SqlDataAdapter da=new SqlDataAdapter();   
 12openConnection();   
 13for(int i=0;i<coll.length;i++) .add(coll[i]);="" ;="" <summary="" catch(exception="" closeconnection();="" comm.commandtext="procName;" comm.commandtype="CommandType.StoredProcedure" comm.parameters="" comm.parameters.clear();="" da.fill(ds);="" da.selectcommand="comm;" e)="" exception(e.message);="" finally="" new="" throw="" {="" }="">   
 14/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -&gt; Unbox   
 15///    
 16/// <param name="sqlstr"/>传入的Sql语句   
 17/// <returns>object 返回值 </returns>   
 18public static object ExecuteScalar(string sqlstr)   
 19{   
 20object obj=new object();   
 21try   
 22{   
 23openConnection();   
 24comm.CommandType =CommandType.Text ;   
 25comm.CommandText =sqlstr;   
 26obj=comm.ExecuteScalar();   
 27}   
 28catch(Exception e)   
 29{   
 30throw new Exception(e.Message);   
 31}   
 32finally   
 33{   
 34closeConnection();   
 35}   
 36return obj;   
 37} 
 38
 39/// <summary>   
 40/// 执行Sql查询语句,同时进行事务处理   
 41/// </summary>   
 42/// <param name="sqlstr"/>传入的Sql语句   
 43public static void ExecuteSqlWithTransaction(string sqlstr)   
 44{   
 45SqlTransaction trans ;   
 46trans=conn.BeginTransaction();   
 47comm.Transaction =trans;   
 48try   
 49{   
 50openConnection();   
 51comm.CommandType =CommandType.Text ;   
 52comm.CommandText =sqlstr;   
 53comm.ExecuteNonQuery();   
 54trans.Commit();   
 55}   
 56catch   
 57{   
 58trans.Rollback();   
 59}   
 60finally   
 61{   
 62closeConnection();   
 63}   
 64} 
 65
 66/// <summary>   
 67/// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接   
 68/// 方法关闭数据库连接   
 69/// </summary>   
 70/// <param name="sqlstr"/>传入的Sql语句   
 71/// <returns>SqlDataReader对象</returns>   
 72public static SqlDataReader dataReader(string sqlstr)   
 73{   
 74SqlDataReader dr=null;   
 75try   
 76{   
 77openConnection();   
 78comm.CommandText =sqlstr;   
 79comm.CommandType =CommandType.Text ;   
 80dr=comm.ExecuteReader(CommandBehavior.CloseConnection);   
 81}   
 82catch   
 83{   
 84try   
 85{   
 86dr.Close();   
 87closeConnection();   
 88}   
 89catch   
 90{   
 91}   
 92}   
 93return dr;   
 94}   
 95/// <summary>   
 96/// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接   
 97/// 方法关闭数据库连接   
 98/// </summary>   
 99/// <param name="sqlstr"/>传入的Sql语句   
100/// <param name="dr"/>传入的ref DataReader 对象   
101public static void dataReader(string sqlstr,ref SqlDataReader dr)   
102{   
103try   
104{   
105openConnection();   
106comm.CommandText =sqlstr;   
107comm.CommandType =CommandType.Text ;   
108dr=comm.ExecuteReader(CommandBehavior.CloseConnection);   
109}   
110catch   
111{   
112try   
113{   
114if(dr!=null &amp;&amp; !dr.IsClosed)   
115dr.Close();   
116}   
117catch   
118{   
119}   
120finally   
121{   
122closeConnection();   
123}   
124}   
125} 
126
127  
128/// <summary>   
129/// 返回指定Sql语句的DataSet   
130/// </summary>   
131/// <param name="sqlstr"/>传入的Sql语句   
132/// <returns>DataSet</returns>   
133public static DataSet dataSet(string sqlstr)   
134{   
135DataSet ds= new DataSet();   
136SqlDataAdapter da=new SqlDataAdapter();   
137try   
138{   
139openConnection();   
140comm.CommandType =CommandType.Text ;   
141comm.CommandText =sqlstr;   
142da.SelectCommand =comm;   
143da.Fill(ds);   
144}   
145catch(Exception e)   
146{   
147throw new Exception(e.Message);   
148}   
149finally   
150{   
151closeConnection();   
152}   
153return ds;   
154} 
155
156/// <summary>   
157/// 返回指定Sql语句的DataSet   
158/// </summary>   
159/// <param name="sqlstr"/>传入的Sql语句   
160/// <param name="ds"/>传入的引用DataSet对象   
161public static void dataSet(string sqlstr,ref DataSet ds)   
162{   
163SqlDataAdapter da=new SqlDataAdapter();   
164try   
165{   
166openConnection();   
167comm.CommandType =CommandType.Text ;   
168comm.CommandText =sqlstr;   
169da.SelectCommand =comm;   
170da.Fill(ds);   
171}   
172catch(Exception e)   
173{   
174throw new Exception(e.Message);   
175}   
176finally   
177{   
178closeConnection();   
179}   
180}   
181/// <summary>   
182/// 返回指定Sql语句的DataTable   
183/// </summary>   
184/// <param name="sqlstr"/>传入的Sql语句   
185/// <returns>DataTable</returns>   
186public static DataTable dataTable(string sqlstr)   
187{   
188SqlDataAdapter da=new SqlDataAdapter();   
189DataTable datatable=new DataTable();   
190try   
191{   
192openConnection();   
193comm.CommandType =CommandType.Text ;   
194comm.CommandText =sqlstr;   
195da.SelectCommand =comm;   
196da.Fill(datatable);   
197}   
198catch(Exception e)   
199{   
200throw new Exception(e.Message);   
201}   
202finally   
203{   
204closeConnection();   
205}   
206return datatable;   
207} 
208
209/// <summary>   
210/// 执行指定Sql语句,同时给传入DataTable进行赋值   
211/// </summary>   
212/// <param name="sqlstr"/>传入的Sql语句   
213/// <param name="dt"/>ref DataTable dt    
214public static void dataTable(string sqlstr,ref DataTable dt)   
215{   
216SqlDataAdapter da=new SqlDataAdapter();   
217try   
218{   
219openConnection();   
220comm.CommandType =CommandType.Text ;   
221comm.CommandText =sqlstr;   
222da.SelectCommand =comm;   
223da.Fill(dt);   
224}   
225catch(Exception e)   
226{   
227throw new Exception(e.Message);   
228}   
229finally   
230{   
231closeConnection();   
232}   
233}   
234/// <summary>   
235/// 执行带参数存储过程并返回数据集合   
236/// </summary>   
237/// <param name="procName"/>存储过程名称   
238/// <param name="parameters"/>SqlParameterCollection 输入参数   
239/// <returns></returns>   
240public static DataTable dataTable(string procName,SqlParameterCollection parameters)   
241{   
242SqlDataAdapter da=new SqlDataAdapter();   
243DataTable datatable=new DataTable();   
244try   
245{   
246openConnection();   
247comm.Parameters.Clear();   
248comm.CommandType=CommandType.StoredProcedure ;   
249comm.CommandText =procName;   
250foreach(SqlParameter para in parameters)   
251{   
252SqlParameter p=(SqlParameter)para;   
253comm.Parameters.Add(p);   
254}   
255da.SelectCommand =comm;   
256da.Fill(datatable);   
257}   
258catch(Exception e)   
259{   
260throw new Exception(e.Message);   
261}   
262finally   
263{   
264closeConnection();   
265}   
266return datatable;   
267} 
268
269public static DataView dataView(string sqlstr)   
270{   
271SqlDataAdapter da=new SqlDataAdapter();   
272DataView dv=new DataView();   
273DataSet ds=new DataSet();   
274try   
275{   
276openConnection();   
277comm.CommandType=CommandType.Text;   
278comm.CommandText =sqlstr;   
279da.SelectCommand =comm;   
280da.Fill(ds);   
281dv=ds.Tables[0].DefaultView;   
282}   
283catch(Exception e)   
284{   
285throw new Exception(e.Message);   
286}   
287finally   
288{   
289closeConnection();   
290}   
291return dv;   
292}   
293} 
294
295  
296}</coll.length;i++)></coll.length;i++)>
Published At
Categories with Web编程
Tagged with
comments powered by Disqus