/// <summary> /// 分页方法 /// </summary> /// <param name="allcount">总数</param> /// <param name="tablename">表名,支持多表联查</param> /// <param name="fields">字段名</param> /// <param name="where">where条件 不需要加where</param> /// <param name="orderby">*排序条件不能为空,不需要加order by</param> /// <param name="getallcount">获取记录总数,true是,false否</param> /// <param name="pageindex">当前页,从1开始,默认1</param> /// <param name="pagesize">每页显示多少条数据,默认15</param> /// <param name="commandTimeout">执行命令超时时间,默认30秒</param> /// <returns></returns> public static List <T> GetPageList(out int allcount, string tablename, string fields = "*", string where = "", string orderby = "", bool getallcount = true, int pageindex = 1, int pagesize = 15, string connect = "", int commandTimeout = 30) { if (pageindex <= 0) { pageindex = 1; } if (pagesize <= 0) { pagesize = 15; } var parameters = new DynamicParameters(); parameters.Add("@Table", tablename); parameters.Add("@Fields", fields); parameters.Add("@Where", where); parameters.Add("@OrderBy", orderby); parameters.Add("@CurrentPage", pageindex); parameters.Add("@PageSize", pagesize); parameters.Add("@GetAllCount", getallcount); parameters.Add("@AllCount", 0, DbType.Int32, ParameterDirection.Output); MsSqlMapperHepler.commandTimeout = commandTimeout; var ret = MsSqlMapperHepler.StoredProcWithParams <T>("Proc_DataPagination", parameters, string.IsNullOrWhiteSpace(connect) ? DBKeys.PRX : connect); allcount = parameters.Get <int>("@AllCount"); return(ret); }
/// <summary> /// 获取转让协议未签名时获取的数据 /// </summary> /// <param name="id"></param> /// <param name="typeid"></param> /// <returns></returns> public static vw_Transfer GetIntKeySign_stare(int id) { string strsql = "select top 1 * from vw_Transfer where id = '" + id + "' order by CreateTime desc"; vw_Transfer si = MsSqlMapperHepler.SqlWithParamsSingle <vw_Transfer>(strsql, null, DBKeys.PRX); return(si); }
/// <summary> /// 获取列表 /// </summary> /// <param name="Stockid"></param> /// <returns></returns> public static Classes GetClassesList() { string strsql = "select id from Classes order by CreateTime desc "; var parameters = new DynamicParameters(); return(MsSqlMapperHepler.SqlWithParamsSingle <Classes>(strsql.ToString(), parameters, DBKeys.PRX)); }
public static string DPGetTableMaxId(string prefix, string field, string tablename, int digit, DBRepository db = null) { try { string retstr = null; var parameters = new DynamicParameters(); parameters.Add("@title", prefix); //开头字母,前缀字母 parameters.Add("@pkName", field); //要插入的表字段 parameters.Add("@tableName", tablename); //所在表 parameters.Add("@bitCount", digit); //不包括前缀的位数 if (db == null) { retstr = MsSqlMapperHepler.StoredProcWithParamsSingle <string>("sp_createKey", parameters, DBKeys.PRX); } else { retstr = db.StoredProcWithParamsSingle <string>("sp_createKey", parameters).FirstOrDefault(); } return(retstr); //返回值 } catch (Exception ex) { throw new Exception(ex.Message + "转换的过程中发生了错误!"); } }
/// <summary> /// 获取文件路径 /// </summary> /// <param name="classId"></param> /// <param name="classIndex"></param> /// <returns></returns> public static List <vw_ClassListJob> ClassListJob(string classid, int classindex) { string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构 fields = @" * "; //输出字段 table = @" vw_ClassListJob "; //表或者视图 orderby = "id"; //排序信息 StringBuilder sb = new StringBuilder(); //构建where条件 sb.Append("select * from vw_ClassListJob where "); sb.Append(" 1=1 "); if (!string.IsNullOrWhiteSpace(classid))//班级ID { sb.Append(" and classid = @ClassID"); } if (classindex != 0)//班级行号 { sb.Append(" and classindex = @ClassIndex "); } sb.Append(" order by id "); var parameters = new DynamicParameters(); parameters.Add("@classid", classid); parameters.Add("@classindex", classindex); return(MsSqlMapperHepler.SqlWithParams <vw_ClassListJob>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 保存学员评价 /// </summary> /// <param name="ar"></param> /// <returns></returns> public static bool SaveStudentEvalute_WX(AttendanceRecord ar) { bool ret = false; StringBuilder sb = new StringBuilder(); sb.Append(" update AttendanceRecord set Evaluate=@Evaluate "); sb.Append(" , UpdateTime=@UpdateTime "); sb.Append(" , UpdatorId=@UpdatorId "); sb.Append(" where ClassID=@ClassID "); sb.Append(" AND ClassIndex=@ClassIndex "); sb.Append(" AND StudentID=@StudentID "); var parameters = new DynamicParameters(); parameters.Add("@StudentID", ar.StudentID); parameters.Add("@Evaluate", ar.Evaluate); parameters.Add("@ClassID", ar.ClassID); parameters.Add("@ClassIndex", ar.ClassIndex); parameters.Add("@UpdateTime", ar.UpdateTime); parameters.Add("@UpdatorId", ar.UpdatorId); if (MsSqlMapperHepler.InsertUpdateOrDeleteSql(sb.ToString(), parameters, DBKeys.PRX) > 0) { ret = true; } return(ret); }
/// <summary> /// 获取主键类型是整数的签名对象 /// </summary> /// <param name="id"></param> /// <param name="typeid"></param> /// <returns></returns> public static SignImage GetIntKeySign(int id, int typeid) { string strsql = "select top 1 * from SignImage where IntKey = '" + id + "' and TypeID = " + typeid + " order by CreateTime desc"; SignImage si = MsSqlMapperHepler.SqlWithParamsSingle <SignImage>(strsql, null, DBKeys.PRX); return(si); }
/// <summary> /// 学员信息和预约单绑定 /// </summary> /// <param name="studi"></param> /// <param name="apid"></param> public static void BindStudentforAP(string studi, string apid) { Appointment ap = AppointmentData.GetOneByID(apid); ap.ApStudentID = studi; MsSqlMapperHepler.Update <Appointment>(ap, DBKeys.PRX); }
/// <summary> /// 获取启用课程下拉 /// </summary> /// <returns></returns> public static List <CommonEntity> GetCourseIL() { List <CommonEntity> ret; string sql = "select id,CourseName as name from Course"; ret = MsSqlMapperHepler.SqlWithParams <CommonEntity>(sql.ToString(), null, DBKeys.PRX); return(ret); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateTransfer(Transfer Rb) { Transfer Rbs = TransferData.GetTransferByID(Rb.ID); //获取对象 Cloner <Transfer, Transfer> .CopyTo(Rb, Rbs); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Rbs, DBKeys.PRX)); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateButton(SYSButton btn) { SYSButton btnto = ButtonData.GetButtonByID(btn.BTN_Id); //获取对象 Cloner <SYSButton, SYSButton> .CopyTo(btn, btnto); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(btnto, DBKeys.PRX)); }
/// <summary> /// 保存班级信息 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool Update(Classes Stu) { Classes Stuto = ClassesData.GetClassesByID(Stu.ID); //获取对象 Cloner <Classes, Classes> .CopyTo(Stu, Stuto); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX)); }
/// <summary> /// 获取SYS_Account表最大的主键 /// </summary> /// <param name="Stockid"></param> /// <returns></returns> public static int Max_ACC_Id() { string strsql = " select isnull(max(ACC_Id),1)as ID from SYS_Account"; var parameters = new DynamicParameters(); parameters.Add("@id"); return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取本次上课信息 /// </summary> /// <param name="classid"></param> /// <param name="classindex"></param> /// <returns></returns> public static vw_ClassAttendanceList GetOneClassAttendanceList(string classid, int classindex) { string strsql = "select * from vw_ClassAttendanceList where ClassID = '" + classid + "' and ClassIndex = " + classindex; vw_ClassAttendanceList ret = new vw_ClassAttendanceList(); ret = MsSqlMapperHepler.SqlWithParamsSingle <vw_ClassAttendanceList>(strsql, null, DBKeys.PRX); return(ret); }
/// <summary> /// 根据班级ID获取学员考勤信息 /// </summary> /// <param name="classid"></param> /// <returns></returns> public static List <vw_AttendanceRecord> GetAttendanceRecordByClassID(string classid, int ClassIndex) { string strsql = "select * from vw_AttendanceRecord where ClassID = '" + classid + "' and ClassIndex = " + ClassIndex; List <vw_AttendanceRecord> ret = new List <vw_AttendanceRecord>(); ret = MsSqlMapperHepler.SqlWithParams <vw_AttendanceRecord>(strsql, null, DBKeys.PRX); return(ret); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateReimburse(Reimburse Rb) { Reimburse Rbs = ReimburseData.GetReimburseByID(Rb.ID); //获取对象 Cloner <Reimburse, Reimburse> .CopyTo(Rb, Rbs); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Rbs, DBKeys.PRX)); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateDiscount(Discount Dis) { Discount Stuto = DiscountData.GetDiscountByID(Dis.ID); //获取对象 Cloner <Discount, Discount> .CopyTo(Dis, Stuto); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX)); }
/// <summary> /// 根据TimePeriod的ID获取开始时间 /// </summary> /// <param name="perid"></param> /// <returns></returns> public static string GetStartTimePeriodByid(int perid) { string ret = ""; string strsql = "select DicItemName from DictionaryItem where DicTypeID = 8 and [DicItemID] = " + perid; ret = " " + MsSqlMapperHepler.SqlWithParamsSingle <string>(strsql, null, DBKeys.PRX).Substring(0, 5); return(ret); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateRefund(Refund re) { Refund Ref = RefundInfoData.GetRefundByID(re.ID); //获取对象 Cloner <Refund, Refund> .CopyTo(re, Ref); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Ref, DBKeys.PRX)); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateStudent(Students Stu) { Students Stuto = StudentData.GetStudentsByID(Stu.ID); //获取对象 Cloner <Students, Students> .CopyTo(Stu, Stuto); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX)); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateAttendanceRecord(AttendanceRecord att) { AttendanceRecord Stuto = StudentScheduleListData.GetAttendanceRecordByID(att.ID); //获取对象 Cloner <AttendanceRecord, AttendanceRecord> .CopyTo(att, Stuto); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX)); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateSYS_Account(SYSAccount sys) { SYSAccount sysa = Accounts_UpdateData.GetSYS_AccountByID(sys.ACC_Id); //获取对象 Cloner <SYSAccount, SYSAccount> .CopyTo(sys, sysa); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(sysa, DBKeys.PRX)); }
public static bool updateCourse(Course course) { Course to = CourseData.getCourseById(course.ID); Cloner <Course, Course> .CopyTo(course, to); return(MsSqlMapperHepler.Update(to, DBKeys.PRX)); }
/// <summary> /// 保存 /// </summary> /// <param name="btn"></param> /// <returns></returns> public static bool UpdateMessage(Message Mes) { Message Stuto = MessageData.GetMessageByID(Mes.ID); //获取对象 Cloner <Message, Message> .CopyTo(Mes, Stuto); //代码克隆,把前台或者的值也就是变更内容复制到目标对象,不做变更的数据不变 return(MsSqlMapperHepler.Update(Stuto, DBKeys.PRX)); }
/// <summary> /// 跳转查询账号信息 /// </summary> /// <param name="apid"></param> /// <returns></returns> public static List <SYSAccount> GetAccounts_Update(string ACC_Account) { String sql = "select * from SYS_Account where ACC_Account = @ACC_Account "; var dynamic = new DynamicParameters(); dynamic.Add("@ACC_Account", ACC_Account); return(MsSqlMapperHepler.SqlWithParams <SYSAccount>(sql, dynamic, DBKeys.PRX)); }
/// <summary> /// 根据绑定的账号获取一名学员信息 /// </summary> /// <param name="AccountID"></param> /// <returns></returns> public static Students GetStudentByAccountID(string AccountID) { string strsql = "select TOP 1 * from Students where BindAccount = @AccountID "; var parameters = new DynamicParameters(); parameters.Add("@AccountID", AccountID); return(MsSqlMapperHepler.SqlWithParamsSingle <Students>(strsql.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取列表 /// </summary> /// <param name="Stockid"></param> /// <returns></returns> public static Students GetStudentsOne(string Mosaic) { string strsql = "select top 1 * from Students WHERE ID like @Mosaic + '%' order by CreateTime desc "; var parameters = new DynamicParameters(); parameters.Add("@Mosaic", Mosaic); return(MsSqlMapperHepler.SqlWithParamsSingle <Students>(strsql.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 根据预约单号ID的主键 获取列表vw_Appointment /// </summary> /// <param name="Stockid"></param> /// <returns></returns> public static vw_Appointment Getvw_AppointmentList(string ID) { string strsql = "select top 1 * from vw_Appointment WITH(NOLOCK) where ID=@ID "; var parameters = new DynamicParameters(); parameters.Add("@ID", ID); return(MsSqlMapperHepler.SqlWithParamsSingle <vw_Appointment>(strsql.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 修改判断手机号是唯一 /// </summary> /// <param name="Stockid"></param> /// <returns></returns> public static int BindPhone_update(string id, string BindPhone) { string strsql = "select id from Students where id <> @id and BindPhone = @BindPhone"; var parameters = new DynamicParameters(); parameters.Add("@id", id); parameters.Add("@BindPhone", BindPhone); return(MsSqlMapperHepler.SqlWithParamsSingle <int>(strsql.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 新增判断手机号+姓名是唯一 /// </summary> /// <param name="Stockid"></param> /// <returns></returns> public static string BindPhone_insert(string BindPhone, string name) { string strsql = "select id from Students WITH(NOLOCK) where BindPhone=@BindPhone and Name = @Name"; var parameters = new DynamicParameters(); parameters.Add("@BindPhone", BindPhone); parameters.Add("@Name", name); return(MsSqlMapperHepler.SqlWithParamsSingle <string>(strsql.ToString(), parameters, DBKeys.PRX)); }