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();
}