架构B/S四 DBUtility 数据层基类 放公共类(一) SqlHelper

放公共类

如:SQLHelper.cs数据访问抽象基础类    ControlBindHelper.cs 控件帮定抽象基础类 DataBase.cs 操作控件类 MessageBox.cs 显示消息提示对话框类。 PageValidate.cs 页面数据校验类  

SQLHelper.cs数据访问抽象基础类 代码

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using CoalTraffic.Common;

namespace CoalTraffic.DBUtility
{
    public class CommandInfo
    {
        public string CommandText;
        public SqlParameter[] Parameters;
    }
/// <summary>
/// 数据访问抽象基础类
    /// Copyright (C) 2004-2008 By

/// </summary>
public abstract class DbHelperSQL
{
        //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. 

        public static string connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
   public DbHelperSQL()
   {
        }

        #region 公用方法

        public static int GetMaxID(string FieldName,string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = DbHelperSQL.GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public static decimal GetMaxIDNum(string FieldName, string TableName)
        {
            string strsql = "select max(cast(" + FieldName + " as decimal(22)))+1 from " + TableName;
            object obj = DbHelperSQL.GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return decimal.Parse(obj.ToString());
            }
        }

        /// <summary>
        /// 跟据当前ID，取得它的下一个ID
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strIDKey">主健名</param>
        /// <param name="strWhere">Where条件</param>
        /// <param name="strCurrentID">当前ID</param>
        /// <returns>下一个ID</returns>
        public static string GetNextID(string strTableName,string strIDKey,string strWhere,string strCurrentID)
        {
            string strNextID = string.Empty;
            string strMinID = string.Empty;
            string strMaxID = string.Empty;
            if (strWhere == "")
            {
                string strSql = "Select Top 1 " + strIDKey + " From " + strTableName + " Order By " + strIDKey + " Asc";

                object objSaclar = DbHelperSQL.GetSingle(strSql);

                if (objSaclar != null && objSaclar.ToString() != "")
                {
                    strMinID = objSaclar.ToString();
                }

                strSql = "Select Top 1 " + strIDKey + " From " + strTableName + " Order By " + strIDKey + " Desc";

                objSaclar = DbHelperSQL.GetSingle(strSql);

                if (objSaclar != null && objSaclar.ToString() != "")
                {
                    strMaxID = objSaclar.ToString();
                }
            }
            else
            {
                string strSql = "Select Top 1 " + strIDKey + " From " + strTableName + " Where " + strWhere +" Order By " + strIDKey + " Asc";

                object objSaclar = DbHelperSQL.GetSingle(strSql);

                if (objSaclar != null && objSaclar.ToString() != "")
                {
                    strMinID = objSaclar.ToString();
                }

                strSql = "Select Top 1 " + strIDKey + " From " + strTableName + " Where " + strWhere + " Order By " + strIDKey + " Desc";

                objSaclar = DbHelperSQL.GetSingle(strSql);

                if (objSaclar != null && objSaclar.ToString() != "")
                {
                    strMaxID = objSaclar.ToString();
                }
            }

            if (strMinID == strMaxID)
            {
                strNextID = strMinID;
            }
            else
            {
                if (PageValidate.IsNumber(strMinID) && PageValidate.IsNumber(strMaxID) && PageValidate.IsNumber(strCurrentID))
                {
                    if ((Convert.ToInt32(strCurrentID) >= Convert.ToInt32(strMinID)) && (Convert.ToInt32(strCurrentID) < Convert.ToInt32(strMaxID)))
                    {
                        strNextID = (Convert.ToInt32(strCurrentID) + 1).ToString();
                    }
                    else if ((Convert.ToInt32(strCurrentID) == Convert.ToInt32(strMaxID)))
                    {
                        strNextID = strMinID;
                    }
                    else
                    {
                        strNextID = strCurrentID; //当前值比最大值大或比最小值小的情况
                    }

                }
                else if ((!PageValidate.IsNumber(strMinID)) && (!PageValidate.IsNumber(strMaxID)) && (!PageValidate.IsNumber(strCurrentID)) && (strMinID.Length > 1) && (strMaxID.Length > 1) && (strCurrentID.Length > 1))
                {
                    int iMinID = Convert.ToInt32(strMinID.Substring(1));
                    int iMaxID = Convert.ToInt32(strMaxID.Substring(1));
                    int iCurrentID = Convert.ToInt32(strCurrentID.Substring(1));
                    int iNextID = int.MinValue;

                    if ((iCurrentID >= iMinID) && (iCurrentID < iMaxID))
                    {
                        iNextID = iCurrentID + 1;
                    }
                    else if (iCurrentID == iMaxID)
                    {
                        iNextID = iMinID;
                    }
                    else
                    {
                        strNextID = strCurrentID; //当前值比最大值大或比最小值小的情况
                    }

                    if (iNextID != int.MinValue)
                    {
                        strNextID = strCurrentID.Substring(0, 1) + iNextID.ToString().PadLeft(strCurrentID.Length-1,'0') ;
                    }
                }
            }

            return strNextID;
        }
   public static bool Exists(string strSql)
   {
    object obj = DbHelperSQL.GetSingle(strSql);
    int cmdresult;
    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    {
     cmdresult = 0;
    }
    else
    {
     cmdresult = int.Parse(obj.ToString());
    }
    if (cmdresult == 0)
    {
     return false;
    }
    else
    {
     return true;
    }
   }
        public static bool Forbid(string strTableName, string strPrimaryKey, string strForbidField,string strPKValue,string strIsForbid)
        {
            string strSql = "Update " + strTableName + " Set " + strForbidField + "='" + strIsForbid + "' Where " + strPrimaryKey + "='" + strPKValue + "'";

            int iAffected = DbHelperSQL.ExecuteSql(strSql);

            if (iAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static bool Forbid(string strTableName, string strPrimaryKey_strIsForbid, string strForbidField_strPKValue )
        {
            string strSql = "Update " + strTableName + " Set " + strForbidField_strPKValue + " Where " + strPrimaryKey_strIsForbid + "";

            int iAffected = DbHelperSQL.ExecuteSql(strSql);

            if (iAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
   /// <summary>
   /// 表是否存在
   /// </summary>
   /// <param name="TableName"></param>
   /// <returns></returns>
   public static bool TabExists(string TableName)
   {
    string strsql="select count(*) from sysobjects where id = object_id(N'["+TableName+"]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
    object obj=DbHelperSQL.GetSingle(strsql);
    int cmdresult;
    if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
    {
     cmdresult=0;
    }
    else
    {
     cmdresult=int.Parse(obj.ToString());
    }
    if(cmdresult==0)
    {
     return false;
    }
    else
    {
     return true;
    }
   }
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 取得某一项的值
        /// </summary>
        /// <param name="strTable">表名</param>
        /// <param name="strField">字段名</param>
        /// <param name="strFiledKey">查询字段</param>
        /// <param name="strValue">匹配查询字段的值</param>
        /// <returns></returns>
        public static string GetFiledValue(string strTable, string strField, string strFiledKey, string strValue)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            string sSQL = "SELECT " + strField + " FROM " + strTable + " where " + strFiledKey + "='" + strValue + "'";
            try
            {
                connection.Open();
                SqlCommand comm = new SqlCommand(sSQL, connection);
                string strvalue = comm.ExecuteScalar().ToString();
                return strvalue;
            }
            catch
            {
                connection.Close();
                return "";
            }
        }

        public static Object ExecuteScalar(string sSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                SqlCommand comm = new SqlCommand(sSQL, connection);
                return comm.ExecuteScalar();
            }
            catch
            {
                connection.Close();
                return null;
            }
        }

        #endregion

        #region 执行简单SQL语句

        /// <summary>
   /// 执行SQL语句，返回影响的记录数
   /// </summary>
   /// <param name="SQLString">SQL语句</param>
   /// <returns>影响的记录数</returns>
   public static int ExecuteSql(string SQLString)
   {
    using (SqlConnection connection = new SqlConnection(connectionString))
    {    
     using (SqlCommand cmd = new SqlCommand(SQLString,connection))
     {
      try
      {  
       connection.Open();
       int rows=cmd.ExecuteNonQuery();
       return rows;
      }
      catch(System.Data.SqlClient.SqlException E)
      {     
       connection.Close();
       throw new Exception(E.Message);
      }
     }    
    }
   }

   public static int ExecuteSqlByTime(string SQLString,int Times)
   {
    using (SqlConnection connection = new SqlConnection(connectionString))
    {    
     using (SqlCommand cmd = new SqlCommand(SQLString,connection))
     {
      try
      {  
       connection.Open();
       cmd.CommandTimeout=Times;
       int rows=cmd.ExecuteNonQuery();
       return rows;
      }
      catch(System.Data.SqlClient.SqlException E)
      {     
       connection.Close();
       throw new Exception(E.Message);
      }
     }    
    }
   }

        /// <summary>
        /// 执行多条SQL语句,返回是否执行成功
        /// </summary>
        /// <param name="SqlList">Sql语句列表</param>
        /// <param name="Prams">参数列表</param>
        /// <returns>返回是否执行成功</returns>
        public static bool ExecuteSqlListTranc(List<string> SqlList, List<SqlParameter[]> Prams)
        {
            bool revalue = false;
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.Transaction = trans;
            try
            {
                for (int i = 0; i < SqlList.Count; i++)
                {
                    comm.CommandText = SqlList[i].ToString();
                    comm.Parameters.Clear();
                    if (Prams != null)
                    {
                        if (Prams[i] != null)
                        {
                            foreach (SqlParameter parm in Prams[i])
                                comm.Parameters.Add(parm);
                        }
                    }
                    comm.ExecuteNonQuery();
                }
                trans.Commit();
                revalue = true;
            }
            catch
            {
                trans.Rollback();
                revalue = false;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return revalue;
        }