团队ORM组件 - Xxx.iW

1. 基于数据表 UserBasic 所需Model/DTO

  • UserBasic -> Model
namespace Iw.Example.Model.Entities
{
	using System;
	using Xxx.Attributes;
	using System.ComponentModel;
	using System.Collections.Generic;
	using Xxx.Contract.Database.Store.Impl;
	using System.ComponentModel.DataAnnotations;
	using Xxx;
	using Xxx.Resources.Cn;
	
    /// <summary>
    ///  - 实体类
    /// </summary>
    [Serializable]
    [TableInfo(TABLE_NAME, TABLE_ALIAS, IDENTITY_FIELD, ALL_FIELD)]
    public partial class UserBasic : XxxEntity<long>
    {
        public const string TABLE_NAME = "UserBasic";
        public const string TABLE_ALIAS = "Use67";
        public const string IDENTITY_FIELD = "Id";
        public const string ALL_FIELD = "Id,Unique_No,CreateUserId,CreateUserName,CreateTime,UpdateUserId,UpdateTime,UpdateUserName,UserName,Gender,Remark";

        public class IwExampleModel : F
        {

        }

        /// <summary>
        /// Field (数据库列字段)
        /// </summary>
        public partial class F
        {
            /// <summary>
            ///  标识列|用于关联(主外键等)关系
            /// </summary>
            public const string ID = "Id";

            /// <summary>
            ///  唯一号码|用于关联(主外键等)关系
            /// </summary>
            public const string UNIQUE_NO = "Unique_No";

            /// <summary>
            ///  创建人|标识,( 不作为 关联条件使用)
            /// </summary>
            public const string CREATEUSERID = "CreateUserId";

            /// <summary>
            ///  创建人|(优先存姓名;姓名不存在 存登录账号)
            /// </summary>
            public const string CREATEUSERNAME = "CreateUserName";

            /// <summary>
            ///  创建时间|(这条业务数据产生的时间)
            /// </summary>
            public const string CREATETIME = "CreateTime";

            /// <summary>
            ///  最后修改人|标识 (不作为 关联条件使用)
            /// </summary>
            public const string UPDATEUSERID = "UpdateUserId";

            /// <summary>
            ///  最后修改时间
            /// </summary>
            public const string UPDATETIME = "UpdateTime";

            /// <summary>
            ///  最后修改人|(优先存姓名;姓名不存在 存登录账号)
            /// </summary>
            public const string UPDATEUSERNAME = "UpdateUserName";

            /// <summary>
            ///  用户名
            /// </summary>
            public const string USERNAME = "UserName";

            /// <summary>
            ///  性别|0:默认值;10:男;20:女
            /// </summary>
            public const string GENDER = "Gender";

            /// <summary>
            ///  备注
            /// </summary>
            public const string REMARK = "Remark";

        }

        public const string SQL_INSERT = " INSERT INTO UserBasic (Unique_No,CreateUserId,CreateUserName,CreateTime,UpdateUserId,UpdateTime,UpdateUserName,UserName,Gender,Remark)  VALUES (@Unique_No,@CreateUserId,@CreateUserName,@CreateTime,@UpdateUserId,@UpdateTime,@UpdateUserName,@UserName,@Gender,@Remark)";
        public const string SQL_UPDATE = " UPDATE UserBasic SET Unique_No=@Unique_No,UpdateUserId=@UpdateUserId,UpdateTime=@UpdateTime,UpdateUserName=@UpdateUserName,UserName=@UserName,Gender=@Gender,Remark=@Remark WHERE 0=0  AND Id = @Id";
        public const string SQL_DELETE = " DELETE FROM UserBasic WHERE 0=0  ";
        public const string SQL_QUERY = " SELECT * FROM UserBasic  WHERE 0=0 ";

        public const string URL_MODULE = "/iwcurd/";
        public const string URL_CONTROLLER = "/iwcurd/userbasic/";
        public const string URL_ADD = "/iwcurd/userbasic/add";
        public const string URL_UPDATE = "/iwcurd/userbasic/update";
        public const string URL_LIST = "/iwcurd/userbasic/list";
        public const string URL_LISTPAGER = "/iwcurd/userbasic/listpager";
        public const string URL_DELETE = "/iwcurd/userbasic/delete";
        public const string URL_EXPORT = "/iwcurd/userbasic/export";
        public const string URL_IMPORT = "/iwcurd/userbasic/import";

