天涯一飘絮

导航

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