sqlhelper - 西瓜皮

公告

  1 using System;
  2 using System.Collections;
  3 using System.Collections.Generic;
  4 using System.Configuration;
  5 using System.Data;
  6 using System.Data.SqlClient;
  7 using System.Text;
  8 
  9 namespace DAL
 10 {
 11     public class SqlHelper
 12     {
 13         //连接字符串
 14         public static string StrConn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
 15 
 16         #region 执行查询，返回DataTable对象-----------------------
 17 
 18         public static DataTable GetTable(string StrSql)
 19         {
 20             return GetTable(StrSql, null);
 21         }
 22         public static DataTable GetTable(string StrSql, SqlParameter[] Parms)
 23         {
 24             return GetTable(StrSql, Parms, CommandType.Text);
 25         }
 26         /// <summary>
 27         /// 执行查询，返回DataTable对象
 28         /// </summary>
 29         /// <param name="StrSql">sql语句</param>
 30         /// <param name="Parms">参数数组</param>
 31         /// <param name="cmdtype">Command类型</param>
 32         /// <returns>DataTable对象</returns>
 33         public static DataTable GetTable(string StrSql, SqlParameter[] Parms, CommandType cmdtype)
 34         {
 35             DataTable dt = new DataTable(); ;
 36             using (SqlConnection conn = new SqlConnection(StrConn))
 37             {
 38                 SqlDataAdapter da = new SqlDataAdapter(StrSql, conn);
 39                 da.SelectCommand.CommandType = cmdtype;
 40                 if (Parms != null)
 41                 {
 42                     da.SelectCommand.Parameters.AddRange(Parms);
 43                 }
 44                 da.Fill(dt);
 45             }
 46             return dt;
 47         }
 48 
 49         #endregion
 50 
 51         #region 执行查询，返回DataSet对象-------------------------
 52 
 53         public static DataSet GetDataSet(string StrSql)
 54         {
 55             return GetDataSet(StrSql, null);
 56         }
 57 
 58         public static DataSet GetDataSet(string StrSql, SqlParameter[] Parms)
 59         {
 60             return GetDataSet(StrSql, Parms, CommandType.Text);
 61         }
 62         /// <summary>
 63         /// 执行查询，返回DataSet对象
 64         /// </summary>
 65         /// <param name="StrSql">sql语句</param>
 66         /// <param name="Parms">参数数组</param>
 67         /// <param name="cmdtype">Command类型</param>
 68         /// <returns>DataSet对象</returns>
 69         public static DataSet GetDataSet(string StrSql, SqlParameter[] Parms, CommandType cmdtype)
 70         {
 71             DataSet dt = new DataSet(); ;
 72             using (SqlConnection conn = new SqlConnection(StrConn))
 73             {
 74                 SqlDataAdapter da = new SqlDataAdapter(StrSql, conn);
 75                 da.SelectCommand.CommandType = cmdtype;
 76                 if (Parms != null)
 77                 {
 78                     da.SelectCommand.Parameters.AddRange(Parms);
 79                 }
 80                 da.Fill(dt);
 81             }
 82             return dt;
 83         }
 84 
 85         #endregion
 86 
 87         #region 执行非查询存储过程和SQL语句-----------------------------
 88 
 89         public static int ExcuteProc(string ProcName)
 90         {
 91             return ExcuteSQL(ProcName, null, CommandType.StoredProcedure);
 92         }
 93 
 94         public static int ExcuteProc(string ProcName, SqlParameter[] Parms)
 95         {
 96             return ExcuteSQL(ProcName, Parms, CommandType.StoredProcedure);
 97         }
 98 
 99         public static int ExcuteSQL(string StrSql)
100         {
101             return ExcuteSQL(StrSql, null);
102         }
103 
104         public static int ExcuteSQL(string StrSql, SqlParameter[] Parms)
105         {
106             return ExcuteSQL(StrSql, Parms, CommandType.Text);
107         }
108 
109         /// 执行非查询存储过程和SQL语句
110         /// 增、删、改
111         /// </summary>
112         /// <param name="StrSql">要执行的SQL语句</param>
113         /// <param name="paras">参数列表，没有参数填入null</param>
114         /// <param name="cmdType">Command类型</param>
115         /// <returns>返回影响行数</returns>
116         public static int ExcuteSQL(string StrSql, SqlParameter[] Parms, CommandType cmdType)
117         {
118             int i = 0;
119             using (SqlConnection conn = new SqlConnection(StrConn))
120             {
121                 SqlCommand cmd = new SqlCommand(StrSql, conn);
122                 cmd.CommandType = cmdType;
123                 if (Parms != null)
124                 {
125                     cmd.Parameters.AddRange(Parms);
126                 }
127                 conn.Open();
128                 i = cmd.ExecuteNonQuery();
129                 conn.Close();
130             }
131             return i;
132 
133         }
134 
135         #endregion
136 
137         #region 查询获取单个值------------------------------------
138 
139         /// <summary>
140         /// 调用不带参数的存储过程获取单个值
141         /// </summary>
142         /// <param name="ProcName"></param>
143         /// <returns></returns>
144         public static object GetObjectByProc(string ProcName)
145         {
146             return GetObjectByProc(ProcName, null);
147         }
148         /// <summary>
149         /// 调用带参数的存储过程获取单个值
150         /// </summary>
151         /// <param name="ProcName"></param>
152         /// <param name="paras"></param>
153         /// <returns></returns>
154         public static object GetObjectByProc(string ProcName, SqlParameter[] Parms)
155         {
156             return GetObject(ProcName, Parms, CommandType.StoredProcedure);
157         }
158         /// <summary>
159         /// 根据sql语句获取单个值
160         /// </summary>
161         /// <param name="StrSql"></param>
162         /// <returns></returns>
163         public static object GetObject(string StrSql)
164         {
165             return GetObject(StrSql, null);
166         }
167         /// <summary>
168         /// 根据sql语句 和 参数数组获取单个值
169         /// </summary>
170         /// <param name="StrSql"></param>
171         /// <param name="paras"></param>
172         /// <returns></returns>
173         public static object GetObject(string StrSql, SqlParameter[] Parms)
174         {
175             return GetObject(StrSql, Parms, CommandType.Text);
176         }
177 
178         /// <summary>
179         /// 执行SQL语句，返回首行首列
180         /// </summary>
181         /// <param name="StrSql">要执行的SQL语句</param>
182         /// <param name="paras">参数列表，没有参数填入null</param>
183         /// <returns>返回的首行首列</returns>
184         public static object GetObject(string StrSql, SqlParameter[] Parms, CommandType cmdtype)
185         {
186             object o = null;
187             using (SqlConnection conn = new SqlConnection(StrConn))
188             {
189                 SqlCommand cmd = new SqlCommand(StrSql, conn);
190                 cmd.CommandType = cmdtype;
191                 if (Parms != null)
192                 {
193                     cmd.Parameters.AddRange(Parms);
194                 }
195 
196                 conn.Open();
197                 o = cmd.ExecuteScalar();
198                 conn.Close();
199             }
200             return o;
201 
202         }
203 
204         #endregion
205 
206         #region 查询获取DataReader------------------------------------
207 
208         /// <summary>
209         /// 调用不带参数的存储过程，返回DataReader对象
210         /// </summary>
211         /// <param name="procName">存储过程名称</param>
212         /// <returns>DataReader对象</returns>
213         public static SqlDataReader GetReaderByProc(string procName)
214         {
215             return GetReaderByProc(procName, null);
216         }
217         /// <summary>
218         /// 调用带有参数的存储过程，返回DataReader对象
219         /// </summary>
220         /// <param name="procName">存储过程名</param>
221         /// <param name="paras">参数数组</param>
222         /// <returns>DataReader对象</returns>
223         public static SqlDataReader GetReaderByProc(string procName, SqlParameter[] Parms)
224         {
225             return GetReader(procName, Parms, CommandType.StoredProcedure);
226         }
227         /// <summary>
228         /// 根据sql语句返回DataReader对象
229         /// </summary>
230         /// <param name="StrSql">sql语句</param>
231         /// <returns>DataReader对象</returns>
232         public static SqlDataReader GetReader(string StrSql)
233         {
234             return GetReader(StrSql, null);
235         }
236         /// <summary>
237         /// 根据sql语句和参数返回DataReader对象
238         /// </summary>
239         /// <param name="StrSql">sql语句</param>
240         /// <param name="paras">参数数组</param>
241         /// <returns>DataReader对象</returns>
242         public static SqlDataReader GetReader(string StrSql, SqlParameter[] Parms)
243         {
244             return GetReader(StrSql, Parms, CommandType.Text);
245         }
246         /// <summary>
247         /// 查询SQL语句获取DataReader
248         /// </summary>
249         /// <param name="StrSql">查询的SQL语句</param>
250         /// <param name="paras">参数列表，没有参数填入null</param>
251         /// <returns>查询到的DataReader（关闭该对象的时候，自动关闭连接）</returns>
252         public static SqlDataReader GetReader(string StrSql, SqlParameter[] Parms, CommandType cmdtype)
253         {
254             SqlDataReader sqldr = null;
255             SqlConnection conn = new SqlConnection(StrConn);
256             SqlCommand cmd = new SqlCommand(StrSql, conn);
257             cmd.CommandType = cmdtype;
258             if (Parms != null)
259             {
260                 cmd.Parameters.AddRange(Parms);
261             }
262             conn.Open();
263             //CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
264             sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
265             return sqldr;
266         }
267 
268         #endregion
269 
270         #region 批量插入数据---------------------------------------------
271 
272         /// <summary>
273         /// 往数据库中批量插入数据
274         /// </summary>
275         /// <param name="sourceDt">数据源表</param>
276         /// <param name="targetTable">服务器上目标表</param>
277         public static void BulkToDB(DataTable sourceDt, string targetTable)
278         {
279             SqlConnection conn = new SqlConnection(StrConn);
280             SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);   //用其它源的数据有效批量加载sql server表中
281             bulkCopy.DestinationTableName = targetTable;    //服务器上目标表的名称
282             bulkCopy.BatchSize = sourceDt.Rows.Count;   //每一批次中的行数
283             try
284             {
285                 conn.Open();
286                 if (sourceDt != null && sourceDt.Rows.Count != 0)
287                     bulkCopy.WriteToServer(sourceDt);   //将提供的数据源中的所有行复制到目标表中
288             }
289             catch (Exception ex)
290             {
291                 throw ex;
292             }
293             finally
294             {
295                 conn.Close();
296                 if (bulkCopy != null)
297                     bulkCopy.Close();
298             }
299         }
300 
301         #endregion
302 
303         #region 执行数据库事务操作
304         /// <summary>
305         /// 执行多条SQL语句，实现数据库事务。
306         /// </summary>
307         /// <param name="SQLStringList">多条SQL语句</param>        
308         public static int ExecuteSqlTran(List<String> SQLStringList)
309         {
310             using (SqlConnection conn = new SqlConnection(StrConn))
311             {
312                 conn.Open();
313                 using (SqlTransaction trans = conn.BeginTransaction()) //启动事务
314                 {
315                     SqlCommand cmd = new SqlCommand();
316                     cmd.Connection = conn;
317                     cmd.Transaction = trans;
318                     try
319                     {
320                         int count = 0;
321                         for (int n = 0; n < SQLStringList.Count; n++)
322                         {
323                             string strsql = SQLStringList[n];
324                             cmd.CommandText = strsql;
325                             count += cmd.ExecuteNonQuery();
326                         }
327                         trans.Commit();
328                         return count;
329                     }
330                     catch
331                     {
332                         trans.Rollback();
333                         return 0;
334                     }
335                 }
336             }
337         }
338 
339         /// <summary>
340         /// 执行带参数的多条SQL语句，实现数据库事务。
341         /// </summary>
342         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
343         public static int ExecuteSqlTran(Hashtable SQLStringList)
344         {
345             using (SqlConnection conn = new SqlConnection(StrConn))
346             {
347                 conn.Open();
348                 using (SqlTransaction trans = conn.BeginTransaction()) //启动事务
349                 {
350                     SqlCommand cmd = new SqlCommand();
351                     cmd.Connection = conn;
352                     cmd.Transaction = trans;
353                     try
354                     {
355                         //循环
356                         int count = 0;
357                         foreach (DictionaryEntry myDE in SQLStringList)
358                         {
359                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
360                             if (cmdParms != null)
361                             {
362                                 cmd.Parameters.AddRange(cmdParms);
363                             }
364                             cmd.CommandText = myDE.Key.ToString();
365                             count += cmd.ExecuteNonQuery();
366                             cmd.Parameters.Clear();
367                         }
368                         trans.Commit();
369                         return count;
370                     }
371                     catch
372                     {
373                         trans.Rollback();
374                         return 0;
375                     }
376                     finally
377                     {
378                         conn.Close();
379                     }
380                 }
381             }
382         }
383         #endregion
384 
385         #region 生成分页sql语句
386         /// <summary>
387         /// 生成分页sql语句
388         /// </summary>
389         /// <param name="sql">sql查询语句</param>
390         /// <param name="page">当前页</param>
391         /// <param name="rows">每页记录数</param>
392         /// <returns>生成分页sql语句</returns>
393         public static string GetPageSql(string StrSql, int page, int rows)
394         {
395             StringBuilder sb = new StringBuilder();
396             sb.Append("select * from ( ");
397             sb.Append(" select row_number()over(order by tempColumn)tempRowNumber,*");
398             sb.Append(" from (" + StrSql + ")t");
399             sb.Append(")tt");
400             sb.Append(" where tempRowNumber>" + ((page * rows) - rows).ToString());
401             return sb.ToString();
402         }
403         #endregion
404 
405     }
406 }
posted on 2013-05-25 00:44 西瓜皮阅读(465) 评论(0) 收藏举报
刷新页面返回顶部