        public const string EXCEL_TEMPLATE_NAME = "userbasic.xls";

        public const string AUTH_CODE_MODULE = "IwCurd";
        public const string AUTH_CODE_CONTROLLER = "IwCurd_UserBasic.";
        public const string AUTH_CODE_ADD = "IwCurd_UserBasic_Add";
        public const string AUTH_CODE_MODIFY = "IwCurd_UserBasic_Modify";
        public const string AUTH_CODE_LIST = "IwCurd_UserBasic_List";
        public const string AUTH_CODE_LISTPAGER = "IwCurd_UserBasic_ListPager";
        public const string AUTH_CODE_DELETE = "IwCurd_UserBasic_Delete";
        public const string AUTH_CODE_EXPORT = "IwCurd_UserBasic_Export";
        public const string AUTH_CODE_IMPORT = "IwCurd_UserBasic_Import";

        /// <summary>
        /// 唯一号码|用于关联(主外键等)关系
        /// </summary>
        [Display(Name = "唯一号码")]
        [Required(ErrorMessage = " {0} " + ResourcesConst.ErrorMsg.Required)]
        [MaxLength(36, ErrorMessage = " {0} " + ResourcesConst.ErrorMsg.MaxLength + " 36")]
        public string Unique_No { get; set; } = Guid.NewGuid().ToString();
        /// <summary>
        /// 用户名
        /// </summary>
        [Display(Name = "用户名")]
        [Required(ErrorMessage = " {0} " + ResourcesConst.ErrorMsg.Required)]
        [MaxLength(255, ErrorMessage = " {0} " + ResourcesConst.ErrorMsg.MaxLength + " 255")]
        public string UserName { get; set; }
        /// <summary>
        /// 性别|0:默认值;10:男;20:女
        /// </summary>
        [Display(Name = "性别")]
        public int? Gender { get; set; }
        /// <summary>
        /// 备注
        /// </summary>
        [Display(Name = "备注")]
        [MaxLength(200, ErrorMessage = " {0} " + ResourcesConst.ErrorMsg.MaxLength + " 200")]
        public string Remark { get; set; }

    }
    public class IwExampleModel : F
    {

    }

    /// <summary>
    /// Field (数据库列字段)
    /// </summary>
    public partial class F
    {
        /// <summary>
        ///  标识列|用于关联(主外键等)关系
        /// </summary>
        public const string ID = "Id";

        /// <summary>
        ///  唯一号码|用于关联(主外键等)关系
        /// </summary>
        public const string UNIQUE_NO = "Unique_No";

        /// <summary>
        ///  创建人|标识,( 不作为 关联条件使用)
        /// </summary>
        public const string CREATEUSERID = "CreateUserId";

        /// <summary>
        ///  创建人|(优先存姓名;姓名不存在 存登录账号)
        /// </summary>
        public const string CREATEUSERNAME = "CreateUserName";

        /// <summary>
        ///  创建时间|(这条业务数据产生的时间)
        /// </summary>
        public const string CREATETIME = "CreateTime";

        /// <summary>
        ///  创建时间|(这条业务数据产生的时间)开始时间
        /// </summary>
        public const string CREATETIMESTART = "CreateTimeStart";

        /// <summary>
        ///  创建时间|(这条业务数据产生的时间)结束时间
        /// </summary>
        public const string CREATETIMEEND = "CreateTimeEnd";

        /// <summary>
        ///  最后修改人|标识 (不作为 关联条件使用)
        /// </summary>
        public const string UPDATEUSERID = "UpdateUserId";

        /// <summary>
        ///  最后修改时间
        /// </summary>
        public const string UPDATETIME = "UpdateTime";

        /// <summary>
        ///  最后修改时间开始时间
        /// </summary>
        public const string UPDATETIMESTART = "UpdateTimeStart";

        /// <summary>
        ///  最后修改时间结束时间
        /// </summary>
        public const string UPDATETIMEEND = "UpdateTimeEnd";

        /// <summary>
        ///  最后修改人|(优先存姓名;姓名不存在 存登录账号)
        /// </summary>
        public const string UPDATEUSERNAME = "UpdateUserName";

        /// <summary>
        ///  用户名
        /// </summary>
        public const string USERNAME = "UserName";

