dt_data/ECDeveloper/Template/Code/WebBase/SqlHelper.cs

///<summary>
///模块名：	DbOra
///    <remarks>
///       功能描述：
///       1）提供数据连接的接口
///    </remarks>
///    <remarks>
///        作者：	 黄文发
///        日期：	2003-09-09
///    </remarks>
///</summary>
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;

namespace System.Data
{
    /// <summary>
    ///   数据库连接字符串用Base64加密在
    ///    App.Config/Web.Config中的
    ///        <appSettings> 
    ///            <add key="SERVER" value="UHBhbXM=" />
    ///        　　<add key="DATABASE" value="UHBhbXM=" />
    ///        　　<add key="USERNAME" value="UHBhbXMy" />
    ///        　　<add key="PASSWD" value="UHBhbXMy" />
    ///        </appSettings>
    /// </summary>
    public class SqlHelper
    {
        private static string constr = null;
        public static string ConnectString
        {
            get
            {
                if (constr == null)
                    constr = GetConnectStringFromConfig("SERVER", "DATABASE", "USERNAME", "PASSWD");
                return constr;
            }
        }

        /// <summary>
        /// 从配置文件中获取数据库连接字符串
        /// </summary>
        /// <param name="cfDataBase"></param>
        /// <param name="cfUserName"></param>
        /// <param name="cfPasswd"></param>
        /// <returns></returns>
        public static string GetConnectStringFromConfig(string cfServer, string cfDataBase, string cfUserName, string cfPasswd)
        {
            string res;
            try
            {
                string server = ConfigurationSettings.AppSettings[cfServer];
                string dataBase = ConfigurationSettings.AppSettings[cfDataBase];
                string userName = ConfigurationSettings.AppSettings[cfUserName];
                string passwd = ConfigurationSettings.AppSettings[cfPasswd];


                if (dataBase == null || dataBase == "")
                    throw new Exception("未指定数据库!");

                res = "Password=" + passwd + ";Persist Security Info=True;User ID="
                    + userName + ";Initial Catalog=" + dataBase + ";max pool size=3000;Data Source=" + server;
            }
            catch (Exception ex)
            {
                throw new Exception("连接字符串错误" + ex.Message);
            }
            return res;
        }


        public SqlHelper()
        {
        }


        public static bool HasRow(DataSet ds)
        {
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return false;
            return true;
        }

        #region ExecSql
        public static int ExecSql(string sql)
        {
            int res = 0;
            SqlConnection con = new SqlConnection(ConnectString);
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            res = cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            return res;
        }

        public static int ExecSql(ArrayList sqls)
        {
            int res = 0;
            SqlConnection con = null;
            SqlTransaction tran = null;
            try
            {
                con = new SqlConnection(ConnectString);
                con.Open();
                tran = con.BeginTransaction();


                foreach (string sql in sqls)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.Transaction = tran;
                    res += cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                res = -1;
                tran.Rollback();
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                if (tran != null)
                    tran.Dispose();
            }
            return res;
        }

        #endregion

        #region Select
        public static SqlDataReader SelectReader(string sql)
        {
            SqlConnection con = new SqlConnection(ConnectString);
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        } 
        public static DataSet Select(string sql, int pageSize, int pageIndex, bool countInfo, ref int allCount)
        {
            SqlDataAdapter ad = null;
            DataSet ds = new DataSet();
            allCount = 0;
            try
            {
                ad = new SqlDataAdapter(sql, ConnectString);
                if ((pageSize == -1) && (pageSize == -1))
                    ad.Fill(ds, "TableName1");
                else
                {
                    int start = pageSize * (pageIndex - 1);
                    if (start < 0) start = 0;
                    ad.Fill(ds, start, pageSize, "TableName");
                    if (countInfo)
                    {
                        ad = new SqlDataAdapter(GetCountSqlString(sql), ConnectString);
                        DataSet dsCount = new DataSet();
                        ad.Fill(dsCount, "TableCount");
                        if (HasRow(dsCount))
                            allCount = Convert.ToInt32(dsCount.Tables[0].Rows[0][0]);
                    }
                }
            }
            catch (Exception e)
            {
                allCount = 0;
                ds = null;
                throw e;
            }
            finally
            {
                if (ad != null)
                {
                    ad.Dispose();
                    ad = null;
                }
            }
            return ds;
        }

        public static DataSet Select(string sql, int pageSize, int pageIndex, ref int allCount)
        {
            return Select(sql, pageSize, pageIndex, true, ref allCount);
        }

        public static DataSet Select(string sql, int pageSize, int pageIndex)
        {
            int i = 0;
            return Select(sql, pageSize, pageIndex, false, ref i);
        }

        public static DataSet Select(string sql)
        {
            return Select(sql, -1, -1);
        }
        public static DataSet ExecuteProd(string prodName, ArrayList objParams)
        {

            SqlConnection con = null;
            try
            {
                con = new SqlConnection(ConnectString);
                SqlCommand oc = new SqlCommand(prodName, con);
                oc.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter sp in objParams)
                    oc.Parameters.Add(sp);
                con.Open();
                SqlDataAdapter adp = new SqlDataAdapter(oc);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
#if DEBUG
                string str = ex.Message;
#endif
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        #endregion

        private static string GetCountSqlString(string sql)
        {
            string res = "";
            try
            {
                string sqlTemp = sql.ToUpper();
                int iFrom = sqlTemp.IndexOf("FROM");
                if (iFrom > 0)
                {
                    int iOrder = sqlTemp.IndexOf("ORDER BY");
                    if (iOrder == -1)
                        iOrder = sql.Length;

                    res = "SELECT COUNT(*) AS NUM FROM " + sql.Substring(iFrom + 4, iOrder - iFrom - 4);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static string String(string str)
        {
            if (str != null)
                return str.Replace("'", "＇");
            else
                return "";
        }

        /// <summary>
        /// 1900-1-1
        /// </summary>
        public static DateTime MinDate
        {
            get { return new DateTime(1900, 1, 1); }
        }
        /// <summary>
        /// 将字符串转化成日期，如果输入的字符串不是日期时，则返回1900-1-1
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static DateTime Date(string str)
        {
            if (str == null || str == "")
                return MinDate;
            else
            {
                DateTime dt = MinDate;
                if (DateTime.TryParse(str, out dt) == false)
                    return MinDate;
                else
                    return dt;
            }
        }
        public static string String(DateTime dt)
        {
            if (dt == (new DateTime(1,1,1)) || dt == MinDate)
                return "";
            else
                return dt.ToShortDateString();
        }
    }

}