MESProductionWeb/AntORM/Ant.Common/DSQL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using D.Constant;

namespace D.Common
{
    public static class DSQL
    {
        #region Methods方法

        private static readonly object _lockObject = new object();

        private static SqlConnection m_conn;

        /// <summary>
        /// 定义数据库连接
        /// </summary>
        /// <returns>数据连接</returns>
        public static SqlConnection Conn()
        {
            if (m_conn.IsNull())
            {
                lock (_lockObject)
                {
                    if (m_conn.IsNull())
                    {
                        try
                        {
                            XmlDocument xmlDoc = new XmlDocument();
                            xmlDoc.Load(D.Constant.DConfig.NHI_CONFIG);
                            string conStr = xmlDoc.DocumentElement.GetElementsByTagName(DString.PROPERTY).Item(2).InnerText;
                            m_conn = new SqlConnection(conStr);
                        }
                        catch (Exception ex)
                        {
                            DLog.WriteLog(DMessage.ERR, ex);
                        }
                    }
                }
            }
            return m_conn;
        }

        #endregion

        #region Methods方法

        /// <summary>
        /// 根据查询语句查询数据
        /// </summary>
        /// <param name="str">查询语句</param>
        /// <returns>数据</returns>
        public static DataSet ExecuteSelectSql(string sql)
        {

            SqlConnection con = Conn();
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            try
            {
                sda.Fill(ds);
            }
            catch (Exception ex)
            {
                DLog.WriteLog(DMessage.ERR, ex);
            }
            finally
            {
                con.Close();
                sda.Dispose();
            }
            return ds;
        }

        /// <summary>
        ///  根据指定条件提取数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="checkName">要检索的字段</param>
        /// <param name="checkValue">检索的值</param>
        /// <returns>值</returns>
        public static DataTable ExecuteSelectSqlTable(string sql)
        {
            DataSet ds = ExecuteSelectSql(sql);
            if (!ds.Tables.Count.Equals(0) && !ds.Tables[0].Rows.Count.Equals(0))
            {
                return ds.Tables[0];
            }
            return new DataTable();
        }

        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="page">页数</param>
        /// <param name="count">数量</param>
        /// <param name="tableName">表名</param>
        /// <returns>数据</returns>
        public static SqlDataAdapter ExecuteSelectSqlPager(string sql)
        {
            SqlConnection con = Conn();
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);
            con.Close();
            return sda;
        }

        /// <summary>
        /// 根据SQL语句更新数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否更新成功</returns>
        public static bool ExcuteData(string sql)
        {
            int action = -1;
            SqlConnection con = Conn();
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                action = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                DLog.WriteLog(DMessage.ERR, ex);
                return false;
            }
            finally
            {
                con.Close();
                cmd.Dispose();
            }
            return !action.Equals(-1);
        }

        /// <summary>
        /// 根据SQL语句更新多条数据
        /// </summary>
        /// <param name="sqls">SQL语句</param>
        /// <returns>是否更新成功</returns>
        public static bool ExcuteDatas(List<String> sqls)
        {
            int action = -1;
            SqlConnection con = Conn();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction tran = null;
            try
            {
                con.Open();
                tran = con.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Connection = con;
                cmd.Transaction = tran;
                foreach (string sql in sqls)
                {
                    cmd.CommandText = sql;
                    action = cmd.ExecuteNonQuery();
                    if (action.Equals(-1))
                    {
                        tran.Rollback();
                        return false;
                    }
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                DLog.WriteLog(DMessage.ERR, ex);
                return false;
            }
            finally
            {
                con.Close();
                cmd.Dispose();
            }
            return true;
        }

        /// <summary>
        /// 删除指定表指定ID的数据
        /// </summary>
        /// <param name="Table">表名</param>
        /// <param name="key">字段名</param>
        /// <param name="value">值</param>
        /// <returns>是否删除成功</returns>
        public static bool DeleteData(string table, string key, string value)
        {
            string sql = String.Format(DString.SQL_DELETE_DATA, table, key, value);
            if (ExcuteData(sql))
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 提取指定表指定字段的数据
        /// </summary>
        /// <param name="keyName">要查询的字段</param>
        /// <param name="tableName">要查询的表</param>
        /// <param name="checkName">要检索的字段</param>
        /// <param name="checkValue">检索的值</param>
        /// <returns>值</returns>
        public static string GetValue(string keyName, string tableName, string checkName, string checkValue)
        {
            string sql = String.Format(DString.SQL_GET_VALUE, keyName, tableName, checkName, checkValue);
            DataSet ds = new DataSet();
            ds = ExecuteSelectSql(sql);
            if (ds.Tables[0].Rows.Count.Equals(0))
            {
                return String.Empty;
            }
            return ds.Tables[0].Rows[0][0].ToString();
        }

        /// <summary>
        ///  根据指定条件提取数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="checkName">要检索的字段</param>
        /// <param name="checkValue">检索的值</param>
        /// <returns>值</returns>
        public static DataTable GetTable(string tableName, string checkName, string checkValue)
        {
            string sql = String.Format(DString.SQL_GET_TABLE, tableName, checkName, checkValue);
            DataSet ds = new DataSet();
            ds = ExecuteSelectSql(sql);
            if (ds.Tables.Count.Equals(0) || ds.Tables[0].Rows.Count.Equals(0))
            {
                return new DataTable();
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="Table">表名</param>
        /// <param name="KeyName">字段名</param>
        /// <param name="Value">值</param>
        /// <param name="Id">ID</param>
        /// <returns>是否更新成功</returns>
        public static bool UpdateData(string Table, string KeyName, string Value, int Id)
        {
            string sql = String.Format(DString.SQL_UPDATE_DATA, Table, KeyName, Value, DateTime.Now, Id);
            if (ExcuteData(sql))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion
    }
}