        /// <summary>
        ///  性别|0:默认值;10:男;20:女
        /// </summary>
        public const string GENDER = "Gender";

        /// <summary>
        ///  备注
        /// </summary>
        public const string REMARK = "Remark";
    }
}
  • UserBasicCriteriaDto
namespace Iw.Example.Dto
{

	using System;
	using System.Collections.Generic;
	using System.ComponentModel.DataAnnotations;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;
	using Xxx.Attributes;
	using Xxx.Resources.Cn;
	using Xxx;
	using Xxx.Enums;
	using Xxx.AuxiliaryEntity;
	using Xxx.Contract.Applications.Dtos;
	using Entity = Iw.Example.Model.Entities.UserBasic;
	using Iw.Example.Model;

    /// <summary>
	///  - 查询数据传输对象
	/// </summary>
    [OrderByDesc(FixedValue.ID)]
	public partial class UserBasicCriteriaDto : CriteriaDto
	{

        /// <summary>
        /// 唯一号码|用于关联(主外键等)关系
        /// </summary>
        [Display(Name = "唯一号码")]
        [Query(Entity.F.UNIQUE_NO, DbKeywords.Equals, "@", Entity.F.UNIQUE_NO, tableAlias: Entity.TABLE_ALIAS)]
        public string Unique_No { get; set; }     

        /// <summary>
        /// 创建人|标识,( 不作为 关联条件使用)
        /// </summary>
        [Display(Name = "创建人")]
        [Query(Entity.F.CREATEUSERID, DbKeywords.Equals, "@", Entity.F.CREATEUSERID, tableAlias: Entity.TABLE_ALIAS)]
        public string CreateUserId { get; set; }     

        /// <summary>
        /// 创建人|(优先存姓名;姓名不存在 存登录账号)
        /// </summary>
        [Display(Name = "创建人")]
        [Query(Entity.F.CREATEUSERNAME, DbKeywords.Equals, "@", Entity.F.CREATEUSERNAME, tableAlias: Entity.TABLE_ALIAS)]
        public string CreateUserName { get; set; }     

        /// <summary>
        /// 创建时间|(这条业务数据产生的时间) 开始时间 
        /// </summary>
        [Display(Name = "创建时间")]
        [Query(Entity.F.CREATETIME, DbKeywords.GreaterThanOrEqual, "@", nameof(CreateTimeStart), tableAlias: Entity.TABLE_ALIAS)]
        public string CreateTimeStart { get; set; }     
        /// <summary>
        /// 创建时间|(这条业务数据产生的时间) 结束时间 
        /// </summary>
        [Display(Name = "创建时间")]
        [Query(Entity.F.CREATETIME, DbKeywords.LessThanOrEqual, "@", nameof(CreateTimeEnd), tableAlias: Entity.TABLE_ALIAS)]
        public string CreateTimeEnd { get; set; }     

        /// <summary>
        /// 最后修改人|标识 (不作为 关联条件使用)
        /// </summary>
        [Display(Name = "最后修改人")]
        [Query(Entity.F.UPDATEUSERID, DbKeywords.Equals, "@", Entity.F.UPDATEUSERID, tableAlias: Entity.TABLE_ALIAS)]
        public string UpdateUserId { get; set; }     

        /// <summary>
        /// 最后修改时间 开始时间 
        /// </summary>
        [Display(Name = "最后修改时间")]
        [Query(Entity.F.UPDATETIME, DbKeywords.GreaterThanOrEqual, "@", nameof(UpdateTimeStart), tableAlias: Entity.TABLE_ALIAS)]
        public string UpdateTimeStart { get; set; }     
        /// <summary>
        /// 最后修改时间 结束时间 
        /// </summary>
        [Display(Name = "最后修改时间")]
        [Query(Entity.F.UPDATETIME, DbKeywords.LessThanOrEqual, "@", nameof(UpdateTimeEnd), tableAlias: Entity.TABLE_ALIAS)]
        public string UpdateTimeEnd { get; set; }     

        /// <summary>
        /// 最后修改人|(优先存姓名;姓名不存在 存登录账号)
        /// </summary>
        [Display(Name = "最后修改人")]
        [Query(Entity.F.UPDATEUSERNAME, DbKeywords.Equals, "@", Entity.F.UPDATEUSERNAME, tableAlias: Entity.TABLE_ALIAS)]
        public string UpdateUserName { get; set; }     

