公司级平台 -接口层IProgram

公司级平台接口层 -IProgram

(IBaseData相关类 )

1、BaseService

 /// <summary>
    /// 基本信息控制器业务逻辑
    /// gcz模式+gc模式。
    /// <remarks>
    /// 注意：不能再次类中写查询SQL语句代码
    /// </remarks>
    /// </summary>
    public class BaseService : IProgram.Service.IBaseEntity
    {
        #region 函数
        public BaseService()
        {

        }
        public BaseService(IBaseData iBase)
        {
            this._iBase = iBase;
        }
        public BaseService(IListData iRepos)
        {
            this._iRepos = iRepos;
        }
        public BaseService(IBaseData iBase,IListData iRepos)
        {
            this._iBase = iBase;
            this._iRepos = iRepos;
        }
        #endregion
        #region 函数
        ~BaseService() 
        {
            if (_iBase != null) 
            {
                if (_iBase._DbHelper != null)
                {
                    _iBase._DbHelper.Connection.Dispose();
                    _iBase._DbHelper.Command.Dispose();
                    _iBase._DbHelper.Dispose();
                }
                //_iBase = null;
            }
            if (_iRepos != null)
            {
                if (_iRepos._DbHelper != null)
                {
                    _iRepos._DbHelper.Connection.Dispose();
                    _iRepos._DbHelper.Command.Dispose();
                    _iRepos._DbHelper.Dispose();
                }
               // _iRepos = null;
            }
        }
        #endregion

        #region private属性
        private IBaseData _iBase;
        private IListData _iRepos;
        #endregion

        #region public属性

        /// <summary>
        /// 数据操作基类
        /// </summary>
        public virtual IBaseData iBase
        {
            get{return _iBase;}
            set { _iBase = value; }
        }
        public virtual IListData iRepos 
        {
            get { return _iRepos; }
            set { _iRepos = value; }
        }
        public string TableName
        {
            get { return ""; }
        }

        public string TableNameSpel
        {
            get { return ""; }
        }
        #endregion

        #region public操作方法
        /// <summary>
        /// 新增前方法
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="objParmas"></param>
        /// <returns></returns>
        public virtual Carpa.Web.Script.IHashObject AddRecord(Carpa.Web.Script.IHashObject entity, Carpa.Web.Script.IHashObject objParmas)
        {
            if (_iBase == null) throw new NullReferenceException("_iBase 数据集为空");
            if (entity == null) return Utils.ReturnObject.GetInstance().GetReturnObject("添加数据结果集为空");
            //判断操作
            IHashObject objReturn = _iBase.GetNewRecord(entity);//_iBase.InsertRecord(entity);
            return objReturn;
        }
        /// <summary>
        /// 修改前方法
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="objParmas"></param>
        /// <returns></returns>
        public virtual Carpa.Web.Script.IHashObject EditRecord(Carpa.Web.Script.IHashObject entity, Carpa.Web.Script.IHashObject objParmas)
        {
            if (_iBase == null) throw new NullReferenceException("_iBase 数据集为空");
            if (entity == null) return Utils.ReturnObject.GetInstance().GetReturnObject("修改数据结果集为空");
            IHashObject objReturn = _iBase.UpdateRecord(entity);
            return objReturn;
        }
        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="sType"></param>
        /// <returns></returns>
        public virtual Carpa.Web.Script.IHashObject SaveRecord(Carpa.Web.Script.IHashObject entity, SaveType sType)
        {
            if (_iBase == null) throw new NullReferenceException("_iBase 数据集为空");
            if (entity == null) return Utils.ReturnObject.GetInstance().GetReturnObject("保存数据结果集为空");
            if (SaveType.NEW == sType) _iBase.InsertRecord(entity);
            else _iBase.UpdateRecord(entity);
            return Utils.ReturnObject.GetInstance().GetReturnObject();
        }
        /// <summary>
        /// 取得当前结果集
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual IHashObject GetRecord(IHashObject RowData)
        {
            if (_iBase == null) throw new NullReferenceException("_iBase 数据集为空");
            IHashObject _rowData = _iBase.GetRecord(RowData);
            return _rowData;
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="objParmas"></param>
        /// <returns></returns>
        public virtual Carpa.Web.Script.IHashObject DeleteRecord(Carpa.Web.Script.IHashObject entity, Carpa.Web.Script.IHashObject objParmas)
        {
            if (_iBase == null) throw new NullReferenceException("_iBase 数据集为空");
            IHashObject objReturn = _iBase.DeleteRecord(entity);
            return objReturn;
        }
        #endregion

        #region public列表方法
        /// <summary>
        /// 取得数据的SQL语句
        /// </summary>
        /// <param name="Params"></param>
        /// <param name="SQLWhere"></param>
        /// <param name="SQLOrder"></param>
        /// <returns></returns>
        public string DBListSql(Carpa.Web.Script.IHashObject Params = null, string SQLWhere = "", string SQLOrder = "")
        {
            if (_iRepos == null) throw new NullReferenceException("IListData 数据集为空");
            return _iRepos.GetListDtSql(Params, SQLWhere, SQLOrder);
        }

        public virtual Carpa.Web.Script.IHashObject Find(Carpa.Web.Script.IHashObject model, Carpa.Web.Script.IHashObject RowData)
        {
            return new HashObject();
        }
        /// <summary>
        /// 查询结果记录
        /// </summary>
        /// <param name="Params"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <param name="SQLWhere"></param>
        /// <param name="SQLOrder"></param>
        /// <returns></returns>
        public virtual System.Data.DataTable FindTable(Carpa.Web.Script.IHashObject Params, int pageIndex, int pageSize, ref int totalRecord, string SQLWhere = "", string SQLOrder = "")
        {
            if (_iRepos == null) throw new NullReferenceException("IListData 数据集为空");
            System.Data.DataTable daTable = _iRepos.GetListDt(Params, pageIndex, pageSize, SQLWhere, SQLOrder);
            if (totalRecord != -1)
            {
                totalRecord = _iRepos.GetListCount(Params, SQLWhere);
            }
            return daTable;
        }

        public IQueryable<Carpa.Web.Script.IHashObject> FindList(Carpa.Web.Script.IHashObject Params, int pageIndex, int pageSize, ref int totalRecord, string SQLWhere = "", string SQLOrder = "")
        {
            throw new NotImplementedException();
        }

        public virtual Carpa.Web.Script.IHashObjectList FindHashList(Carpa.Web.Script.IHashObject Params, int pageIndex, int pageSize, ref int totalRecord, string SQLWhere = "", string SQLOrder = "")
        {
            if (_iRepos == null) throw new NullReferenceException("IListData 数据集为空");
            IHashObjectList list=  _iRepos.GetHashListDt(Params, pageIndex, pageSize, SQLWhere, SQLOrder);
            if (totalRecord != -1)
            {
                totalRecord = _iRepos.GetListCount(Params, SQLWhere);
            }
            return list;
        }
        public virtual Carpa.Web.Script.IHashObjectList FindHashList(DbHelper db, Carpa.Web.Script.IHashObject Params, int pageIndex, int pageSize, ref int totalRecord, string SQLWhere = "", string SQLOrder = "")
        {
            if (_iRepos == null) throw new NullReferenceException("IListData 数据集为空");
            IHashObjectList list = _iRepos.GetHashListDt(db,Params, pageIndex, pageSize, SQLWhere, SQLOrder);
            if (totalRecord != -1)
            {
                totalRecord = _iRepos.GetListCount(db,Params, SQLWhere);
            }
            return list;
        }
        public virtual int Count(Carpa.Web.Script.IHashObject Params = null, string SQLWhere = "")
        {
            if (_iRepos == null) throw new NullReferenceException("IListData 数据集为空");
            IHashObject objRecord = _iRepos.GetListSum(Params, SQLWhere);
            return  objRecord.GetValue<int>("ncount");
        }
        public virtual int Count(DbHelper db , Carpa.Web.Script.IHashObject Params = null, string SQLWhere = "")
        {
            if (_iRepos == null) throw new NullReferenceException("IListData 数据集为空");
            IHashObject objRecord = _iRepos.GetListSum(db, Params, SQLWhere);
            return objRecord.GetValue<int>("ncount");

        }
        public virtual int GetPageRowNumber(Carpa.Web.Script.IHashObject Params = null, string SQLWhere = "", string SQLOrder = "")
        {
            return 0;
        }
        public virtual Carpa.Web.Script.IHashObject ImportRecord(Carpa.Web.Script.IHashObject entity, Carpa.Web.Script.IHashObject objParmas)
        {
            return null;
        }

        public virtual Carpa.Web.Script.IHashObject ExportRecord(Carpa.Web.Script.IHashObject entity, Carpa.Web.Script.IHashObject objParmas)
        {
            return null;
        }

        public virtual Carpa.Web.Script.IHashObject PrintRecord(Carpa.Web.Script.IHashObject entity, Carpa.Web.Script.IHashObject objParmas)
        {
            return null;
        }
        #endregion

        #region private

        #endregion
    }

2、IBaseData

 /// <summary>
    /// 功能：数据操作接口
    /// 数据操作基类
    /// (添加，修改，保存)
    /// 作者：谭斌
    /// 时间：2015年3月26日17:18:29
    /// </summary>
    public interface IBaseData : IDbHelper
    {
        #region 属性
        string TableName { get; }
        string TableNameSpell { get; }
        string GetKeyName { get; }
        //IList<MsSqlColumnInfo> ColumnInfoList { get; }
        #endregion
    
        #region 记录操作
        IHashObject CheckData(IHashObject RowData, string[] ColNames = null, int nType = 0);
        bool CheckRecordExists(IHashObject RowData);
        IHashObject GetNewRecord(IHashObject RowData);
        IHashObject GetRecord(IHashObject RowData);
        IHashObject InsertRecord(IHashObject RowData);
        IHashObject UpdateRecord(IHashObject RowData);
        IHashObject DeleteRecord(IHashObject RowData);
        #endregion

        #region 关联表操作
        IList LineTable_Get();
        IHashObject LineTable_CheckUsed(IHashObject RowData, IList lstLineTable = null);
        IHashObject LineTable_DeleteUsed(IHashObject RowData, IList lstLineTable = null, bool NullableSetNull = false);

        #endregion
    }

3、BaseData

 /// <summary>
    /// 数据库：SQL Server数据库
    /// 功能：数据操作基类
    /// 数据操作基类
    /// (添加，修改，保存)
    /// 作者：谭斌
    /// 时间：2015年3月26日17:18:29
    /// </summary>
    public abstract class BaseData : IBaseData
    {
        #region 定义变量、常量
        public const string _RETURN_INSERTID_KEY = "insertid";
        private string _TableName = "";            //表名
        private IList<MsSqlColumnInfo> _lstCol;  //字段列表
        private string _TableNameSpell = string.Empty;
        private string _BaseKeyName = string.Empty;

        //[NonSerialized]
        protected DbHelper _db;                    //传入的数据库
        protected string _TableCaption = "";       //表说明
        #endregion

        public BaseData() { }
        public BaseData(DbHelper db, string InTableName = "")
        {
            _db = db;
            if (!string.IsNullOrEmpty(InTableName))
                _TableName = InTableName;
        }

        #region 属性
        public virtual string TableName
        {
            get {return _TableName; }
            set { _TableName = value; }
        }
        public DbHelper _DbHelper
        {
            get { return _db; }
            set { _db = value; }
        }
        //表标签
        public virtual string TableCaption
        {
            get
            {
                return !string.IsNullOrEmpty(_TableCaption) ? _TableCaption : TableName;
            }
        }
        public virtual string TableNameSpell
        {
            get
            {
                return "";
            }
            set
            {
                _TableNameSpell = value;
            }
        }
        /// <summary>
        /// 列信息
        /// </summary>
        public  IList<MsSqlColumnInfo> ColumnInfoList
        {
            get
            {
                if (_lstCol == null)
                    _lstCol=IProgram.DataStruct.GetPropertyInfoArray(DataStructModel());
                return _lstCol;
            }
            set { _lstCol = value; }
        }
        public abstract Type DataStructModel();

        /// <summary>
        /// 获取主表字段
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        private IList<MsSqlColumnInfo> CreatTableColumn(string TableName)
        {
            #region 获取表字段
            absDataTable table = new MsSqlDataTable();
            IHashObjectList lst= table.GetColumnInfo(TableName, _db);

            IList<MsSqlColumnInfo> lstCol = new List<MsSqlColumnInfo>();
            foreach (IHashObject objColInfo in lst)
            {
                MsSqlColumnInfo ColInfo = new MsSqlColumnInfo(objColInfo);
                lstCol.Add(ColInfo);
            }
            #endregion
            return lstCol;
        }
        #endregion

        #region 记录集操作
        /// <summary>
        /// 取得单据列表SQL语句
        /// </summary>
        /// <returns></returns>
        private  string GetListDtSql(IHashObject Params = null, string SQLWhere = "", string SQLOrder = "")
        {
            string strSql = "";
            if (!string.IsNullOrEmpty(TableName))
            {
                strSql = string.Format("Select * From `{0}`  {1} ", TableName, TableNameSpell);
                if (!string.IsNullOrEmpty(SQLWhere)) strSql += string.Format(" Where 1=1 {0}", SQLWhere);
                if (!string.IsNullOrEmpty(SQLOrder))
                {
                    if (SQLOrder.Substring(0, 1) == ",") SQLOrder = SQLOrder.Substring(1);
                    strSql = string.Format("SELECT * FROM ({0}) A {1}", strSql, string.Format(" Order By {0} ", SQLOrder));
                }
            }
            return strSql;
        }
        /// <summary>
        /// 取得列表记录
        /// </summary>
        /// <param name="Params">基类未用，用于子类重写</param>
        /// <param name="Start"></param>
        /// <param name="Count"></param>
        /// <param name="SQLWhere"></param>
        /// <param name="SQLOrder"></param>
        /// <returns></returns>
        private  DataTable GetListDt(IHashObject Params = null, int Start = 0, int Count = 0, string SQLWhere = "", string SQLOrder = "")
        {
            DataTable datat = new DataTable();
            string strSql = GetListDtSql(Params, SQLWhere, SQLOrder);
            if (!string.IsNullOrEmpty(strSql))
            {
                Start = Start < 0 ? 0 : Start;
                if (Count > 0)
                    strSql += string.Format(" Limit {0},{1} ", Start, Count);

                datat = _db.ExecuteSQL(strSql);
            }
            return datat;
        }

        /// <summary>
        /// 取得列表总记录数
        /// </summary>
        /// <param name="Start"></param>
        /// <param name="Count"></param>
        /// <param name="SQLWhere"></param>
        /// <param name="SQLOrder"></param>
        /// <returns></returns>
        private  int GetListCount(IHashObject Params = null, string SQLWhere = "")
        {
            int rint = 0;
            if (!string.IsNullOrEmpty(TableName))
            {
                string strSql = string.Format("Select Count(1) as nCount From `{0}` {1} ", TableName, TableNameSpell);
                if (!string.IsNullOrEmpty(SQLWhere)) strSql += string.Format(" Where 1=1 {0}", SQLWhere);
                IHashObject obj = _db.SelectFirstRow(strSql);
                rint = obj.GetValue<int>("ncount");
            }
            return rint;
        }
        /// <summary>
        /// 基本信息定位
        /// </summary>
        /// <param name="Params">定位编号:funkey :funvalue</param>
        /// <param name="SQLWhere"></param>
        /// <param name="SQLOrder"></param>
        /// <returns></returns>
        private  int GetPageRowNumber(IHashObject Params = null, string SQLWhere = "", string SQLOrder = "")
        {
            if (Params == null || Params.Count == 0) return -1;
            try
            {
                _db.AddParameter("_select", GetListDtSql(Params, SQLWhere, SQLOrder));
                _db.AddParameter("_baseID", Params.GetValue<string>("funkey"));
                _db.AddParameter("_BillNo", Params.GetValue<string>("funvalue"));
                _db.AddOutputParameter("@_pageIndex", "");
                IHashObject obj = null;

                obj = _db.ExecuteOutputParameterProcedure("pBillPageIndex") as HashObject;

                if (obj == null || obj.Count == 0) return -1;
                if (string.IsNullOrEmpty(obj.GetValue<string>("@_pageIndex", ""))) return -1;
                return obj.GetValue<int>("@_pageIndex", 0);
            }
            catch (Exception e)
            {
                return 0;
            }
        }
        #endregion

        #region SQL构造相关方法
        /// <summary>
        /// 获取记录主键条件
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual string GetKeyWhereSql(IHashObject RowData)
        {
            IEnumerable<MsSqlColumnInfo> ieKey = ColumnInfoList.Where(c => c.IsKey);
            string[] arrKey = ieKey.Select(c => c.Name).ToArray();
            IHashObject objReturn = CheckData(RowData, arrKey, 1);
            if (!Utils.AppUtils.ReturnObject.CheckReturnObjectIsOk(objReturn))
                throw new Exception(Utils.AppUtils.ReturnObject.GetReturnMessage(objReturn));

            string strWhere = "";
            foreach (MsSqlColumnInfo ColInfo in ieKey)
                strWhere += string.Format(" And {0}={1} ", ColInfo.Name, MsSqlColumnInfo.GetColValueString(ColInfo, RowData.GetValue<object>(ColInfo.Name)));
            return strWhere;
        }
        /// <summary>
        /// 主表主键列
        /// </summary>
        public virtual string GetKeyName
        {
            get
            {
                if (string.IsNullOrEmpty(_BaseKeyName))
                {
                    IEnumerable<MsSqlColumnInfo> ieKey = ColumnInfoList.Where(c => c.IsKey);
                    string[] arrKey = ieKey.Select(c => c.Name).ToArray();
                    if (arrKey.Length > 0)
                        _BaseKeyName = arrKey[0];
                }
                return _BaseKeyName;
            }
        }
        #endregion

        #region 记录数据判断方法
        /// <summary>
        /// 检测记录是否存在
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual bool CheckRecordExists(IHashObject RowData)
        {
            string strWhere = GetKeyWhereSql(RowData);
            string strSql = "Select 1 as cosips From {0} Where 1=1 {1};";
            strSql = string.Format(strSql, TableName, strWhere);

            return _db.SelectFirstRow(strSql) != null;
        }

        /// <summary>
        /// 记录数据检测
        /// </summary>
        /// <param name="RowData"></param>
        /// <param name="ColNames">指定的列名</param>
        /// <param name="nType">指定的列名的操作类型：0:无关；1:检测指定列；2：检测除指定列外的其它列</param>
        /// <returns></returns>
        public virtual IHashObject CheckData(IHashObject RowData, string[] ColNames = null, int nType = 0)
        {
            IHashObject objReturn = Utils.AppUtils.ReturnObject.GetReturnObject();

            #region 循环检测所有表字段
            foreach (MsSqlColumnInfo ColInfo in ColumnInfoList)
            {
                if (ColNames != null)
                {
                    //跳过不检测列
                    if (nType == 1 && ColNames.FirstOrDefault(s => s.ToLower() == ColInfo.Name) == null) continue;
                    if (nType == 2 && ColNames.FirstOrDefault(s => s.ToLower() == ColInfo.Name) != null) continue;
                }

                //检测字段是否可以为空值
                if (!ColInfo.IsNullAble && ColInfo.Default == null && !ColInfo.IsAutoIncrement
                    && RowData.GetValue<object>(ColInfo.Name, null) == null)
                {
                    objReturn = Utils.AppUtils.ReturnObject.GetReturnObject(string.Format("表：{0}[{1}]，字段：{2}[{3}]，不能为空", TableCaption, TableName, ColInfo.Caption, ColInfo.Name));
                    return objReturn;
                }
            }
            #endregion

            #region 数据库重复验证
            if (_db == null) return objReturn;
            var iQuer = ColumnInfoList.Where(s => s.ValidationRepeat).Select(s => s.Name);
            if (iQuer.Count() == 0) return objReturn;
            string selectSql = "SELECT {0} FROM {1} WHERE 1=2 {2} ";
            string strCols = "";
            string where = "";
            foreach (string key in iQuer)
            {
                strCols += string.Format(",{0}", key);
                where += string.Format(" or {0}=@{0}", key);
                _db.AddParameter(key, RowData[key]);
            }
            IHashObject iscount = _db.SelectFirstRow(string.Format(selectSql, strCols.Substring(1),TableName, where));
            if (iscount != null || iscount.Count != 0)
            {
                //ColumnInfoList.Where(s => s.ValidationRepeat).Select(s => s.Caption);
                return Utils.AppUtils.ReturnObject.GetReturnObject("编号重复。");
            }
            #endregion

            return objReturn;
        }
        #endregion

        #region 记录增、删、改操作
        /// <summary>
        /// 新增前操作
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual IHashObject GetNewRecord(IHashObject RowData) { return null; }

        /// <summary>
        /// 重新取得记录
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual IHashObject GetRecord(IHashObject RowData)
        {
            string strKeyWhereSql = GetKeyWhereSql(RowData);
            string strSql = string.Format("Select * From {0} Where 1=1 {1}", TableName, strKeyWhereSql);
            IHashObject billobj = _db.SelectFirstRow(strSql);
            return billobj;
        }

        public virtual IHashObject InsertRecord(IHashObject RowData)
        {
            return InsertRecord(RowData, true);
        }
        /// <summary>
        /// 插入记录
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual IHashObject InsertRecord(IHashObject RowData, bool isInsert)
        {
            //初始返回对象
            IHashObject objReturn = Utils.AppUtils.ReturnObject.GetReturnObject();

            //数据检验
            objReturn = CheckData(RowData);
            if (!Utils.AppUtils.ReturnObject.CheckReturnObjectIsOk(objReturn))
                return objReturn;

            #region 构造插入语句
            string strSql = string.Empty;
            if (isInsert) strSql = "INSERT INTO {0}({1}) VALUES({2});";
            else strSql = "REPLACE INTO {0}({1}) VALUES({2});";

            string strCols = "";
            string strValues = "";
         
            foreach (MsSqlColumnInfo ColInfo in ColumnInfoList)
            {

                if (ColInfo.IsAutoIncrement) continue;                                    //自增列跳过
                if (!RowData.ContainsKey(ColInfo.Name) && ColInfo.IsNullAble) continue;     //未指定值，且可以为空的列跳过
                if (RowData.GetValue<object>(ColInfo.Name) == null && ColInfo.IsNullAble) continue;
                //ColInfo.DataType == "datetime" 
                //if (ColInfo.DataType == "datetime" && string.IsNullOrEmpty(RowData.GetValue<string>(ColInfo.Name))) continue;

                strCols += string.Format(",{0}", ColInfo.Name);
                //strValues += "," + MsSqlColumnInfo.GetColValueString(ColInfo, RowData.GetValue<object>(ColInfo.Name));
                strValues += string.Format(",@{0}");
                _db.AddParameter(ColInfo.Name, RowData.GetValue<object>(ColInfo.Name));
            }
            strCols = strCols.Substring(1);
            strValues = strValues.Substring(1);
            #endregion
            try
            {
                //执行插入SQL语句
                strSql = string.Format(strSql, TableName, strCols, strValues);
                _db.ExecuteNonQuerySQL(strSql);

                //如果主键是自增字段，则要取得新增的Id
                IEnumerable<MsSqlColumnInfo> ieAiKey = ColumnInfoList.Where(c => c.IsKey && c.IsAutoIncrement);
                if (ieAiKey.Count() == 1)
                {
                    //strSql = "Select last_insert_id() as lastid;";
                    strSql = string.Format("SELECT IDENT_CURRENT('{0}') as lastid ", TableName);
                    IHashObject objId = _db.SelectFirstRow(strSql);
                    if (objId != null)
                    {
                        objReturn[_RETURN_INSERTID_KEY] = objId.GetValue<string>("lastid", "");
                        objReturn["returnbaseid"] = objId.GetValue<string>("lastid", "");
                    }
                }
            }
            catch (Exception e)
            {
                return Utils.AppUtils.ReturnObject.GetReturnObject(e.Message);
            }
            //返回对象
            return objReturn;
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual IHashObject UpdateRecord(IHashObject RowData)
        {
            //初始返回对象
            IHashObject objReturn = Utils.AppUtils.ReturnObject.GetReturnObject();

            //修改数据检验 只检查传入数据。
            objReturn = CheckData(RowData, RowData.Keys.ToArray(), 1);
            if (!Utils.AppUtils.ReturnObject.CheckReturnObjectIsOk(objReturn))
                return objReturn;

            //判断原记录是否存在
            if (!CheckRecordExists(RowData))
            {
                objReturn = Utils.AppUtils.ReturnObject.GetReturnObject("记录已不存在，不能修改");
                return objReturn;
            }

            string strSql = "";
            string strWhere = GetKeyWhereSql(RowData);
            string strColValue = "";
            #region 获取所有非KEY字段及值
            IEnumerable<MsSqlColumnInfo> ie = ColumnInfoList.Where(c => !c.IsKey);
            foreach (MsSqlColumnInfo ColInfo in ie)
            {
                if (ColInfo.IsAutoIncrement) continue;                                      //自增列跳过
                if (!RowData.ContainsKey(ColInfo.Name)) continue;                           //未指定值,不进行修改
                //strColValue += string.Format(",{0}={1}", ColInfo.Name, MsSqlColumnInfo.GetColValueString(ColInfo, RowData.GetValue<object>(ColInfo.Name)));
                strColValue += string.Format(",{0}=@{0}", ColInfo.Name);
                _db.AddParameter(ColInfo.Name, MsSqlColumnInfo.GetColValueString(ColInfo, RowData.GetValue<object>(ColInfo.Name)));
            }
            strColValue = strColValue.Substring(1);
            #endregion

            strSql = string.Format("Update {0} Set {1} Where 1=1 {2}", TableName, strColValue, strWhere);
            _db.ExecuteNonQuerySQL(strSql);

            return objReturn;
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="RowData"></param>
        /// <returns></returns>
        public virtual IHashObject DeleteRecord(IHashObject RowData)
        {
            //初始返回对象
            IHashObject objReturn = Utils.AppUtils.ReturnObject.GetReturnObject();

            //判断原记录是否存在
            if (!CheckRecordExists(RowData))
            {
                objReturn = Utils.AppUtils.ReturnObject.GetReturnObject("记录已不存在，不能删除");
                return objReturn;
            }
            string strWhere = GetKeyWhereSql(RowData);
            string strSql = "Delete From {0} Where 1=1 {1};";
            strSql = string.Format(strSql, TableName, strWhere);
            try
            {
                _db.ExecuteNonQuerySQL(strSql);
            }
            catch (Exception e)
            {
                if (e.Message.IndexOf("FOREIGN KEY", StringComparison.CurrentCultureIgnoreCase) >= 0)
                {
                    objReturn = Utils.AppUtils.ReturnObject.GetReturnObject("该信息已经被使用不能删除");
                }
                else
                {
                    objReturn = Utils.AppUtils.ReturnObject.GetReturnObject("删除失败:" + e.Message);
                }
            }
            return objReturn;
        }

        public virtual IHashObject InsertListRecord(IList RowDataList)
        {
            //初始返回对象
            IHashObject objReturn = Utils.AppUtils.ReturnObject.GetReturnObject();

            #region 构造插入语句
            string strSql = "Insert Into {0}({1}) Values({2});";
            string strCols = "";
            string strValues = "";
            foreach (IHashObject RowData in RowDataList)
            {
                foreach (MsSqlColumnInfo ColInfo in ColumnInfoList)
                {

                }
            }
            strCols = strCols.Substring(1);
            strValues = strValues.Substring(1);
            #endregion

            //返回对象
            return objReturn;
        }
        #endregion

        #region 关联表处理

        /// <summary>
        /// 获取唯一主键
        /// </summary>
        /// <returns></returns>
        private MsSqlColumnInfo GetKeyColInfo()
        {
            IEnumerable<MsSqlColumnInfo> ieKey = ColumnInfoList.Where(c => c.IsKey);
            if (ieKey.Count() == 0)
                throw new Exception(string.Format("表[{0}]不含主键，当前功能只能处理唯一主键表", TableCaption));
            if (ieKey.Count() > 1)
                throw new Exception(string.Format("表[{0}]含多字段主键，当前功能只能处理唯一主键表", TableCaption));

            MsSqlColumnInfo KeyColInfo = ieKey.ElementAt(0);
            return KeyColInfo;
        }
        public IList LineTable_Get() 
        {
            return new List<object>();
        }
        /// <summary>
        /// 取得当前表所有的关联表。根据关键字取，关键列有且只有一个，否则抛出异常
        /// </summary>
        /// <returns></returns>
//        public IList LineTable_Get()
//        {
//            MsSqlColumnInfo KeyColInfo = GetKeyColInfo();
//            string strSql = @"
//                    SELECT DISTINCT d.TABLE_NAME AS tablename, d.IS_NULLABLE AS nullable FROM information_schema.COLUMNS d WHERE   d.table_schema=(SELECT DATABASE())
//            AND EXISTS(SELECT 1 FROM information_schema.TABLES t WHERE t.table_schema=(SELECT DATABASE()))
//                    and d.column_name='{1}' 
//                        And d.table_name <> '{0}';";

//            IHashObjectList lst = _db.Select(string.Format(strSql, TableName, KeyColInfo.Name));

//            return lst as IList;
//        }

        /// <summary>
        /// 判断当前记录在其它表中是否使用过，不指定关系表列表，则缺省为检查所有关系表
        /// </summary>
        /// <param name="RowData"></param>
        /// <param name="lstLineTable">传入数据中，包含：tablename、tablecaption、nullable；当本参数为空时，缺省取所有关联表</param>
        /// <returns></returns>
        public IHashObject LineTable_CheckUsed(IHashObject RowData, IList lstLineTable = null)
        {
            IList lst = lstLineTable != null ? lstLineTable : new List<object>(); //LineTable_Get();
            string strSql = "Select 1 From {0} " + string.Format(" Where 1=1 {0} ", GetKeyWhereSql(RowData) + " limit 0,1 ");
            foreach (IHashObject obj in lst)
            {
                if (_db.SelectFirstRow(string.Format(strSql, obj.GetValue<string>("tablename"))) != null)
                {
                    return Utils.AppUtils.ReturnObject.GetReturnObject(
                        string.Format("该记录已经被[{0}]表使用不能删除!",
                        string.IsNullOrEmpty(obj.GetValue<string>("tablename", "")) ? obj.GetValue<string>("tablecaption", "") : obj.GetValue<string>("tablename", "")
                        )
                    );
                }
            }
            return Utils.AppUtils.ReturnObject.GetReturnObject();
        }

        /// <summary>
        /// 删除传入关联表中关联记录，必须指定关系表列表且至少有一记录，否则抛出异常
        /// </summary>
        /// <param name="RowData"></param>
        /// <param name="lstLineTable">传入数据中，包含：tablename、tablecaption、nullable；当本参数为空时或记录为0时，抛出异常</param>
        /// <param name="NullableSetNull">为true时，当关联表中字段允许为空时，不删除记录，仅更新为空值。</param>
        /// <returns></returns>
        public IHashObject LineTable_DeleteUsed(IHashObject RowData, IList lstLineTable = null, bool NullableSetNull = false)
        {
            IHashObject objReturn = Utils.AppUtils.ReturnObject.GetReturnObject();

            MsSqlColumnInfo KeyColInfo = GetKeyColInfo();

            if (string.IsNullOrEmpty(RowData.GetValue<string>(KeyColInfo.Name, "")))
                throw new Exception(string.Format("删除表[{0}]的关联表记录，未提供删除主键值", TableCaption));


            if (lstLineTable == null || lstLineTable.Count == 0)
                throw new Exception(string.Format("删除表[{0}]的关联表记录，未提供删除关联表记录", TableCaption));

            string strDelSql = "Delete From {0} Where {1}={2} ;";
            string strUpSql = "Update {0} Set {1}=null Where {1}={2} ;";
            foreach (IHashObject obj in lstLineTable)
            {
                try
                {
                    string strSql = "";
                    if (obj.GetValue<string>("nullable").ToString().ToLower() == "yes" && NullableSetNull)
                    {
                        //允许为空就更新值为空值
                        strSql = string.Format(strUpSql, obj.GetValue<string>("tablename"), KeyColInfo.Name, MsSqlColumnInfo.GetColValueString(KeyColInfo, RowData.GetValue<object>(KeyColInfo.Name)));
                    }
                    else
                    {
                        //不允许为空就删除记录
                        strSql = string.Format(strDelSql, obj.GetValue<string>("tablename"), KeyColInfo.Name, MsSqlColumnInfo.GetColValueString(KeyColInfo, RowData.GetValue<object>(KeyColInfo.Name)));
                    }
                    _db.ExecuteNonQuerySQL(strSql);
                }
                catch (Exception e)
                {
                    objReturn = Utils.AppUtils.ReturnObject.GetReturnObject(string.Format("删除表[{0}]的关联表[{1}]记录失败，原因是：{1}", TableCaption, string.IsNullOrEmpty(obj.GetValue<string>("tablecaption", "")) ? obj.GetValue<string>("tablename", "") : obj.GetValue<string>("tablecaption", ""), e.Message));
                    return objReturn;
                }
            }
            return objReturn;
        }

        #endregion
    }

4、IDbHelper

 /// <summary>
    /// 功能：数据操作接口
    /// 作者：谭斌
    /// 时间：2015年3月26日17:18:29
    /// </summary>
    public interface IDbHelper
    {
        DbHelper _DbHelper { get; set; }
    }

5、MsSqlDataTable

public abstract class absDataTable 
    {
        public abstract IHashObjectList GetColumnInfo(string TableName, DbHelper _db);
        public abstract IHashObjectList GetPKColumnInfo(string TableName, DbHelper _db);
    }
    public class MsSqlDataTable : absDataTable
    {

        //取得列数据表字段信息
        public override IHashObjectList GetColumnInfo(string TableName, DbHelper _db)
        {
            string strSql = @"select id,lower(c.name) as columnname, c.isnullable as isnullable,c.cdefault,c.xtype,
            d.[definition] as defaultvalue,t.name as data_type,t.length as data_len
             from syscolumns c
            left join [sys].[default_constraints] d on d.object_id = c.cdefault
            left join systypes t on c.xtype=t.xtype
            where exists(select 1 from sysobjects o where  LOWER(o.name) = '{0}' and o.type='U' And c.id=o.id  ) ";
            IHashObjectList lst = _db.Select(string.Format(strSql, TableName));
            //PK
            IHashObjectList iPk = this.GetPKColumnInfo(TableName, _db);
            var iQuer = from l in lst
                        join i in iPk
                        on l.GetValue<string>("id") equals i.GetValue<string>("id")
                        select i;
            foreach (IHashObject row in lst)
            {
                var iPK = iPk.Where(s => s.GetValue<string>("id") == row.GetValue<string>("id"));
                row["column_key"] = (iPK == null || iPK.Count() == 0) ? false : true;
            }
            //自增ID列
            strSql = @"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
                WHERE TABLE_NAME='{0}' AND  COLUMNPROPERTY(      
                OBJECT_ID('{0}'),COLUMN_NAME,'IsIdentity')=1 ";
            strSql = string.Format(strSql, TableName);
            IHashObjectList objrow = _db.Select(strSql);
            foreach (IHashObject row in lst)
            {
                if (objrow == null || objrow.Count == 0) { row["column_isidentity"] = false;continue; }
                var iPK = objrow.Where(s => s.GetValue<string>("column_name").ToUpper() == row.GetValue<string>("columnname").ToUpper());
                row["column_isidentity"] = (iPK == null || iPK.Count() == 0) ? false : true;
            }
            return lst;
        }
        //取得列数据表字段主键
        public override IHashObjectList GetPKColumnInfo(string TableName, DbHelper _db)
        {
            string strSql = @"
             SELECT syscolumns.name  FROM  syscolumns  
             inner JOIN  sysindexkeys  ON   syscolumns.id=sysindexkeys.id  AND  syscolumns.colid=sysindexkeys.colid   
             inner JOIN  sysindexes   ON   syscolumns.id=sysindexes.id  AND  sysindexkeys.indid=sysindexes.indid  
             inner JOIN  sysobjects   ON   sysindexes.name=sysobjects.name  AND  sysobjects.xtype='PK'
             WHERE syscolumns.id=object_id('{0}') ";
            IHashObjectList lst = _db.Select(string.Format(strSql, TableName));
            return lst;
        }
    }
    public class MySqlDataTable : absDataTable
    {
      
        //取得列数据表字段信息
        public override IHashObjectList GetColumnInfo(string TableName, DbHelper _db)
        {
            string strSql = string.Format("Select * From information_schema.COLUMNS Where table_name='{0}' AND table_schema ='{1}' ", TableName, _db.Connection.Database);
            IHashObjectList lst = _db.Select(strSql);
            return lst;
        }
        public override IHashObjectList GetPKColumnInfo(string TableName, DbHelper _db)
        {
            string strSql = string.Format("Select * From information_schema.COLUMNS Where table_name='{0}' AND table_schema ='{1}' ", TableName, _db.Connection.Database);
            IHashObjectList lst = _db.Select(strSql);
            var iQuer = lst.Where(s => s.GetValue<string>("column_key", "").ToLower() == "pri");
            if (iQuer == null || iQuer.Count() == 0) return new HashObjectList();
            return iQuer as IHashObjectList;
        }
    }
    public class OracleDataTable : absDataTable 
    {
        public override IHashObjectList GetColumnInfo(string TableName, DbHelper _db)
        {
            throw new NotImplementedException();
        }

        public override IHashObjectList GetPKColumnInfo(string TableName, DbHelper _db)
        {
            throw new NotImplementedException();
        }
    }

6、MsSqlColumnInfo

 public abstract  class  IColumnInfo
    {
        #region 定义常量，变量
        internal IHashObject _objColumn;//字段对象
        #endregion
        public IColumnInfo() { }
        public IColumnInfo(IHashObject objColumn)
        {
            if (objColumn == null) throw new Exception("数据字段信息为空");
            _objColumn = objColumn;
            if (string.IsNullOrEmpty(Name)) throw new Exception("数据字段名称为空");
        }

        public abstract string Name { get; set; }
        public abstract string DataType { get; set; }
        public abstract bool IsKey { get; set; }
        public abstract bool IsAutoIncrement { get; set; }
        public abstract bool IsNullAble { get; set; }
        public abstract string Default { get; set; }
        public abstract string Caption { get; set; }
        public abstract bool ValidationRepeat { get; set; }

    }

    //public class MySqlColumnInfo : IColumnInfo
    //{ 
    //    #region 定义常量，变量
    //    private string _Caption = "";      //字段标签
    //    #endregion

    //    #region 定义属性
    //    /// <summary>
    //    /// 字段名称
    //    /// </summary>
    //    public override string Name
    //    {
    //        get
    //        {
    //            return _objColumn.GetValue<string>("column_name").ToLower();
    //        }
    //    }

    //    /// <summary>
    //    /// 数据类型
    //    /// </summary>
    //    public override string DataType
    //    {
    //        get
    //        {
    //            return _objColumn.GetValue<string>("data_type").ToLower();
    //        }
    //    }

    //    /// <summary>
    //    /// 是否关键字
    //    /// </summary>
    //    public override bool IsKey
    //    {
    //        get
    //        {
    //            return _objColumn.GetValue<string>("column_key", "").ToLower() == "pri";
    //        }
    //    }

    //    /// <summary>
    //    /// 是否自增字段
    //    /// </summary>
    //    public override bool IsAutoIncrement
    //    {
    //        get
    //        {
    //            return _objColumn.GetValue<string>("extra", "").ToLower() == "auto_increment";
    //        }

    //    }

    //    /// <summary>
    //    /// 字段可否为空
    //    /// </summary>
    //    public override bool IsNullAble
    //    {
    //        get
    //        {
    //            return _objColumn.GetValue<string>("is_nullable").ToString().ToLower() == "yes";
    //        }
    //    }

    //    /// <summary>
    //    /// 缺省值
    //    /// </summary>
    //    public override string Default
    //    {
    //        get
    //        {
    //            return _objColumn.GetValue<object>("column_default") is System.DBNull ? null :
    //            _objColumn.GetValue<string>("column_default", "").Equals("\0") ? "0" : _objColumn.GetValue<string>("column_default", "");
    //        }
    //    }
    //    /// <summary>
    //    /// 字段标签
    //    /// </summary>
    //    public override string Caption
    //    {
    //        get
    //        {
    //            return _Caption;
    //        }
    //    }
    //    #endregion

    //    public MySqlColumnInfo(IHashObject objColumn)
    //        : base(objColumn)

    //    {
    //        if (objColumn == null) throw new Exception("数据字段信息为空");
    //        _objColumn = objColumn;
    //        if (string.IsNullOrEmpty(Name)) throw new Exception("数据字段名称为空");

    //        #region 分解字段说明
    //        //string Col_Comment = _objColumn.GetValue<string>("column_comment");
    //        //string _Cation = Name;
    //        //string[] arrComment = Col_Comment.Split(';');
    //        //if (arrComment != null && arrComment.Length > 0)
    //        //{
    //        //    foreach (string str in arrComment)
    //        //    {
    //        //        if (str.IndexOf("cap:") >= 0)
    //        //        {
    //        //            _Caption = str.Substring(str.IndexOf("cap:") + 4);
    //        //        }
    //        //    }
    //        //}
    //        #endregion
    //    }

    //    /// <summary>
    //    /// 取得字段值
    //    /// </summary>
    //    /// <param name="ColInfo"></param>
    //    /// <param name="Value"></param>
    //    /// <param name="IsNullSetDefault"></param>
    //    /// <returns></returns>
    //    public static string GetColValueString(MySqlColumnInfo ColInfo, object Value, bool IsNullSetDefault = true)
    //    {
    //        bool IsChar = true;//是否可以以字符串类型操作数据
    //        string ReturnValueFormat = GetReturnValueFormat(ColInfo.DataType, out IsChar);
    //        if (Value == null)
    //        {
    //            if (ColInfo.IsNullAble) return "null";
    //            if (ColInfo.Default == null)
    //                throw new Exception(string.Format("字段：{0}[{1}]，不能为空", ColInfo.Caption, ColInfo.Name));
    //            else
    //                Value = ColInfo.Default;
    //        }
    //        //如果是ColInfo数据为整形，传入值为'',就去默认值
    //        if (string.IsNullOrEmpty(Value.ToString()) && !IsChar)
    //        {
    //            Value = ColInfo.Default;
    //            if (ColInfo.Default == null) return "null";
    //        }
    //        if (IsChar && Value != null && Value.ToString().IndexOf("'")>=0) 
    //            Value = Value.ToString().Replace("'", "''");
            
    //        return string.Format(ReturnValueFormat, Value);
    //    }

    //    /// <summary>
    //    /// 根据数据类型
    //    /// </summary>
    //    /// <param name="DataType"></param>
    //    /// <returns></returns>
    //    private static string GetReturnValueFormat(string DataType, out bool ischar)
    //    {
    //        switch (DataType.ToLower())
    //        {
    //            case "varchar":
    //            case "tinytext":
    //            case "text":
    //            case "char":
    //            case "longtext":
    //                ischar = true;
    //                return "'{0}'";

    //            case "datetime":
    //            case "date":
    //            case "time":
    //                ischar = false;
    //                return "'{0}'";
    //            default:
    //                ischar = false;
    //                return "{0}";
    //            //break;
    //        }
    //    }
       
    //}


    public class MsSqlColumnInfo : IColumnInfo
    {
        #region 定义属性
        private string _Caption = "";      //字段标签
        private string _Name =string.Empty;
        private string _DataType = string.Empty;
        private bool _IsKey = false;
        private bool _IsAutoIncrement = false;
        private bool _IsNullAble = true;
        private string _Default = null;
        private bool _ValidationRepeat = false;
        #endregion
        public MsSqlColumnInfo():base() { }
        public MsSqlColumnInfo(IHashObject objColumn)
            : base(objColumn)
        {
            this._Name = _objColumn.GetValue<string>("columnname").ToLower();
            this._DataType = _objColumn.GetValue<string>("data_type").ToLower();
            this._IsKey = _objColumn.GetValue<bool>("column_key", false);
            this._IsAutoIncrement=_objColumn.GetValue<bool>("column_isidentity", false);
            this._IsNullAble=_objColumn.GetValue<string>("isnullable").ToString().ToLower() == "yes";
            this._Default = _objColumn.GetValue<object>("cdefault") is System.DBNull ? null :
                _objColumn.GetValue<string>("cdefault", "").Equals("\0") ? "0" : _objColumn.GetValue<string>("cdefault", ""); ;
        }

        #region 定义属性
        /// <summary>
        /// 字段名称
        /// </summary>
        public override string Name
        {
            get{ return this._Name; }
            set { this._Name = value; }
        }

        /// <summary>
        /// 数据类型
        /// </summary>
        public override string DataType
        {
            get { return this._DataType;}
            set { this._DataType = value; }
        }

        /// <summary>
        /// 是否关键字
        /// </summary>
        public override bool IsKey
        {
            get { return this._IsKey;}
            set { this._IsKey = value; }
        }

        /// <summary>
        /// 是否自增字段
        /// </summary>
        public override bool IsAutoIncrement
        {
            get { return this._IsAutoIncrement; }
            set { this._IsAutoIncrement = value; }
        }

        /// <summary>
        /// 字段可否为空
        /// </summary>
        public override bool IsNullAble
        {
            get { return this._IsNullAble; }
            set { this._IsNullAble = value; }
        }

        /// <summary>
        /// 缺省值
        /// </summary>
        public override string Default
        {
            get {return this._Default; }
            set { this._Default = value; }
        }
        /// <summary>
        /// 字段标签
        /// </summary>
        public override string Caption
        {
            get { return _Caption; }
            set { this._Caption = value; }
        }
        /// <summary>
        /// 字段验证
        /// </summary>
        public override bool ValidationRepeat
        {
            get { return _ValidationRepeat; }
            set { this._ValidationRepeat = value; }
        }
        #endregion

        /// <summary>
        /// 取得字段值
        /// </summary>
        /// <param name="ColInfo"></param>
        /// <param name="Value"></param>
        /// <param name="IsNullSetDefault"></param>
        /// <returns></returns>
        public static string GetColValueString(MsSqlColumnInfo ColInfo, object Value, bool IsNullSetDefault = true)
        {
            bool IsChar = true;//是否可以以字符串类型操作数据
            string ReturnValueFormat = GetReturnValueFormat(ColInfo.DataType, out IsChar);
            if (Value == null)
            {
                if (ColInfo.IsNullAble) return "null";
                if (ColInfo.Default == null)
                    throw new Exception(string.Format("字段：{0}[{1}]，不能为空", ColInfo.Caption, ColInfo.Name));
                else
                    Value = ColInfo.Default;
            }
            //如果是ColInfo数据为整形，传入值为'',就去默认值
            if (string.IsNullOrEmpty(Value.ToString()) && !IsChar)
            {
                Value = ColInfo.Default;
                if (ColInfo.Default == null) return "null";
            }
            if (IsChar && Value != null && Value.ToString().IndexOf("'") >= 0)
                Value = Value.ToString().Replace("'", "''");

            return string.Format(ReturnValueFormat, Value);
        }

        /// <summary>
        /// 根据数据类型
        /// </summary>
        /// <param name="DataType"></param>
        /// <returns></returns>
        private static string GetReturnValueFormat(string DataType, out bool ischar)
        {
            switch (DataType.ToLower())
            {
                case "varchar":
                case "tinytext":
                case "text":
                case "char":
                case "longtext":
                    ischar = true;
                    return "'{0}'";
                case "datetime2":
                case "datetime":
                case "date":
                case "time":
                    ischar = false;
                    return "'{0}'";
                default:
                    ischar = false;
                    return "{0}";
                //break;
            }
        }

        //366ec与net对接 -->PAD-->微信展示-->微商城-->微商城改版豪华版版版版版->租车网-->Carpa改版云商城oto-->微百货+后台管理-->云pos零售-->……
    }

    //public class OracleColumnInfo : IColumnInfo
    //{
    //}

 

 

转载于:https://www.cnblogs.com/tiggerinchengdu/p/4511830.html