C#通用的数据操作类

C#通用的数据操作类
拷贝后保存为 /App_Code/SQLHelper.cs,修改命名空间与 104 行的数据连接字符串即可

  1. /// <summary>通用数据库接口   
  2. ///    
  3. /// 数据库访问的一些常用方法  
  4. ///    
  5. /// </summary>   
  6. using System;   
  7. using System.Collections;   
  8. using System.Collections.Specialized;   
  9. using System.Data;   
  10. using System.Data.SqlClient;   
  11. using System.Configuration;   
  12. using System.Data.Common;   
  13. using System.Collections.Generic;   
  14. namespace Legalsoft.Wizard.DBUtility   
  15. {   
  16.     public enum EffentNextType   
  17.     {   
  18.         /// <summary>   
  19.         /// 对其他语句无任何影响    
  20.         /// </summary>   
  21.         None,   
  22.         /// <summary>   
  23.         /// 当前语句必须为"SELECT COUNT(1) FROM .."格式,如果存在则继续执行,不存在回滚事务   
  24.         /// </summary>   
  25.         WhenHaveContine,   
  26.         /// <summary>   
  27.         /// 当前语句必须为"SELECT COUNT(1) FROM .."格式,如果不存在则继续执行,存在回滚事务   
  28.         /// </summary>   
  29.         WhenNoHaveContine,   
  30.         /// <summary>   
  31.         /// 当前语句影响到的行数必须大于0,否则回滚事务   
  32.         /// </summary>   
  33.         ExcuteEffectRows,   
  34.         /// <summary>   
  35.         /// 引发事件-当前语句必须为"SELECT COUNT(1) FROM .."格式,如果不存在则继续执行,存在回滚事务   
  36.         /// </summary>   
  37.         SolicitationEvent   
  38.     }   
  39.     public class CommandInfo   
  40.     {   
  41.         public object ShareObject = null;   
  42.         public object OriginalData = null;   
  43.         event EventHandler _solicitationEvent;   
  44.         public event EventHandler SolicitationEvent   
  45.         {   
  46.             add   
  47.             {   
  48.                 _solicitationEvent += value;   
  49.             }   
  50.             remove   
  51.             {   
  52.                 _solicitationEvent -= value;   
  53.             }   
  54.         }   
  55.         public void OnSolicitationEvent()   
  56.         {   
  57.             if (_solicitationEvent != null)   
  58.             {   
  59.                 _solicitationEvent(this,new EventArgs());   
  60.             }   
  61.         }   
  62.         public string CommandText;   
  63.         public System.Data.Common.DbParameter[] Parameters;   
  64.         public EffentNextType EffentNextType = EffentNextType.None;   
  65.         public CommandInfo()   
  66.         {   
  67.         }   
  68.         public CommandInfo(string sqlText, SqlParameter[] para)   
  69.         {   
  70.             this.CommandText = sqlText;   
  71.             this.Parameters = para;   
  72.         }   
  73.         public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type)   
  74.         {   
  75.             this.CommandText = sqlText;   
  76.             this.Parameters = para;   
  77.             this.EffentNextType = type;   
  78.         }   
  79.     }   
  80.     /// <summary>   
  81.     /// 数据访问抽象基础类   
  82.     /// Copyright (C) 2004-2008 By LiTianPing    
  83.     /// </summary>   
  84.     public abstract class SQLHelper   
  85.     {   
  86.         //数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.         
  87.         public static string connectionString = "";   
  88.         public SQLHelper()   
  89.         {   
  90.             connectionString = @"DATA SOURCE=(local);UID=sa;PWD=111111;DATABASE=LegalSoft";   
  91.         }  
  92.         #region 公用方法   
  93.         /// <summary>   
  94.         /// 读取指定图片的二进制信息   
  95.         /// </summary>   
  96.         /// <param name="id"></param>   
  97.         /// <returns></returns>   
  98.         public object LoadImage(int id)   
  99.         {   
  100.             SqlConnection myConnection = new SqlConnection(connectionString);   
  101.             SqlCommand myCommand = new SqlCommand("SELECT [bImage] FROM [tblCAIPIAO_NEWS_IMG] WHERE id=@id", myConnection);   
  102.             myCommand.CommandType = CommandType.Text;   
  103.             myCommand.Parameters.Add(new SqlParameter("@id", id));   
  104.             myConnection.Open();   
  105.             object result = myCommand.ExecuteScalar();   
  106.             try  
  107.             {   
  108.                 return new System.IO.MemoryStream((byte[])result);   
  109.             }   
  110.             catch (ArgumentNullException e)   
  111.             {   
  112.                 return null;   
  113.             }   
  114.             finally  
  115.             {   
  116.                 myConnection.Close();   
  117.             }   
  118.         }   
  119.         /// <summary>   
  120.         /// 判断是否存在某表的某个字段   
  121.         /// </summary>   
  122.         /// <param name="tableName">表名称</param>   
  123.         /// <param name="columnName">列名称</param>   
  124.         /// <returns>是否存在</returns>   
  125.         public static bool ColumnExists(string tableName, string columnName)   
  126.         {   
  127.             string sql = "select count(1) from syscolumns where [id]=object_id("" + tableName + "") and [name]="" + columnName + """;   
  128.             object res = GetSingle(sql);   
  129.             if (res == null)   
  130.             {   
  131.                 return false;   
  132.             }   
  133.             return Convert.ToInt32(res) > 0;   
  134.         }   
  135.         public static int GetMaxID(string FieldName, string TableName)   
  136.         {   
  137.             string strsql = "select max(" + FieldName + ")+1 from " + TableName;   
  138.             object obj = SQLHelper.GetSingle(strsql);   
  139.             if (obj == null)   
  140.             {   
  141.                 return 1;   
  142.             }   
  143.             else  
  144.             {   
  145.                 return int.Parse(obj.ToString());   
  146.             }   
  147.         }   
  148.         public static bool Exists(string strSql)   
  149.         {   
  150.             object obj = SQLHelper.GetSingle(strSql);   
  151.             int cmdresult;   
  152.             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))   
  153.             {   
  154.                 cmdresult = 0;   
  155.             }   
  156.             else  
  157.             {   
  158.                 cmdresult = int.Parse(obj.ToString());   
  159.             }   
  160.             if (cmdresult == 0)   
  161.             {   
  162.                 return false;   
  163.             }   
  164.             else  
  165.             {   
  166.                 return true;   
  167.             }   
  168.         }   
  169.         /// <summary>   
  170.         /// 表是否存在   
  171.         /// </summary>   
  172.         /// <param name="TableName"></param>   
  173.         /// <returns></returns>   
  174.         public static bool TabExists(string TableName)   
  175.         {   
  176.             string strsql = "select count(*) from sysobjects where id = object_id(N"[" + TableName + "]") and OBJECTPROPERTY(id, N"IsUserTable") = 1";   
  177.             //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N"[dbo].[" + TableName + "]") AND type in (N"U")";   
  178.             object obj = SQLHelper.GetSingle(strsql);   
  179.             int cmdresult;   
  180.             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))   
  181.             {   
  182.                 cmdresult = 0;   
  183.             }   
  184.             else  
  185.             {   
  186.                 cmdresult = int.Parse(obj.ToString());   
  187.             }   
  188.             if (cmdresult == 0)   
  189.             {   
  190.                 return false;   
  191.             }   
  192.             else  
  193.             {   
  194.                 return true;   
  195.             }   
  196.         }   
  197.         public static bool Exists(string strSql, params SqlParameter[] cmdParms)   
  198.         {   
  199.             object obj = SQLHelper.GetSingle(strSql, cmdParms);   
  200.             int cmdresult;   
  201.             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))   
  202.             {   
  203.                 cmdresult = 0;   
  204.             }   
  205.             else  
  206.             {   
  207.                 cmdresult = int.Parse(obj.ToString());   
  208.             }   
  209.             if (cmdresult == 0)   
  210.             {   
  211.                 return false;   
  212.             }   
  213.             else  
  214.             {   
  215.                 return true;   
  216.             }   
  217.         }  
  218.         #endregion  
  219.         #region  执行简单SQL语句   
  220.         /// <summary>   
  221.         /// 执行SQL语句,返回影响的记录数   
  222.         /// </summary>   
  223.         /// <param name="SQLString">SQL语句</param>   
  224.         /// <returns>影响的记录数</returns>   
  225.         public static int ExecuteSql(string SQLString)   
  226.         {   
  227.             using (SqlConnection connection = new SqlConnection(connectionString))   
  228.             {   
  229.                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))   
  230.                 {   
  231.                     try  
  232.                     {   
  233.                         connection.Open();   
  234.                         int rows = cmd.ExecuteNonQuery();   
  235.                         return rows;   
  236.                     }   
  237.                     catch (System.Data.SqlClient.SqlException e)   
  238.                     {   
  239.                         connection.Close();   
  240.                         throw e;   
  241.                     }   
  242.                 }   
  243.             }   
  244.         }   
  245.         public static int ExecuteSqlByTime(string SQLString, int Times)   
  246.         {   
  247.             using (SqlConnection connection = new SqlConnection(connectionString))   
  248.             {   
  249.                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))   
  250.                 {   
  251.                     try  
  252.                     {   
  253.                         connection.Open();   
  254.                         cmd.CommandTimeout = Times;   
  255.                         int rows = cmd.ExecuteNonQuery();   
  256.                         return rows;   
  257.                     }   
  258.                     catch (System.Data.SqlClient.SqlException e)   
  259.                     {   
  260.                         connection.Close();   
  261.                         throw e;   
  262.                     }   
  263.                 }   
  264.             }   
  265.         }   
  266.         /// <summary>   
  267.         /// 执行Sql和Oracle滴混合事务   
  268.         /// </summary>   
  269.         /// <param name="list">SQL命令行列表</param>   
  270.         /// <param name="oracleCmdSqlList">Oracle命令行列表</param>   
  271.         /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>   
  272.         public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)   
  273.         {   
  274.             using (SqlConnection conn = new SqlConnection(connectionString))   
  275.             {   
  276.                 conn.Open();   
  277.                 SqlCommand cmd = new SqlCommand();   
  278.                 cmd.Connection = conn;   
  279.                 SqlTransaction tx = conn.BeginTransaction();   
  280.                 cmd.Transaction = tx;   
  281.                 try  
  282.                 {   
  283.                     foreach (CommandInfo myDE in list)   
  284.                     {   
  285.                         string cmdText = myDE.CommandText;   
  286.                         SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;   
  287.                         PrepareCommand(cmd, conn, tx, cmdText, cmdParms);   
  288.                         if (myDE.EffentNextType == EffentNextType.SolicitationEvent)   
  289.                         {   
  290.                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)   
  291.                             {   
  292.                                 tx.Rollback();   
  293.                                 throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");   
  294.                                 //return 0;   
  295.                             }   
  296.                             object obj = cmd.ExecuteScalar();   
  297.                             bool isHave = false;   
  298.                             if (obj == null && obj == DBNull.Value)   
  299.                             {   
  300.                                 isHave = false;   
  301.                             }   
  302.                             isHave = Convert.ToInt32(obj) > 0;   
  303.                             if (isHave)   
  304.                             {   
  305.                                 //引发事件   
  306.                                 myDE.OnSolicitationEvent();   
  307.                             }   
  308.                         }   
  309.                         if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)   
  310.                         {   
  311.                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)   
  312.                             {   
  313.                                 tx.Rollback();   
  314.                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");   
  315.                                 //return 0;   
  316.                             }   
  317.                             object obj = cmd.ExecuteScalar();   
  318.                             bool isHave = false;   
  319.                             if (obj == null && obj == DBNull.Value)   
  320.                             {   
  321.                                 isHave = false;   
  322.                             }   
  323.                             isHave = Convert.ToInt32(obj) > 0;   
  324.                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)   
  325.                             {   
  326.                                 tx.Rollback();   
  327.                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");   
  328.                                 //return 0;   
  329.                             }   
  330.                             if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)   
  331.                             {   
  332.                                 tx.Rollback();   
  333.                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");   
  334.                                 //return 0;   
  335.                             }   
  336.                             continue;   
  337.                         }   
  338.                         int val = cmd.ExecuteNonQuery();   
  339.                         if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)   
  340.                         {   
  341.                             tx.Rollback();   
  342.                             throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");   
  343.                             //return 0;   
  344.                         }   
  345.                         cmd.Parameters.Clear();   
  346.                     }   
  347.                     //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");   
  348.                     //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);   
  349.                     //if (!res)   
  350.                     //{   
  351.                     //    tx.Rollback();   
  352.                     //    throw new Exception("Oracle执行失败");   
  353.                         // return -1;   
  354.                     //}   
  355.                     tx.Commit();   
  356.                     return 1;   
  357.                 }   
  358.                 catch (System.Data.SqlClient.SqlException e)   
  359.                 {   
  360.                     tx.Rollback();   
  361.                     throw e;   
  362.                 }   
  363.                 catch (Exception e)   
  364.                 {   
  365.                     tx.Rollback();   
  366.                     throw e;   
  367.                 }   
  368.             }   
  369.         }   
  370.         /// <summary>   
  371.         /// 执行多条SQL语句,实现数据库事务。   
  372.         /// </summary>   
  373.         /// <param name="SQLStringList">多条SQL语句</param>        
  374.         public static int ExecuteSqlTran(List<String> SQLStringList)   
  375.         {   
  376.             using (SqlConnection conn = new SqlConnection(connectionString))   
  377.             {   
  378.                 conn.Open();   
  379.                 SqlCommand cmd = new SqlCommand();   
  380.                 cmd.Connection = conn;   
  381.                 SqlTransaction tx = conn.BeginTransaction();   
  382.                 cmd.Transaction = tx;   
  383.                 try  
  384.                 {   
  385.                     int count = 0;   
  386.                     for (int n = 0; n < SQLStringList.Count; n++)   
  387.                     {   
  388.                         string strsql = SQLStringList[n];   
  389.                         if (strsql.Trim().Length > 1)   
  390.                         {   
  391.                             cmd.CommandText = strsql;   
  392.                             count += cmd.ExecuteNonQuery();   
  393.                         }   
  394.                     }   
  395.                     tx.Commit();   
  396.                     return count;   
  397.                 }   
  398.                 catch  
  399.                 {   
  400.                     tx.Rollback();   
  401.                     return 0;   
  402.                 }   
  403.             }   
  404.         }   
  405.         /// <summary>   
  406.         /// 执行带一个存储过程参数的的SQL语句。   
  407.         /// </summary>   
  408.         /// <param name="SQLString">SQL语句</param>   
  409.         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>   
  410.         /// <returns>影响的记录数</returns>   
  411.         public static int ExecuteSql(string SQLString, string content)   
  412.         {   
  413.             using (SqlConnection connection = new SqlConnection(connectionString))   
  414.             {   
  415.                 SqlCommand cmd = new SqlCommand(SQLString, connection);   
  416.                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);   
  417.                 myParameter.Value = content;   
  418.                 cmd.Parameters.Add(myParameter);   
  419.                 try  
  420.                 {   
  421.                     connection.Open();   
  422.                     int rows = cmd.ExecuteNonQuery();   
  423.                     return rows;   
  424.                 }   
  425.                 catch (System.Data.SqlClient.SqlException e)   
  426.                 {   
  427.                     throw e;   
  428.                 }   
  429.                 finally  
  430.                 {   
  431.                     cmd.Dispose();   
  432.                     connection.Close();   
  433.                 }   
  434.             }   
  435.         }   
  436.         /// <summary>   
  437.         /// 执行带一个存储过程参数的的SQL语句。   
  438.         /// </summary>   
  439.         /// <param name="SQLString">SQL语句</param>   
  440.         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>   
  441.         /// <returns>影响的记录数</returns>   
  442.         public static object ExecuteSqlGet(string SQLString, string content)   
  443.         {   
  444.             using (SqlConnection connection = new SqlConnection(connectionString))   
  445.             {   
  446.                 SqlCommand cmd = new SqlCommand(SQLString, connection);   
  447.                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);   
  448.                 myParameter.Value = content;   
  449.                 cmd.Parameters.Add(myParameter);   
  450.                 try  
  451.                 {   
  452.                     connection.Open();   
  453.                     object obj = cmd.ExecuteScalar();   
  454.                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))   
  455.                     {   
  456.                         return null;   
  457.                     }   
  458.                     else  
  459.                     {   
  460.                         return obj;   
  461.                     }   
  462.                 }   
  463.                 catch (System.Data.SqlClient.SqlException e)   
  464.                 {   
  465.                     throw e;   
  466.                 }   
  467.                 finally  
  468.                 {   
  469.                     cmd.Dispose();   
  470.                     connection.Close();   
  471.                 }   
  472.             }   
  473.         }   
  474.         /// <summary>   
  475.         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)   
  476.         /// </summary>   
  477.         /// <param name="strSQL">SQL语句</param>   
  478.         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>   
  479.         /// <returns>影响的记录数</returns>   
  480.         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)   
  481.         {   
  482.             using (SqlConnection connection = new SqlConnection(connectionString))   
  483.             {   
  484.                 SqlCommand cmd = new SqlCommand(strSQL, connection);   
  485.                 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);   
  486.                 myParameter.Value = fs;   
  487.                 cmd.Parameters.Add(myParameter);   
  488.                 try  
  489.                 {   
  490.                     connection.Open();   
  491.                     int rows = cmd.ExecuteNonQuery();   
  492.                     return rows;   
  493.                 }   
  494.                 catch (System.Data.SqlClient.SqlException e)   
  495.                 {   
  496.                     throw e;   
  497.                 }   
  498.                 finally  
  499.                 {   
  500.                     cmd.Dispose();   
  501.                     connection.Close();   
  502.                 }   
  503.             }   
  504.         }   
  505.         /// <summary>   
  506.         /// 执行一条计算查询结果语句,返回查询结果(object)。   
  507.         /// </summary>   
  508.         /// <param name="SQLString">计算查询结果语句</param>   
  509.         /// <returns>查询结果(object)</returns>   
  510.         public static object GetSingle(string SQLString)   
  511.         {   
  512.             using (SqlConnection connection = new SqlConnection(connectionString))   
  513.             {   
  514.                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))   
  515.                 {   
  516.                     try  
  517.                     {   
  518.                         connection.Open();   
  519.                         object obj = cmd.ExecuteScalar();   
  520.                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))   
  521.                         {   
  522.                             return null;   
  523.                         }   
  524.                         else  
  525.                         {   
  526.                             return obj;   
  527.                         }   
  528.                     }   
  529.                     catch (System.Data.SqlClient.SqlException e)   
  530.                     {   
  531.                         connection.Close();   
  532.                         throw e;   
  533.                     }   
  534.                 }   
  535.             }   
  536.         }   
  537.         public static object GetSingle(string SQLString, int Times)   
  538.         {   
  539.             using (SqlConnection connection = new SqlConnection(connectionString))   
  540.             {   
  541.                 using (SqlCommand cmd = new SqlCommand(SQLString, connection))   
  542.                 {   
  543.                     try  
  544.                     {   
  545.                         connection.Open();   
  546.                         cmd.CommandTimeout = Times;   
  547.                         object obj = cmd.ExecuteScalar();   
  548.                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))   
  549.                         {   
  550.                             return null;   
  551.                         }   
  552.                         else  
  553.                         {   
  554.                             return obj;   
  555.                         }   
  556.                     }   
  557.                     catch (System.Data.SqlClient.SqlException e)   
  558.                     {   
  559.                         connection.Close();   
  560.                         throw e;   
  561.                     }   
  562.                 }   
  563.             }   
  564.         }   
  565.         /// <summary>   
  566.         /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )   
  567.         /// </summary>   
  568.         /// <param name="strSQL">查询语句</param>   
  569.         /// <returns>SqlDataReader</returns>   
  570.         public static SqlDataReader ExecuteReader(string strSQL)   
  571.         {   
  572.             SqlConnection connection = new SqlConnection(connectionString);   
  573.             SqlCommand cmd = new SqlCommand(strSQL, connection);   
  574.             try  
  575.             {   
  576.                 connection.Open();   
  577.                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);   
  578.                 return myReader;   
  579.             }   
  580.             catch (System.Data.SqlClient.SqlException e)   
  581.             {   
  582.                 throw e;   
  583.             }   
  584.         }   
  585.         /// <summary>   
  586.         /// 执行查询语句,返回DataSet   
  587.         /// </summary>   
  588.         /// <param name="SQLString">查询语句</param>   
  589.         /// <returns>DataSet</returns>   
  590.         public static DataSet Query(string SQLString)   
  591.         {   
  592.             using (SqlConnection connection = new SqlConnection(connectionString))   
  593.             {   
  594.                 DataSet ds = new DataSet();   
  595.                 try  
  596.                 {   
  597.                     connection.Open();   
  598.                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);   
  599.                     command.Fill(ds, "ds");   
  600.                 }   
  601.                 catch (System.Data.SqlClient.SqlException ex)   
  602.                 {   
  603.                     throw new Exception(ex.Message);   
  604.                 }   
  605.                 return ds;   
  606.             }   
  607.         }   
  608.         /// <summary>   
  609.         /// 查询并得到数据集DataSet   
  610.         /// </summary>   
  611.         /// <param name="SQLString"></param>   
  612.         /// <param name="Times"></param>   
  613.         /// <returns></returns>   
  614.         public static DataSet Query(string SQLString, int Times)   
  615.         {   
  616.             using (SqlConnection connection = new SqlConnection(connectionString))   
  617.             {   
  618.                 DataSet ds = new DataSet();   
  619.                 try  
  620.                 {   
  621.                     connection.Open();   
  622.                     SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);   
  623.                     command.SelectCommand.CommandTimeout = Times;   
  624.                     command.Fill(ds, "ds");   
  625.                 }   
  626.                 catch (System.Data.SqlClient.SqlException ex)   
  627.                 {   
  628.                     throw new Exception(ex.Message);   
  629.                 }   
  630.                 return ds;   
  631.             }   
  632.         }  
  633.         #endregion  
  634.         #region 执行带参数的SQL语句   
  635.         /// <summary>   
  636.         /// 执行SQL语句,返回影响的记录数   
  637.         /// </summary>   
  638.         /// <param name="SQLString">SQL语句</param>   
  639.         /// <returns>影响的记录数</returns>   
  640.         public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)   
  641.         {   
  642.             using (SqlConnection connection = new SqlConnection(connectionString))   
  643.             {   
  644.                 using (SqlCommand cmd = new SqlCommand())   
  645.                 {   
  646.                     try  
  647.                     {   
  648.                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);   
  649.                         int rows = cmd.ExecuteNonQuery();   
  650.                         cmd.Parameters.Clear();   
  651.                         return rows;   
  652.                     }   
  653.                     catch (System.Data.SqlClient.SqlException e)   
  654.                     {   
  655.                         throw e;   
  656.                     }   
  657.                 }   
  658.             }   
  659.         }   
  660.         /// <summary>   
  661.         /// 执行多条SQL语句,实现数据库事务。   
  662.         /// </summary>   
  663.         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>   
  664.         public static void ExecuteSqlTran(Hashtable SQLStringList)   
  665.         {   
  666.             using (SqlConnection conn = new SqlConnection(connectionString))   
  667.             {   
  668.                 conn.Open();   
  669.                 using (SqlTransaction trans = conn.BeginTransaction())   
  670.                 {   
  671.                     SqlCommand cmd = new SqlCommand();   
  672.                     try  
  673.                     {   
  674.                         //循环   
  675.                         foreach (DictionaryEntry myDE in SQLStringList)   
  676.                         {   
  677.                             string cmdText = myDE.Key.ToString();   
  678.                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;   
  679.                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);   
  680.                             int val = cmd.ExecuteNonQuery();   
  681.                             cmd.Parameters.Clear();   
  682.                         }   
  683.                         trans.Commit();   
  684.                     }   
  685.                     catch  
  686.                     {   
  687.                         trans.Rollback();   
  688.                         throw;   
  689.                     }   
  690.                 }   
  691.             }   
  692.         }   
  693.         /// <summary>   
  694.         /// 执行多条SQL语句,实现数据库事务。   
  695.         /// </summary>   
  696.         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>   
  697.         public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)   
  698.         {   
  699.             using (SqlConnection conn = new SqlConnection(connectionString))   
  700.             {   
  701.                 conn.Open();   
  702.                 using (SqlTransaction trans = conn.BeginTransaction())   
  703.                 {   
  704.                     SqlCommand cmd = new SqlCommand();   
  705.                     try  
  706.                     {   
  707.                         int count = 0;   
  708.                         //循环   
  709.                         foreach (CommandInfo myDE in cmdList)   
  710.                         {   
  711.                             string cmdText = myDE.CommandText;   
  712.                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;   
  713.                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);   
  714.                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)   
  715.                             {   
  716.                                 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)   
  717.                                 {   
  718.                                     trans.Rollback();   
  719.                                     return 0;   
  720.                                 }   
  721.                                 object obj = cmd.ExecuteScalar();   
  722.                                 bool isHave = false;   
  723.                                 if (obj == null && obj == DBNull.Value)   
  724.                                 {   
  725.                                     isHave = false;   
  726.                                 }   
  727.                                 isHave = Convert.ToInt32(obj) > 0;   
  728.                                 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)   
  729.                                 {   
  730.                                     trans.Rollback();   
  731.                                     return 0;   
  732.                                 }   
  733.                                 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)   
  734.                                 {   
  735.                                     trans.Rollback();   
  736.                                     return 0;   
  737.                                 }   
  738.                                 continue;   
  739.                             }   
  740.                             int val = cmd.ExecuteNonQuery();   
  741.                             count += val;   
  742.                             if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)   
  743.                             {   
  744.                                 trans.Rollback();   
  745.                                 return 0;   
  746.                             }   
  747.                             cmd.Parameters.Clear();   
  748.                         }   
  749.                         trans.Commit();   
  750.                         return count;   
  751.                     }   
  752.                     catch  
  753.                     {   
  754.                         trans.Rollback();   
  755.                         throw;   
  756.                     }   
  757.                 }   
  758.             }   
  759.         }   
  760.         /// <summary>   
  761.         /// 执行多条SQL语句,实现数据库事务。   
  762.         /// </summary>   
  763.         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>   
  764.         public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)   
  765.         {   
  766.             using (SqlConnection conn = new SqlConnection(connectionString))   
  767.             {   
  768.                 conn.Open();   
  769.                 using (SqlTransaction trans = conn.BeginTransaction())   
  770.                 {   
  771.                     SqlCommand cmd = new SqlCommand();   
  772.                     try  
  773.                     {   
  774.                         int indentity = 0;   
  775.                         //循环   
  776.                         foreach (CommandInfo myDE in SQLStringList)   
  777.                         {   
  778.                             string cmdText = myDE.CommandText;   
  779.                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;   
  780.                             foreach (SqlParameter q in cmdParms)   
  781.                             {   
  782.                                 if (q.Direction == ParameterDirection.InputOutput)   
  783.                                 {   
  784.                                     q.Value = indentity;   
  785.                                 }   
  786.                             }   
  787.                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);   
  788.                             int val = cmd.ExecuteNonQuery();   
  789.                             foreach (SqlParameter q in cmdParms)   
  790.                             {   
  791.                                 if (q.Direction == ParameterDirection.Output)   
  792.                                 {   
  793.                                     indentity = Convert.ToInt32(q.Value);   
  794.                                 }   
  795.                             }   
  796.                             cmd.Parameters.Clear();   
  797.                         }   
  798.                         trans.Commit();   
  799.                     }   
  800.                     catch  
  801.                     {   
  802.                         trans.Rollback();   
  803.                         throw;   
  804.                     }   
  805.                 }   
  806.             }   
  807.         }   
  808.         /// <summary>   
  809.         /// 执行多条SQL语句,实现数据库事务。   
  810.         /// </summary>   
  811.         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>   
  812.         public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)   
  813.         {   
  814.             using (SqlConnection conn = new SqlConnection(connectionString))   
  815.             {   
  816.                 conn.Open();   
  817.                 using (SqlTransaction trans = conn.BeginTransaction())   
  818.                 {   
  819.                     SqlCommand cmd = new SqlCommand();   
  820.                     try  
  821.                     {   
  822.                         int indentity = 0;   
  823.                         //循环   
  824.                         foreach (DictionaryEntry myDE in SQLStringList)   
  825.                         {   
  826.                             string cmdText = myDE.Key.ToString();   
  827.                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;   
  828.                             foreach (SqlParameter q in cmdParms)   
  829.                             {   
  830.                                 if (q.Direction == ParameterDirection.InputOutput)   
  831.                                 {   
  832.                                     q.Value = indentity;   
  833.                                 }   
  834.                             }   
  835.                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);   
  836.                             int val = cmd.ExecuteNonQuery();   
  837.                             foreach (SqlParameter q in cmdParms)   
  838.                             {   
  839.                                 if (q.Direction == ParameterDirection.Output)   
  840.                                 {   
  841.                                     indentity = Convert.ToInt32(q.Value);   
  842.                                 }   
  843.                             }   
  844.                             cmd.Parameters.Clear();   
  845.                         }   
  846.                         trans.Commit();   
  847.                     }   
  848.                     catch  
  849.                     {   
  850.                         trans.Rollback();   
  851.                         throw;   
  852.                     }   
  853.                 }   
  854.             }   
  855.         }   
  856.         /// <summary>   
  857.         /// 执行一条计算查询结果语句,返回查询结果(object)。   
  858.         /// </summary>   
  859.         /// <param name="SQLString">计算查询结果语句</param>   
  860.         /// <returns>查询结果(object)</returns>   
  861.         public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)   
  862.         {   
  863.             using (SqlConnection connection = new SqlConnection(connectionString))   
  864.             {   
  865.                 using (SqlCommand cmd = new SqlCommand())   
  866.                 {   
  867.                     try  
  868.                     {   
  869.                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);   
  870.                         object obj = cmd.ExecuteScalar();   
  871.                         cmd.Parameters.Clear();   
  872.                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))   
  873.                         {   
  874.                             return null;   
  875.                         }   
  876.                         else  
  877.                         {   
  878.                             return obj;   
  879.                         }   
  880.                     }   
  881.                     catch (System.Data.SqlClient.SqlException e)   
  882.                     {   
  883.                         throw e;   
  884.                     }   
  885.                 }   
  886.             }   
  887.         }   
  888.         /// <summary>   
  889.         /// 执行查询语句,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )   
  890.         /// </summary>   
  891.         /// <param name="strSQL">查询语句</param>   
  892.         /// <returns>SqlDataReader</returns>   
  893.         public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)   
  894.         {   
  895.             SqlConnection connection = new SqlConnection(connectionString);   
  896.             SqlCommand cmd = new SqlCommand();   
  897.             try  
  898.             {   
  899.                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);   
  900.                 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);   
  901.                 cmd.Parameters.Clear();   
  902.                 return myReader;   
  903.             }   
  904.             catch (System.Data.SqlClient.SqlException e)   
  905.             {   
  906.                 throw e;   
  907.             }   
  908.             //       finally   
  909.             //       {   
  910.             //          cmd.Dispose();   
  911.             //          connection.Close();   
  912.             //       }     
  913.         }   
  914.         /// <summary>   
  915.         /// 执行查询语句,返回DataSet   
  916.         /// </summary>   
  917.         /// <param name="SQLString">查询语句</param>   
  918.         /// <returns>DataSet</returns>   
  919.         public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)   
  920.         {   
  921.             using (SqlConnection connection = new SqlConnection(connectionString))   
  922.             {   
  923.                 SqlCommand cmd = new SqlCommand();   
  924.                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);   
  925.                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))   
  926.                 {   
  927.                     DataSet ds = new DataSet();   
  928.                     try  
  929.                     {   
  930.                         da.Fill(ds, "ds");   
  931.                         cmd.Parameters.Clear();   
  932.                     }   
  933.                     catch (System.Data.SqlClient.SqlException ex)   
  934.                     {   
  935.                         throw new Exception(ex.Message);   
  936.                     }   
  937.                     return ds;   
  938.                 }   
  939.             }   
  940.         }   
  941.         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)   
  942.         {   
  943.             if (conn.State != ConnectionState.Open)   
  944.                 conn.Open();   
  945.             cmd.Connection = conn;   
  946.             cmd.CommandText = cmdText;   
  947.             if (trans != null)   
  948.                 cmd.Transaction = trans;   
  949.             cmd.CommandType = CommandType.Text;//cmdType;   
  950.             if (cmdParms != null)   
  951.             {   
  952.                 foreach (SqlParameter parameter in cmdParms)   
  953.                 {   
  954.                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&   
  955.                         (parameter.Value == null))   
  956.                     {   
  957.                         parameter.Value = DBNull.Value;   
  958.                     }   
  959.                     cmd.Parameters.Add(parameter);   
  960.                 }   
  961.             }   
  962.         }  
  963.         #endregion  
  964.         #region 存储过程操作   
  965.         /// <summary>   
  966.         /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close )   
  967.         /// </summary>   
  968.         /// <param name="storedProcName">存储过程名</param>   
  969.         /// <param name="parameters">存储过程参数</param>   
  970.         /// <returns>SqlDataReader</returns>   
  971.         public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)   
  972.         {   
  973.             SqlConnection connection = new SqlConnection(connectionString);   
  974.             SqlDataReader returnReader;   
  975.             connection.Open();   
  976.             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);   
  977.             command.CommandType = CommandType.StoredProcedure;   
  978.             returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);   
  979.             return returnReader;   
  980.         }   
  981.         /// <summary>   
  982.         /// 执行存储过程   
  983.         /// </summary>   
  984.         /// <param name="storedProcName">存储过程名</param>   
  985.         /// <param name="parameters">存储过程参数</param>   
  986.         /// <param name="tableName">DataSet结果中的表名</param>   
  987.         /// <returns>DataSet</returns>   
  988.         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)   
  989.         {   
  990.             using (SqlConnection connection = new SqlConnection(connectionString))   
  991.             {   
  992.                 DataSet dataSet = new DataSet();   
  993.                 connection.Open();   
  994.                 SqlDataAdapter sqlDA = new SqlDataAdapter();   
  995.                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);   
  996.                 sqlDA.Fill(dataSet, tableName);   
  997.                 connection.Close();   
  998.                 return dataSet;   
  999.             }   
  1000.         }   
  1001.         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)   
  1002.         {   
  1003.             using (SqlConnection connection = new SqlConnection(connectionString))   
  1004.             {   
  1005.                 DataSet dataSet = new DataSet();   
  1006.                 connection.Open();   
  1007.                 SqlDataAdapter sqlDA = new SqlDataAdapter();   
  1008.                 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);   
  1009.                 sqlDA.SelectCommand.CommandTimeout = Times;   
  1010.                 sqlDA.Fill(dataSet, tableName);   
  1011.                 connection.Close();   
  1012.                 return dataSet;   
  1013.             }   
  1014.         }   
  1015.         /// <summary>   
  1016.         /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)   
  1017.         /// </summary>   
  1018.         /// <param name="connection">数据库连接</param>   
  1019.         /// <param name="storedProcName">存储过程名</param>   
  1020.         /// <param name="parameters">存储过程参数</param>   
  1021.         /// <returns>SqlCommand</returns>   
  1022.         private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)   
  1023.         {   
  1024.             SqlCommand command = new SqlCommand(storedProcName, connection);   
  1025.             command.CommandType = CommandType.StoredProcedure;   
  1026.             foreach (SqlParameter parameter in parameters)   
  1027.             {   
  1028.                 if (parameter != null)   
  1029.                 {   
  1030.                     // 检查未分配值的输出参数,将其分配以DBNull.Value.   
  1031.                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&   
  1032.                         (parameter.Value == null))   
  1033.                     {   
  1034.                         parameter.Value = DBNull.Value;   
  1035.                     }   
  1036.                     command.Parameters.Add(parameter);   
  1037.                 }   
  1038.             }   
  1039.             return command;   
  1040.         }   
  1041.         /// <summary>   
  1042.         /// 执行存储过程,返回影响的行数       
  1043.         /// </summary>   
  1044.         /// <param name="storedProcName">存储过程名</param>   
  1045.         /// <param name="parameters">存储过程参数</param>   
  1046.         /// <param name="rowsAffected">影响的行数</param>   
  1047.         /// <returns></returns>   
  1048.         public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)   
  1049.         {   
  1050.             using (SqlConnection connection = new SqlConnection(connectionString))   
  1051.             {   
  1052.                 int result;   
  1053.                 connection.Open();   
  1054.                 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);   
  1055.                 rowsAffected = command.ExecuteNonQuery();   
  1056.                 result = (int)command.Parameters["ReturnValue"].Value;   
  1057.                 //Connection.Close();   
  1058.                 return result;   
  1059.             }   
  1060.         }   
  1061.         /// <summary>   
  1062.         /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
  1063.         /// </summary>   
  1064.         /// <param name="storedProcName">存储过程名</param>   
  1065.         /// <param name="parameters">存储过程参数</param>   
  1066.         /// <returns>SqlCommand 对象实例</returns>   
  1067.         private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)   
  1068.         {   
  1069.             SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);   
  1070.             command.Parameters.Add(new SqlParameter("ReturnValue",   
  1071.                 SqlDbType.Int, 4, ParameterDirection.ReturnValue,   
  1072.                 false, 0, 0, string.Empty, DataRowVersion.Default, null));   
  1073.             return command;   
  1074.         }  
  1075.         #endregion   
  1076.     }   
  1077. }  
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值