        /// <summary>
        /// 用户名
        /// </summary>
        [Display(Name = "用户名")]
        [Query(Entity.F.USERNAME, DbKeywords.Equals, "@", Entity.F.USERNAME, tableAlias: Entity.TABLE_ALIAS)]
        public string UserName { get; set; }     

        /// <summary>
        /// 性别|0:默认值;10:男;20:女
        /// </summary>
        [Display(Name = "性别")]
        [Query(Entity.F.GENDER, DbKeywords.Equals, "@", Entity.F.GENDER, tableAlias: Entity.TABLE_ALIAS)]
        public int? Gender { get; set; }     

        /// <summary>
        /// 备注
        /// </summary>
        [Display(Name = "备注")]
        [Query(Entity.F.REMARK, DbKeywords.Equals, "@", Entity.F.REMARK, tableAlias: Entity.TABLE_ALIAS)]
        public string Remark { get; set; }     
	}
}

2. 建立一个数据仓库访问对象 Repository

IRepository repository = new Repository<SqlConnection>("Xxx"); // SqlServer 示例中使用的是SqlServer
// IRepository mySQLRepository = new Repository<MySqlConnection>("Xxx"); // MySQL

3. 新增

//单一新增 返回值为long 因为返回新增的主键ID是BIGINT类型
long addResult1 = repository.Add(new UserBasic() { 
	Gender = 10, 
	UserName = "张炳彬", 
	Remark = "Remark" }, db: null, tran: null, tableName: UserBasic.TABLE_NAME, returnIdentity: true, commandTimeout: 120, commandType: CommandType.Text);

//批量新增
IList<UserBasic> userBasicList = new List<UserBasic>() { new UserBasic() { Gender = 10, UserName = "张炳彬", Remark = "Remark" } };
//返回值int 返回插入成功数
int addResult2 = repository.Add(entities: userBasicList, tableName: UserBasic.TABLE_NAME, commandTimeout: 120, commandType: CommandType.Text);

//按照指定的字段插入
long addResult4 = repository.AddDesignate(entity: new UserBasic() { Gender = 10, UserName = "张炳彬", Remark = "Remark" }, tableName: null, db: null, tran: null, returnIdentity: true, commandTimeout: 120, commandType: CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);
//按照指定的字段批量插入
int addResult5 = repository.AddDesignate(entities: userBasicList, tableName: null, db: null, tran: null, commandTimeout: 120, CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);

//排除指定的字段插入
long addResult6 = repository.AddIgnore(entity: new UserBasic() { Gender = 10, UserName = "张炳彬", Remark = "Remark" }, tableName: null, db: null, tran: null, returnIdentity: true, commandTimeout: 120, commandType: CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);
//排除指定的字段批量插入
int addResult7 = repository.AddIgnore(entities: userBasicList, tableName: null, db: null, tran: null, commandTimeout: 120, CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);

4. 查询

//字段对应条件列表
IList<DbColumn> getWhere = new List<DbColumn>();
//UserBasic.F可以点出数据库对应的字段
getWhere.Add(UserBasic.F.USERNAME, "张炳彬");

//多数据查询 返回迭代器类型
IEnumerable<UserBasic> ResultList = repository.Get<UserBasic>(where: getWhere, db: null, tran: null, buffered: true, commandTimeout: 120, commandType: CommandType.Text);

//单数据查询 返回对象实体类型
UserBasic ResultFirstDefault = repository.GetFirstOrDefault<UserBasic>(where: getWhere, db: null, tran: null,commandTimeout: 120, commandType: CommandType.Text);

//多数据查询 返回对象数据表格
DataTable ResultDataTable = repository.GetDataTable<UserBasic>(where: getWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text);

5. 分页查询

//分页查询数据传输对象
Dto.UserBasicCriteriaDto dto = new Dto.UserBasicCriteriaDto();
dto.PageSize = 5;//每页X条
dto.PageIndex = 1;//第X页
dto.UserName = "张炳彬";//查询条件 不赋值为NULL不加入查询条件
//分页查询 排序会根据 数据传输对象 上的属性[OrderByDesc(FixedValue.ID)]来定义 返回迭代器类型
IEnumerable<UserBasic> ResultPagerList = repository.GetPager<UserBasic, Dto.UserBasicCriteriaDto>(criteria: dto, buffered: true, commandTimeout: 120, CommandType.Text);

