/// <summary> /// ����condition��value���ֵ���£�condition�����в�Ϊnull���ֶζ�����Ϊ��ѯ���� /// ��value�е����в�Ϊnull���ֶν��ᱻ���� /// ֧��ģ����ѯ /// </summary> /// <param name="value">��Ҫ���µ�ֵ</param> /// <param name="condition">����ʱ������</param> /// <returns>Ӱ��ļ�¼��</returns> public virtual int UpdateWithModel(Model value, Model condition) { this.setSystemField(value); ParmCollection conditionParms = this.Table.PrepareUpdateParms(value, this.IsLikeMode, condition); return(this.Internal_DataHelper.ExecuteNonQuery(string.Format(conditionParms.UpdateSql, conditionParms.WhereSql), conditionParms)); }
/// <summary> /// ����condition��value���ֵ���£�condition�����в�Ϊnull���ֶζ�����Ϊ��ѯ���� /// ��value�е����в�Ϊnull���ֶν��ᱻ���� /// ֧��ģ����ѯ /// </summary> /// <param name="value">��Ҫ���µ�ֵ</param> /// <param name="condition">����ʱ������</param> /// <returns>���º��ֵ����</returns> public virtual IEnumerable <OutputModel> UpdateWithModelAndReturn(Model value, Model condition) { this.setSystemField(value); ParmCollection conditionParms = this.Table.PrepareUpdateParms(value, this.IsLikeMode, condition); return(this.List(string.Format(conditionParms.UpdateSql, conditionParms.WhereSql), conditionParms).ToList()); }
/// <summary> /// 根据具体Model对象生成Update参数及子句 /// </summary> /// <param name="value">更新Model对象,用于生成Update部分,属性为null值的不作为生成条件</param> /// <param name="condition">条件Model对象,用于生成Where部分,属性为null值的不作为生成条件</param> /// <param name="isLikeMode">是否模糊查询</param> /// <returns>Update参数及子句</returns> public virtual ParmCollection PrepareUpdateParms(object value, bool isLikeMode = false, object condition = null) { ParmCollection temp = new ParmCollection(); string update = string.Empty; foreach (IDataParameter p in this.Parameters) { foreach (DBFieldInfoAttribute f in this.Fields) { if (f.ColumnName == p.SourceColumn && f.Field.GetValue(value) != null && f.SqlDbType != SqlDbType.Timestamp) { p.Value = f.Field.GetValue(value); update += string.Format("{0}={1}U, ", f.ColumnNameFix, f.ParameterName); IDataParameter pp = this.CloneParameter(p); pp.ParameterName = f.ParameterName + "U"; temp.Add(pp); break; } } } if (update.Length == 0) { return(temp); } ParmCollection where = this.PrepareConditionParms(condition, isLikeMode); update = string.Format("UPDATE {0} SET {1} OUTPUT INSERTED.* {2}", this.TableName, update.Substring(0, update.Length - 2), "{0}"); where.UpdateSql = update; where.AddRange(temp); return(where); }
/// <summary> /// 根据具体Model对象获取非自动增量的参数集合 /// </summary> /// <param name="o">Model对象</param> /// <returns>非自动增量的参数集合</returns> public virtual ParmCollection PrepareNotIdentityParms(object o) { ParmCollection temp = new ParmCollection(); foreach (IDataParameter p in this.Parameters) { foreach (DBFieldInfoAttribute f in this.Fields) { if (!f.IsIdentity && f.ColumnName == p.SourceColumn) { object v = f.Field.GetValue(o); if (f.IsKey && v == null) { v = Guid.NewGuid(); } if (f.DefaultValue != null) { if (f.DefaultValue.GetType().IsEnum) { f.DefaultValue = (int)f.DefaultValue; } } p.Value = v == null ? (f.DefaultValue == null ? DBNull.Value : f.DefaultValue) : v; p.DbType = v == null ? (f.DefaultValue == null ? p.DbType : DbType.String) : p.DbType; temp.Add(p); break; } } } return(temp); }
/// <summary> /// ���������ֶ�ֵ���£�model��������κ�ֵ��������Ϊ�������� /// ��model�г��Զ������ֶ���������в�Ϊnull���ֶν��ᱻ���� /// </summary> /// <param name="model">Ҫ���µ�ֵ</param> /// <returns>Ӱ��ļ�¼��</returns> public virtual OutputModel UpdateWithIdentityAndReturn(Model model) { this.setSystemField(model); ParmCollection conditionParms = this.Table.PrepareUpdateParms(model); conditionParms.AddRange(this.Table.PrepareAllParms(model)); return(this.Select(string.Format(conditionParms.UpdateSql, this.Table.IdentityParm.WhereSql), conditionParms)); }
/// <summary> /// ����model��ֵ��ѯ��model��ÿһ����null�ֶζ�����Ϊ��ѯ���� /// ������ѯ���м�¼����modelΪnull���� /// ֧��ģ����ѯ /// </summary> /// <param name="model">��Ϊ��ѯ������model</param> /// <returns>���ϲ�ѯ���������ݱ�</returns> public virtual DataTable ListTable(Model model) { ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode); DataTable dt = ListTable( string.Format(this.Table.Select, parms.WhereSql, this.OrderBy) , parms); return(dt); }
/// <summary> /// ��model��ij��˱�ʶΪIdentity,Timestamp֮�������ֵ�������ݿ� /// </summary> /// <param name="model">��Ҫ�������ݿ��model</param> /// <returns>Ӱ��ļ�¼��</returns> public virtual int Insert(Model model) { model.GmtCreateUser = this.Operater; model.GmtCreateDate = DateTime.Now; model.GmtUpdateUser = this.Operater; model.GmtUpdateDate = DateTime.Now; ParmCollection parms = this.Table.PrepareNotIdentityParms(model); return(Internal_DataHelper.ExecuteNonQuery(this.Table.Insert, parms)); }
/// <summary> /// ����model��ֵ��Ϊ��ѯ����,model��ÿһ����null�ֶζ�����Ϊ��ѯ������ /// ��ȡModel��Ӧ�����ݿ���ڷ��������ļ�¼���ϼ� /// ֧��ģ����ѯ /// </summary> /// <param name="model">��Ϊ������model</param> /// <returns>��¼���ϼ�</returns> public virtual int GetCount(Model model) { ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode); object o = Internal_DataHelper.ExecuteScalar(CommandType.Text, string.Format("SELECT COUNT(*) FROM {0} {1}", this.Table.TableName, parms.WhereSql), parms); if (o != null) { return(int.Parse(o.ToString())); } return(0); }
/// <summary> /// ��model��ij��˱�ʶΪIdentity,Timestamp֮�������ֵ�������ݿ� /// </summary> /// <param name="model">��Ҫ�������ݿ��model</param> /// <returns>�����Ľ��</returns> public virtual OutputModel InsertAndReturn(Model model) { model.GmtCreateUser = this.Operater; model.GmtCreateDate = DateTime.Now; model.GmtUpdateUser = this.Operater; model.GmtUpdateDate = DateTime.Now; ParmCollection parms = this.Table.PrepareNotIdentityParms(model); using (IDataReader rdr = Internal_DataHelper.ExecuteReader(this.Table.Insert, parms)) { return(rdr.Read() ? (OutputModel)this.Table.ReadDataReader(rdr, new OutputModel()) : null); } }
/// <summary> /// ���������ֶ�ֵ���£�model��������κ�ֵ��������Ϊ�������� /// ��model�г��Զ������ֶ���������в�Ϊnull���ֶν��ᱻ���� /// </summary> /// <param name="models">Ҫ���µ�ֵ����</param> /// <returns>Ӱ��ļ�¼��</returns> public virtual int UpdateWithIdentity(params Model[] models) { int returnCount = 0; foreach (var model in models) { this.setSystemField(model); ParmCollection conditionParms = this.Table.PrepareUpdateParms(model); conditionParms.AddRange(this.Table.PrepareAllParms(model)); returnCount += this.Internal_DataHelper.ExecuteNonQuery(string.Format(conditionParms.UpdateSql, this.Table.IdentityParm.WhereSql), conditionParms); } return(returnCount); }
/// <summary> /// ���������ֶ�ֵ���£�model��������κ�ֵ��������Ϊ�������� /// ��model�����г��Զ������ֶ���������в�Ϊnull���ֶν��ᱻ���� /// </summary> /// <param name="models">Ҫ���µ�ֵ����</param> /// <returns>���º�Ľ��</returns> public virtual IEnumerable <OutputModel> UpdateWithIdentityAndReturn(Model[] models) { var resultList = new List <OutputModel>(); foreach (var model in models) { this.setSystemField(model); ParmCollection conditionParms = this.Table.PrepareUpdateParms(model); conditionParms.AddRange(this.Table.PrepareAllParms(model)); var result = this.Select(string.Format(conditionParms.UpdateSql, this.Table.IdentityParm.WhereSql), conditionParms); resultList.Add(result); } return(resultList); }
public virtual PageModel <OutputModel> ListPage(Model model) { bool flag = typeof(BasePageModel).IsAssignableFrom(model.GetType()); PageModel <OutputModel> pageModel = new PageModel <OutputModel>(); ParmCollection parmCollection = base.Table.PrepareConditionParms(model, base.IsLikeMode); string requirSQL = string.Format(base.Table.Select, parmCollection.WhereSql, string.Empty); PageInfo pageInfo = new PageInfo(requirSQL, base.OrderBy, model.PageSize ?? 10, model.PageNO ?? 1); pageModel.ListData = List(pageInfo.PageSQL, parmCollection); pageModel.PageNO = (model.PageNO ?? 1); pageModel.PageSize = (model.PageSize ?? 10); using (IDataReader dataReader = base.Internal_DataHelper.ExecuteReader(pageInfo.PageCountSQL, parmCollection)) { pageModel.DataCount = (dataReader.Read() ? dataReader.GetValue <int>("PAGECOUNT") : 0); } return(pageModel); }
/// <summary> /// 根据model的值作为查询条件,model里每一个非null字段都会作为查询条件 /// 获取分页的数据 /// 支持模糊查询 /// </summary> /// <param name="model">作为条件的model</param> /// <returns>符合查询条件的分页数据集合</returns> public virtual PageModel <OutputModel> ListPage(Model model) { bool isIMyInterface = typeof(BasePageModel).IsAssignableFrom(model.GetType()); var result = new PageModel <OutputModel>(); ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode); var requirSql = string.Format(this.Table.Select, parms.WhereSql, string.Empty); PageInfo pageInfo = new PageInfo(requirSql, this.OrderBy, model.PageSize ?? 10, model.PageNO ?? 1); result.ListData = List(pageInfo.PageSQL, parms); result.PageNO = model.PageNO ?? 1; result.PageSize = model.PageSize ?? 10; using (IDataReader rdr = Internal_DataHelper.ExecuteReader(pageInfo.PageCountSQL, parms)) { result.DataCount = rdr.Read() ? rdr.GetValue <int>("PAGECOUNT") : 0; } return(result); }
/// <summary> /// 根据Model数据表属性,预生成Select,Update,Delete,Insert语句主干 /// </summary> /// <param name="table">Model数据表属性</param> /// <param name="dataHelper">数据库连接对象</param> /// <param name="t">Model类型</param> internal void Initial(ModelAttribute table, IDbDataHelper dataHelper, Type t) { string selectColumns = string.Empty; string updateColumns = string.Empty; string insertColumns = string.Empty; string identityColumn = string.Empty; string insertselectColumns = string.Empty; string keysColumns = string.Empty; this.m_parameters = new Collection <IDataParameter>(); foreach (DBFieldInfoAttribute f in table) { if (f.Field == null) { FieldInfo field = t.GetField(f.FieldName); if (field == null) { throw new MyModel.MyException(string.Format("Model[{0}]中没有字段名为[{1}]的private或protected的字段。", t, f.FieldName)); } f.Field = field; } f.ParameterPrefix = dataHelper.ParameterPrefix; f.Prefix = dataHelper.Prefix; f.Suffix = dataHelper.Suffix; IDataParameter parm = dataHelper.Parameter(f.ParameterName, dataHelper.DbType(f), f.ColumnName); this.m_parameters.Add(parm); // 自增字段 if (f.IsIdentity) { this.m_identityParm = new ParmCollection(parm); this.m_identityParm.WhereSql = string.Format(" WHERE {0}={1} ", f.ColumnNameFix, f.ParameterName); identityColumn = f.ColumnNameFix; } else { if (f.SqlDbType != SqlDbType.Timestamp) { updateColumns += string.Format("{0}={1}, ", f.ColumnNameFix, f.ParameterName); insertColumns += string.Format("{0}, ", f.ParameterName); insertselectColumns += string.Format("{0}, ", f.ColumnNameFix); } selectColumns += string.Format("{0}, ", f.ColumnNameFix); } if (f.IsKey) { this.m_keysParms.Add(parm); keysColumns += string.Format("{0}={1} AND ", f.ColumnNameFix, f.ParameterName); } if (f.IsMainKey) { this.m_treeMainKeyColName = f.ColumnName; } if (f.IsParentKey) { this.m_treeParentKeyColName = f.ColumnName; } } if (identityColumn == string.Empty && selectColumns == string.Empty) { throw new MyModel.MyException(t.ToString() + " 类中没有一个Field应用DbFieldInfoAttribute属性。"); } string select = selectColumns.Substring(0, selectColumns.Length - 2); string insertselect = insertselectColumns.Substring(0, insertselectColumns.Length - 2); this.m_fields = table; this.m_modelName = t.ToString(); this.m_tableName = table.AttrTable.TableName; this.m_select = string.Format("SELECT {0}{1} FROM {2} {3} {4}", identityColumn == string.Empty ? string.Empty : (identityColumn + ", "), select, "{0}", "{1}", "{2}"); this.m_update = string.Format("UPDATE {0} SET {1} {2}", "{0}", updateColumns.Substring(0, updateColumns.Length - 2), "{1}"); this.m_insert = string.Format("INSERT INTO {0}({1}) OUTPUT INSERTED.* VALUES({2})", "{0}", insertselect, insertColumns.Substring(0, insertColumns.Length - 2)); this.m_insertCol = string.Format("INSERT INTO {0}({1}) VALUES({2})", "{0}", insertselect, "{1}"); this.m_keysParms.WhereSql = keysColumns.Length > 0 ? string.Format(" WHERE {0}", keysColumns.Substring(0, keysColumns.Length - 4)) : string.Empty; }
/// <summary> /// ����model��ֵ��Ϊ��ѯ����,model��ÿһ����null�ֶζ�����Ϊ��ѯ���� /// ���ط��������ĵ�һ��model /// ֧��ģ����ѯ /// </summary> /// <param name="model">��Ϊ������model</param> /// <returns>���ϲ�ѯ�����ĵ�һ��model</returns> public virtual OutputModel SelectWithModel(Model model) { ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode); return(this.Select(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms)); }
/// <summary> /// ���Զ������ֶ�Ϊ��ѯ��������ȡ��������model /// </summary> /// <param name="identity">��Ϊ�������Զ�����</param> /// <returns>��������model</returns> public virtual OutputModel SelectWithIdentity(long identity) { ParmCollection parms = this.Table.PrepareIdentityParm(identity); return(this.Select(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms)); }
/// <summary> /// 根据具体Model对象生成Where参数及子句 /// 对象中属性为null值的不作为生成条件 /// </summary> /// <param name="o">Model对象</param> /// <param name="or">条件连接是否使用or,true为使用or,false为使用and</param> /// <param name="isLikeMode">是否模糊查询</param> /// <returns>Where参数及子句</returns> public virtual ParmCollection PrepareSearchParms(object o, bool or, bool isLikeMode) { ParmCollection temp = new ParmCollection(); if (o == null) { return(temp); } string where = string.Empty; foreach (IDataParameter p in this.Parameters) { foreach (DBFieldInfoAttribute f in this.Fields) { object val = f.Field.GetValue(o); if (f.ColumnName == p.SourceColumn && val != null && val.ToString() != string.Empty) { if (f.Field.FieldType == typeof(byte[])) {//字段为byte[0]时不做查询条件 var byteVal = (byte[])val; if (byteVal.Length == 0) { break; } } string link; if (f.Field.FieldType == typeof(string) && isLikeMode) { val = val.ToString().Replace("[", "[[]").Replace("%", "[%]").Replace("^", "[^]").Replace("_", "[_]"); link = "LIKE"; if (f.LikeEqual == EnumLikeMode.NoLike) { link = "="; } if (f.LikeEqual == EnumLikeMode.AllLike) { val = "%" + val + "%"; } if (f.LikeEqual == EnumLikeMode.BackwardLike) { val = val + "%"; } if (f.LikeEqual == EnumLikeMode.ForwardLike) { val = "%" + val; } } else { val = f.Field.GetValue(o); link = "="; } p.Value = val; where += string.Format("{0} {1} {2} {3} ", f.ColumnNameFix, link, f.ParameterName, or ? "OR" : "AND"); temp.Add(p); break; } } } if (where.Length > 0) { where = string.Format("{0}{1}", " WHERE ", where.Substring(0, where.Length - 5)); temp.WhereSql = where; } return(temp); }
/// <summary> /// �������ֶ�Ϊ��ѯ������model�еķ������ֶξ�����Ϊ��ѯ��������ȡ��������model /// </summary> /// <param name="model">��Ϊ������model</param> /// <returns>��������model</returns> public virtual OutputModel SelectWithKeys(Model model) { ParmCollection parms = this.Table.PrepareKeysParms(model); return(this.Select(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms)); }
/// <summary> /// ����model��ֵ��Ϊ������ѯ������model��ÿһ����null�ֶζ�����Ϊ��ѯ���� /// ������ѯ���м�¼����modelΪnull���� /// ֧��ģ����ѯ /// </summary> /// <param name="model">��Ϊ������model</param> /// <returns>���ϲ�ѯ���������ݼ���</returns> public virtual IEnumerable <OutputModel> List(Model model) { ParmCollection parms = this.Table.PrepareConditionParms(model, this.IsLikeMode); return(List(string.Format(this.Table.Select, parms.WhereSql, this.OrderBy), parms)); }