erp/BizCom/ComBase.cs

using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework;
using NHibernate.Criterion;
using SQLData;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace BizCom
{
    public class ComBase<T> : ActiveRecordBase<T>
    {
        #region 自定义

        private static readonly Dictionary<Type, string> _dicTable = new Dictionary<Type, string>();

        private static object dicTable_Flag = 1;
        /// <summary>
        /// 表名
        /// </summary>
        public string TName
        {
            get { return GetTableName(); }
        }

        public static IDataAccess DbConn
        {
            get { return DbHelper.DbConn; }
        }

        public static DbTrans DbTran
        {
            get { return DbHelper.DbTran; }
        }

        /// <summary>
        /// 返回ProgID
        /// </summary>
        /// <returns>ProgId</returns>
        public virtual string GetTableName()
        {
            Type lType = GetType();
            if (!_dicTable.ContainsKey(lType))
            {
                lock (dicTable_Flag)
                {
                    if (!_dicTable.ContainsKey(lType))
                    {
                        var attribute = GetAttribute<ActiveRecordAttribute>(lType);
                        _dicTable.Add(lType, attribute.Table);
                    }
                }
            }
            return _dicTable[lType];
        }


        private T GetAttribute<T>(Type lType) where T : Attribute
        {
            object[] aArr = lType.GetCustomAttributes(false);
            for (int i = 0; i < aArr.Length; i++)
            {
                if (typeof(T) == aArr[i].GetType())
                    return (T)aArr[i];
            }
            return null;
        }


        /// <summary>
        /// 返回ProgID
        /// </summary>
        /// <returns>ProgId</returns>
        public static string GetViewName()
        {
            string gName = GetName();
            gName = Regex.Replace(gName, "X_", "view_", RegexOptions.IgnoreCase);
            gName = Regex.Replace(gName, "S_", "view_", RegexOptions.IgnoreCase);
            return gName;
        }
        /// <summary>
        /// 返回ProgID
        /// </summary>
        /// <returns>ProgId</returns>
        public static string GetName()
        {
            Type lType = typeof(T);
            if (!_dicTable.ContainsKey(lType))
            {
                lock (dicTable_Flag)
                {
                    if (!_dicTable.ContainsKey(lType))
                    {
                        var attribute = GetAttributeByView<ActiveRecordAttribute>(lType);
                        _dicTable.Add(lType, attribute.Table);
                    }
                }
            }
            return _dicTable[lType];
        }

        private static T GetAttributeByView<T>(Type lType) where T : Attribute
        {
            object[] aArr = lType.GetCustomAttributes(false);
            for (int i = 0; i < aArr.Length; i++)
            {
                if (typeof(T) == aArr[i].GetType())
                    return (T)aArr[i];
            }
            return null;
        }


        #region 单表查询

        public static DataTable SimpleQuery()
        {
            return SimpleQuery(0, "", "", "");
        }

        public static DataTable SimpleQuery(string where)
        {
            return SimpleQuery(0, "", "", where);
        }

        public static DataTable SimpleQuery(int top, string filter, string orderby, string where)
        {
            string topRecord = "";
            if (top > 0) topRecord = " top " + top;
            if (filter == "") filter = "*";
            if (orderby != "") orderby = " order by " + orderby;
            if (where != "") where = " where " + where;
            string sql = string.Format("select {4} {0} from {1} {2} {3}", filter, GetName(), where, orderby, topRecord);
            WirteLog(sql);
            return DbConn.ExecuteDataset(sql).Tables[0];
        }
        #endregion


        public static DataSet MultiQuery(string sql)
        {
            WirteLog(sql);
            return DbConn.ExecuteDataset(sql);
        }


        #region 视图查询
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="top">取记录数</param>
        /// <param name="filter">字段</param>
        /// <param name="orderby">排序</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public static DataTable Query(int top, string filter, string orderby, string where)
        {
            string topRecord = "";
            if (top > 0) topRecord = " top " + top;
            if (filter == "") filter = "*";
            if (orderby != "") orderby = " order by " + orderby;
            if (where != "") where = " where " + where;
            string sql = string.Format("select {4} {0} from {1} {2} {3}", filter, GetViewName(), where, orderby, topRecord);
            WirteLog(sql);
            return DbConn.ExecuteDataset(sql).Tables[0];
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="top">取记录数</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public static DataTable Query(int top, string where)
        {
            return Query(top, "", "", where);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="topRecord">取几条</param>
        /// <returns></returns>
        public static DataTable Query(int topRecord)
        {
            return Query(topRecord, "");
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="orderby"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static DataTable Query(string filter, string orderby, string where)
        {
            return Query(0, filter, orderby, where);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="orderby"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static DataTable Query(string orderby, string where)
        {
            return Query(0, "", orderby, where);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public static DataTable Query(string where)
        {
            return Query(0, where);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public static DataTable Query()
        {
            return Query("");
        }
        #endregion

        /// <summary>
        /// 事务执行
        /// </summary>
        /// <param name="sql">多条语句,单条少用</param>
        public static void TransExecuteNonQuery(string sql)
        {
            WirteLog(sql);
            using (TransactionScope transaction = new TransactionScope())
            {
                try
                {
                    ExecuteNonQuery(sql);
                    transaction.VoteCommit();
                }
                catch (Exception ex)
                {
                    transaction.VoteRollBack();
                    throw;
                }
            }
        }
        #endregion

        public static string CreateCode(string field, string table, string tag)
        {
            DateTime dt = DateTime.Now;
            string prev = dt.ToString("yyMM");
            string sql = string.Format("select isnull(max({1}),'') from {2} where {1} like '{0}%'", tag + prev, field, table);
            object result = ExecuteScalar(sql);
            if (result != null && result.ToString() != "")
            {
                string tmp = result.ToString();
                tmp = tmp.Replace(tag + prev, "");
                int num = Convert.ToInt32(tmp) + 1;
                return tag + prev + num.ToString().PadLeft(4, '0');
            }
            else
            {
                return tag + prev + "0001";
            }
        }

        public static string CreateNoPrevCode(string field, string table, string tag)
        {
            DateTime dt = DateTime.Now;
            string sql = string.Format("select isnull(max({1}),'') from {2} where {1} like '{0}%'", tag, field, table);
            object result = ExecuteScalar(sql);
            if (result != null && result.ToString() != "")
            {
                string tmp = result.ToString();
                tmp = tmp.Replace(tag, "");
                int num = Convert.ToInt32(tmp) + 1;
                return tag + num.ToString().PadLeft(5, '0');
            }
            else
            {
                return tag + "00001";
            }
        }

        public static string AutoCode(string field, string table, string tag, int len)
        {
            DateTime dt = DateTime.Now;
            string sql = string.Format("select isnull(max({1}),'') from {2} where {1} like '{0}%'", tag, field, table);
            object result = ExecuteScalar(sql);
            if (result != null && result.ToString() != "")
            {
                string tmp = result.ToString();
                tmp = tmp.Replace(tag, "");
                int num = Convert.ToInt32(tmp) + 1;
                return tag + num.ToString().PadLeft(len, '0');
            }
            else
            {
                return tag + "1".PadLeft(len, '0');
            }
        }

        public static object genCode = new object();
        public static string GetGenCode(string tag, string prefix, int len)
        {
            SqlParameter[] sqlParameter ={
                        new SqlParameter("@tag", SqlDbType.VarChar,20),
                        new SqlParameter("@prefix", SqlDbType.VarChar, 20),
                        new SqlParameter("@codelen", SqlDbType.Int, 4),
                        new SqlParameter("@otag", SqlDbType.VarChar, 50)};

            sqlParameter[0].Value = tag;
            sqlParameter[1].Value = prefix;
            sqlParameter[2].Value = len;
            sqlParameter[3].Direction = ParameterDirection.Output;
            lock (genCode)
            {
                ExecuteNonQueryStore("sp_getgencode", sqlParameter);
                return sqlParameter[3].Value.ToString();
            }
        }

        public static object genCode2 = new object();
        public static string GetGenCode2(string tag, string prefix, int len)
        {
            SqlParameter[] sqlParameter ={
                        new SqlParameter("@tag", SqlDbType.VarChar,20),
                        new SqlParameter("@prefix", SqlDbType.VarChar, 20),
                        new SqlParameter("@codelen", SqlDbType.Int, 4),
                        new SqlParameter("@otag", SqlDbType.VarChar, 50)};

            sqlParameter[0].Value = tag;
            sqlParameter[1].Value = prefix;
            sqlParameter[2].Value = len;
            sqlParameter[3].Direction = ParameterDirection.Output;
            lock (genCode2)
            {
                ExecuteNonQueryStore("sp_getgencode2", sqlParameter);
                return sqlParameter[3].Value.ToString();
            }
        }

        public static string GetHid(string tag, string prefix)
        {
            SqlParameter[] sqlParameter ={
                        new SqlParameter("@compid", SqlDbType.NVarChar,10),
                        new SqlParameter("@ls_hid", SqlDbType.NVarChar, 10),
                        new SqlParameter("@indate", SqlDbType.NVarChar, 10),
                        new SqlParameter("@inupd", SqlDbType.NVarChar, 10),
                        new SqlParameter("@ls_rhid", SqlDbType.VarChar, 20)};

            sqlParameter[0].Value = "01";
            sqlParameter[1].Value = tag;
            sqlParameter[2].Value = prefix;
            sqlParameter[3].Value = "yes";
            sqlParameter[4].Direction = ParameterDirection.Output;
            ExecuteNonQueryStore("gf_GetHid", sqlParameter);

            return sqlParameter[4].Value.ToString();
        }


        public static T Get(object id)
        {
            return FindFirst(Expression.Eq("ID", Convert.ToInt32(id)));
        }



        public static DataSet ExecuteDataset(string sql)
        {
            WirteLog(sql);
            ISessionFactoryHolder sessionFactoryHolder = ActiveRecordMediator.GetSessionFactoryHolder();
            using (NHibernate.ISession session = sessionFactoryHolder.CreateSession(typeof(T)))
            {
                IDbCommand command = session.Connection.CreateCommand();
                command.CommandText = sql;
                if (session.Transaction.IsActive) session.Transaction.Enlist(command);
                IDbDataAdapter da = new SqlDataAdapter(command as SqlCommand);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            //return command.ExecuteNonQuery();
        }

        public static int ExecuteNonQuery(string sql)
        {
            WirteLog(sql);
            ISessionFactoryHolder sessionFactoryHolder = ActiveRecordMediator.GetSessionFactoryHolder();
            using (NHibernate.ISession session = sessionFactoryHolder.CreateSession(typeof(T)))
            {
                IDbCommand command = session.Connection.CreateCommand();
                command.CommandText = sql;
                if (session.Transaction.IsActive) session.Transaction.Enlist(command);
                return command.ExecuteNonQuery();
            }

        }

        public static DataSet ExecuteDataSetStore(string sql, SqlParameter[] paras)
        {
            WirteLog(sql);
            ISessionFactoryHolder sessionFactoryHolder = ActiveRecordMediator.GetSessionFactoryHolder();
            using (NHibernate.ISession session = sessionFactoryHolder.CreateSession(typeof(T)))
            {
                IDbCommand command = session.Connection.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                if (paras != null)
                {
                    for (int j = 0; j < paras.Length; j++)
                    {
                        command.Parameters.Add(paras[j]);
                    }
                }
                command.CommandText = sql;
                if (session.Transaction.IsActive) session.Transaction.Enlist(command);
                IDbDataAdapter da = new SqlDataAdapter(command as SqlCommand);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        }

        public static int ExecuteNonQueryStore(string sql, SqlParameter[] paras)
        {
            WirteLog(sql);
            ISessionFactoryHolder sessionFactoryHolder = ActiveRecordMediator.GetSessionFactoryHolder();
            using (NHibernate.ISession session = sessionFactoryHolder.CreateSession(typeof(T)))
            {
                IDbCommand command = session.Connection.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                //IDataParameterCollection
                if (paras != null)
                {
                    for (int j = 0; j < paras.Length; j++)
                    {
                        command.Parameters.Add(paras[j]);
                    }
                }
                command.CommandText = sql;
                if (session.Transaction.IsActive) session.Transaction.Enlist(command);
                return command.ExecuteNonQuery();
            }
        }

        public static object ExecuteScalar(string sql)
        {
            WirteLog(sql);
            ISessionFactoryHolder sessionFactoryHolder = ActiveRecordMediator.GetSessionFactoryHolder();
            //NHibernate.ISession session = sessionFactoryHolder.GetSessionFactory(typeof(CeErpInventory)).GetCurrentSession();
            //NHibernate.ISession  session=sessionFactoryHolder.GetConfiguration(typeof(ActiveRecordBase)).BuildSessionFactory().OpenSession();
            using (NHibernate.ISession session = sessionFactoryHolder.CreateSession(typeof(T)))
            {
                IDbCommand command = session.Connection.CreateCommand();
                command.CommandText = sql;
                if (session.Transaction.IsActive) session.Transaction.Enlist(command);
                return command.ExecuteScalar();
            }
        }


        static log4net.ILog logger = log4net.LogManager.GetLogger("ComBase");//获取一个日志记录器
        private static void WirteLog(string text)
        {
            logger.Info(text);
        }
    }
}