6. 分页查询获取DataTable

//使用基础型的数据传输对象
CriteriaDto criteriaDto = new CriteriaDto();
criteriaDto.PageSize = 5;//每页X条
criteriaDto.PageIndex = 1;//第X页
//分页查询 排序按照示例 查询条件需要[4.查询]的字段对应条件列表
DataTable ResultPagerDataTable = repository.GetDataTablePager<UserBasic>(dto: criteriaDto, orderBy: "ORDER BY Id ASC", where: getWhere, commandTimeout: 120, CommandType.Text);

7. 更新

//字段对应条件列表
IList<DbColumn> updateWhere = new List<DbColumn>();
updateWhere.Add(UserBasic.F.USERNAME, "张炳彬");
//修改[4. 查询]的单数据查询的结果
ResultFirstDefault.Gender = 20;
//根据实体来更新数据库数据 返回结果为更新成功数
int updateResult1 = repository.Update<UserBasic>(entity: ResultFirstDefault, whereField: updateWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text);

//修改的字段对应条件列表
IList<DbColumn> updateSet = new List<DbColumn>();
updateSet.Add(UserBasic.F.GENDER, 30);
//根据修改的字段对应条件列表来更新数据库数据 返回结果为更新成功数
int updateResult2 = repository.Update<UserBasic>(set: updateSet, where: updateWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text);

//按照指定的字段更新
int updateResult4 = repository.UpdateDesignate<UserBasic>(entity: ResultFirstDefault, where: updateWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);
//按照指定的字段批量更新
int updateResult5 = repository.UpdateDesignate<UserBasic>(entities: ResultList.ToList(), where: updateWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);

//按照指定的字段更新
int updateResult6 = repository.UpdateIgnore<UserBasic>(entity: ResultFirstDefault, where: updateWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);
//按照指定的字段批量更新
int updateResult7 = repository.UpdateIgnore<UserBasic>(entities: ResultList.ToList(), where: updateWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text, UserBasic.F.GENDER, UserBasic.F.USERNAME);

8. 删除

//字段对应条件列表
IList<DbColumn> deleteWhere = new List<DbColumn>();
deleteWhere.Add(UserBasic.F.USERNAME, "张炳彬");
//根据字段对应条件列表来删除数据库数据 返回结果为删除成功数
int deleteResult1 = repository.Delete<UserBasic>(where: deleteWhere, db: null, tran: null, commandTimeout: 120, CommandType.Text);

9. 事务操作

//使用仓储对象创建一个连接
using IDbConnection con = repository.CreateConnection();
//使用连接创建一个事务
using IDbTransaction tran = con.BeginTransaction();
try
{
	//新增一条数据 把db和tran传入
    long addResult3 = repository.Add(new UserBasic() { Gender = 10, UserName = "张炳彬", Remark = "Remark" }, db: con, tran: tran, tableName: UserBasic.TABLE_NAME, returnIdentity: true, commandTimeout: 120, commandType: System.Data.CommandType.Text);
	
    IList<DbColumn> trangetWhere = new List<DbColumn>();
    getWhere.Add(UserBasic.F.USERNAME, "张炳彬", DbKeywords.Equals);
	//查询一条数据 把db和tran传入 按照事务隔离性必须传入
    UserBasic tranResultFirstDefault = repository.GetFirstOrDefault<UserBasic>(where: getWhere, db: con, tran: tran,commandTimeout: 120, commandType: CommandType.Text);
	
	//更新数据 把db和tran传入
    int updateResult3 = repository.Update<UserBasic>(set: updateSet, where: updateWhere, db: con, tran: tran, commandTimeout: 120, CommandType.Text);
	
	IList<DbColumn> deleteWhere1 = new List<DbColumn>();
    deleteWhere.Add(UserBasic.F.USERNAME, "张炳彬");
	//删除数据 把db和tran传入
    int deleteResult2 = repository.Delete<UserBasic>(where: deleteWhere1, db: null, tran: null, commandTimeout: 120, CommandType.Text);
	
	//提交事务
    tran.Commit();
}
catch
{
	//回滚事务
    tran.Rollback();
}
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

安得权

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值