SQL 工具类

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;

/// <summary>
///sql Server 数据库操作
/// </summary>
public class CMS_SqlHelp
{
   private static string sqlconstr = Convert.ToString(ConfigurationManager.ConnectionStrings["sqlconstr"]);
    public CMS_SqlHelp()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
       
    }
    /// <summary>
    /// 自定义分页
    /// </summary>
    /// <param name="tblName">表名</param>
    /// <param name="strGetFields">需要返回的列</param>
    /// <param name="fldName">排序字段名</param>
    /// <param name="PageSize">每页显示的条数</param>
    /// <param name="PageIndex">页码</param>
    /// <param name="doCount">返回记录总数，非0值则返回</param>
    /// <param name="OrderType">设置排序类型，非0值则降序</param>
    /// <param name="strWhere">查询条件，不加where</param>
    /// <returns>datatable</returns>
    public static DataTable GetData(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
    {
        string strSQL = "", strTmp = "", strOrder = "";
        if (doCount != 0)
        {
            if (strWhere != "")
            {
                strSQL = "select count(*) as Total from " + tblName + " where " + strWhere;
            }
            else
            {
                strSQL = "select count(*) as Total from " + tblName;
            }
        }
        else
        {
            if (OrderType != 0)
            {
                strTmp = "<(select min";
                strOrder = " order by " + fldName + " desc";
            }
            else
            {
                strTmp = ">(select max";
                strOrder = " order by " + fldName + " asc";
            }
            if (PageIndex == 1)
            {
                if (strWhere != "")
                {
                    strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " where " + strWhere + " " + strOrder;
                }
                else
                {
                    strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " " + strOrder;
                }
            }
            else
            {
                if (strWhere != "")
                {
                    strSQL = "select top " + PageSize + " " + strGetFields + "  from " + tblName + " where " + fldName + " " + strTmp + "(" + fldName + ") from (select top " + (PageIndex - 1) * PageSize + " " + fldName + " from " + tblName + " where " + strWhere + " " + strOrder + ") as tblTmp) and " + strWhere + " " + strOrder;
                }
                else
                {
                    strSQL = "select top " + PageSize + " " + strGetFields + "  from " + tblName + " where " + fldName + " " + strTmp + "(" + fldName + ") from (select top " + (PageIndex - 1) * PageSize + " " + fldName + " from " + tblName + "" + strOrder + ") as tblTmp)" + strOrder;
                }
            }
        }
        DataTable dt = CMS_SqlHelp.getDataTable(strSQL);
        return dt;
    }
/// <summary>
/// 执行无返回的SQL语句
/// </summary>
/// <param name="sqlStr">SQL语句</param>
/// <returns></returns>
    public static bool ExcuteSqlServer(string sqlStr)
    {
        SqlConnection con = new SqlConnection(sqlconstr);
        SqlCommand sqlcom = new SqlCommand();
        sqlcom.Connection = con;
        sqlcom.CommandText = sqlStr;
        con.Open();
        try
        {
            sqlcom.ExecuteNonQuery();
       

            return true;
        }
        catch (Exception ex)
        {
           
            errorCollecting.getError(ex);
            return false;
        }
        finally {
            con.Close();
        }
    }
    #region ExecuteScalar
    /// <summary>
    /// 返回所查结果第一列第一行
    /// </summary>
    /// <param name="sqlStr"></param>
    /// <returns></returns>
    public static object ExecuteScalar(string sqlStr)
    {
        SqlConnection con = new SqlConnection(sqlconstr);
        SqlCommand sqlcom = new SqlCommand();
        sqlcom.Connection = con;
        sqlcom.CommandText = sqlStr;
        object obj = null;
        con.Open();
        try
        {
         obj=  sqlcom.ExecuteScalar();
         return obj;

           
        }
        catch (Exception ex)
        {

            errorCollecting.getError(ex);
            return false;
        }
        finally
        {
            con.Close();
        }
    }
    #endregion
    public static SqlDataReader ExcuteSqlDataReader(string sqlStr)
    {
        SqlConnection con = new SqlConnection(sqlconstr);
        SqlCommand sqlcom = new SqlCommand();
      
        sqlcom.Connection = con;
        sqlcom.CommandText = sqlStr;
        SqlDataReader sdr = null;
             con.Open();
             sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
            return sdr;
    
            
         
    }
    /// <summary>
    /// 返回DaTable
    /// </summary>
    /// <param name="sqlStr"></param>
    /// <returns></returns>
    public static DataTable getDataTable(string sqlStr)
    {
        SqlConnection con = new SqlConnection(sqlconstr);
  

      
    

        DataTable dt = new DataTable();
        con.Open();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(sqlStr, con);
            da.Fill(dt);
        }
        catch (Exception e)
        {
            errorCollecting.getError(e);
        }
        finally
        {
            con.Close();
        }
        return dt;
    }


    #region   ExcuteProc
    /// <summary>
    /// 执行无返回值Proc
    /// </summary>
    /// <param name="sqlProc"></param>
    public static void ExecuteProcedureNonQurey(string sqlProc)
    {
        SqlConnection con = new SqlConnection(sqlconstr);

        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = sqlProc;
        com.CommandType = CommandType.StoredProcedure;

        con.Open();
        try
        {
            com.ExecuteNonQuery();
            com.Dispose();

        }
        catch (Exception ex)
        {
            errorCollecting.getError(ex);
        }
        finally
        {
            con.Close();

        }
    }

    #endregion
    /// <summary>
    /// 执行存储过程，不返回任何值
    /// </summary>
    /// <param name="storedProcedureName">存储过程名</param>
    /// <param name="parameters">参数</param>
    /*
       SqlParameter sp = new SqlParameter("@job_desc", SqlDbType.VarChar, 50);
        SqlParameter sp2 = new SqlParameter("@job_id",SqlDbType.SmallInt);

        IDataParameter[] Idp = new IDataParameter[]{sp,sp2 };
        Idp[0].Value="adff";
        Idp[1].Value=6;
        CMS_sqlProc.ExecuteProcedureNonQurey("updateMy", Idp);
   
     */
    public static void ExecuteProcedureNonQurey(string storedProcedureName, IDataParameter[] parameters)
    {
        SqlConnection connection = new SqlConnection(sqlconstr);
        SqlCommand command = new SqlCommand(storedProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        connection.Open();
        try
        {
            command.ExecuteNonQuery();
            connection.Close();
        }
        catch (Exception ex)
        {
            errorCollecting.getError(ex);
        }


    }

    /// <summary>
    /// 执行存储，并返回SqlDataReader
    /// </summary>
    /// <param name="storedProcedureName">存储过程名</param>
    /// <param name="parameters">参数</param>
    /// <returns>包含查询结果的SqlDataReader</returns>
    public static SqlDataReader ExecuteProcedureReader(string storedProcedureName, IDataParameter[] parameters)
    {
        SqlConnection connection = new SqlConnection(sqlconstr);
        SqlCommand command = new SqlCommand(storedProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        connection.Open();
        SqlDataReader sqlReader = command.ExecuteReader(CommandBehavior.CloseConnection);
        return sqlReader;
    }

    /// <summary>
    /// 执行存储，并返回DataTable
    /// </summary>
    /// <param name="storedProcedureName">存储过程名</param>
    /// <param name="parameters">参数</param>
    /// <returns>包含查询结果的SqlDataReader</returns>
    public static DataTable ExecuteProcedureDataTable(string storedProcedureName, IDataParameter[] parameters)
    {
        SqlConnection connection = new SqlConnection(sqlconstr);
        SqlCommand command = new SqlCommand(storedProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        connection.Open();


          DataTable dt = new DataTable();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            connection.Close();
        }
        return dt;


    }

//可以尽量避免sqlconnection.open()操作 
    public static DataSet ExecuteProcedureDataset(string storedProcedureName, IDataParameter[] parameters)
    {
        SqlConnection connection = new SqlConnection(sqlconstr);
        SqlCommand command = new SqlCommand(storedProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
        }
        connection.Open();

        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(command);
       
            da.Fill(ds);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            connection.Close();
        }
        return ds;


    }