公司级平台接口层 
-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" : _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" : _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 //{ //}