1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 using System.Collections;
  7 using System.Configuration;
  8 
  9 namespace Yzx.SqlHelper
 10 {
 11     public class DBUltility : IDisposable
 12     {
 13 
 14         // private SqlConnection globalConnection;
 15         private string connectionString;
 16         private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
 17         public DBUltility()
 18         {
 19             connectionString =ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
 20         }
 21         public DBUltility(string newConnectionString)
 22         {
 23             connectionString = newConnectionString;
 24             //globalConnection = new SqlConnection (newConnectionString);
 25 
 26         }
 27         protected SqlCommand GetSqlCommand(SqlConnection conn, string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
 28         {
 29             SqlCommand comd = new SqlCommand(cmdText, conn);
 30             comd.CommandType = cmdType;
 31             if (cmdParameters != null)
 32             {
 33                 foreach (SqlParameter param in cmdParameters)
 34                 {
 35                     comd.Parameters.Add(param);
 36                 }
 37             }
 38             return comd;
 39         }
 40         protected SqlCommand GetSqlCommandByreturn(SqlConnection conn, string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
 41         {
 42             SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
 43             comd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
 44             return comd;
 45         }
 46         //protected void ConnectionOpen()
 47         //{
 48         //    if(conn.State != ConnectionState.Open)
 49         //    {
 50         //        conn.Open();
 51         //    }
 52         //}
 53         //protected void ConnectionClose()
 54         //{
 55         //    if(conn.State != ConnectionState.Closed)
 56         //    {
 57         //        conn.Close();
 58         //    }
 59         //}
 60         /// <summary>
 61         /// 执行一条语句，返回一个DataSet结果集
 62         /// </summary>
 63         /// <param name="cmdText">查询语句或者是存储过程名</param>
 64         /// <param name="cmdType">SqlCommand执行的方式，是文本还是存储过程</param>
 65         /// <param name="cmdParameters">附代的参数列表</param>
 66         /// <returns>返回一个DataSet结果集</returns>
 67         public DataSet ExecuteDataset(string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
 68         {
 69             using (SqlConnection conn = new SqlConnection(connectionString))
 70             {
 71                 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
 72                 DataSet ds = new DataSet();
 73                 conn.Open();
 74                 SqlDataAdapter da = new SqlDataAdapter(comd);
 75                 da.Fill(ds);
 76                 conn.Close();
 77                 comd.Parameters.Clear();
 78                 return ds;
 79             }
 80 
 81         }
 82         /// <summary>
 83         /// 执行一条语句，返回一个DataSet结果集
 84         /// </summary>
 85         /// <param name="cmdText">查询语句或者是存储过程名</param>
 86         /// <param name="cmdType">SqlCommand执行的方式，是文本还是存储过程</param>
 87         /// <param name="cmdParameters">附代的参数列表</param>
 88         /// <returns>返回一个DataTable结果集</returns>
 89         public DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParameters)
 90         {
 91             using (SqlConnection conn = new SqlConnection(connectionString))
 92             {
 93                 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
 94                 DataTable dt = new DataTable();
 95                 conn.Open();
 96                 SqlDataAdapter da = new SqlDataAdapter(comd);
 97                 da.Fill(dt);
 98                 conn.Close();
 99                 comd.Parameters.Clear();
100                 return dt;
101             }
102         }
103         /// <summary>
104         /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
105         /// </summary>
106         /// <param name="cmdText">查询语句</param>
107         /// <param name="cmdType">查询的方式</param>
108         /// <param name="cmdParameters">可选参数列表</param>
109         /// <returns>SqlDataReader(注意手工关闭SqlDataReader)</returns>
110         public SqlDataReader ExecuteDataReader(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters)
111         {
112             SqlConnection conn = new SqlConnection(connectionString);
113             SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
114             try
115             {
116 
117                 conn.Open();
118                 SqlDataReader dtr = comd.ExecuteReader(CommandBehavior.CloseConnection);
119                 comd.Parameters.Clear();
120                 return dtr;
121             }
122             catch
123             {
124                 conn.Close();
125                 throw;
126             }
127 
128         }
129         /// <summary>
130         /// 执行SQL语句，返回影响的记录数
131         /// </summary>
132         /// <param name="cmdText">查询语句</param>
133         /// <param name="cmdType">查询的方式</param>
134         /// <param name="cmdParameters">可选参数列表</param>
135         /// <param name="ifreturn">是否为返回参数值（一般为存储过程）</param>
136         /// <returns>返回影响的记录数一般为整数</returns>
137         public int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters, bool ifreturn)
138         {
139             using (SqlConnection conn = new SqlConnection(connectionString))
140             {
141                 SqlCommand comd;
142                 int result;
143 
144                 if (ifreturn)
145                 {
146                     comd = GetSqlCommandByreturn(conn, cmdText, cmdType, cmdParameters);
147                     conn.Open();
148                     comd.ExecuteScalar();
149                     result = (int)comd.Parameters["ReturnValue"].Value;
150 
151                 }
152                 else
153                 {
154                     comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
155                     conn.Open();
156                     result = comd.ExecuteNonQuery();
157                 }
158                 comd.Parameters.Clear();
159                 conn.Close();
160                 return result;
161 
162             }
163         }
164         /// <summary>
165         /// 执行一条计算查询结果语句，返回查询结果（object）
166         /// </summary>
167         /// <param name="cmdText">查询语句</param>
168         /// <param name="cmdType">查询的方式</param>
169         /// <param name="cmdParameters">可选参数列表</param>
170         /// <returns>查询结果（object）</returns>
171         public object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] cmdParameters)
172         {
173             using (SqlConnection conn = new SqlConnection(connectionString))
174             {
175                 SqlCommand comd = GetSqlCommand(conn, cmdText, cmdType, cmdParameters);
176                 conn.Open();
177                 object result = comd.ExecuteScalar();
178                 comd.Parameters.Clear();
179                 conn.Close();
180                 return result;
181             }
182         }
183         /// <summary>
184         /// 缓存SQL 参数列表
185         /// </summary>
186         /// <param name="cacheKey">缓存对象名</param>
187         /// <param name="cheparams">要缓存的参数列表 SqlParameter [] 类型</param>
188         public static void CacheParameter(string cacheKey, SqlParameter[] cheparams)
189         {
190             parmCache[cacheKey] = cheparams;
191         }
192         /// <summary>
193         /// 获取在HashTable之是的缓存参数列表
194         /// </summary>
195         /// <param name="cacheKey">要获取的缓存对象名</param>
196         /// <returns>根据缓存对象名，取得缓存的参数列表</returns>
197         public static SqlParameter[] GetCachedParameters(string cacheKey)
198         {
199             SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
200 
201             if (cachedParms == null)
202                 return null;
203 
204             SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
205 
206             for (int i = 0, j = cachedParms.Length; i < j; i++)
207                 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
208 
209             return clonedParms;
210         }
211         #region IDisposable 成员
212 
213         public void Dispose()
214         {
215             //throw new Exception ("The method or operation is not implemented.");
216             //Dispose (true);
217             //GC.SuppressFinalize (this);
218             //GC.g
219             //globalConnection.Dispose ();
220             connectionString = null;
221             GC.ReRegisterForFinalize(this);
222         }
223         //public void Dispose(bool iftrue)
224         //{
225         //    if (iftrue)
226         //    {
227 
228         //    }
229         //}
230         ~DBUltility()
231         {
232             //globalConnection.Dispose ();
233             connectionString = null;
234 
235         }
236         #endregion
237     }
238 }
239