/// <summary> /// 查询数据(实体) /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="where">拼接的where条件字符串</param> /// <returns>返回结果集</returns> public T SelectEntity <T>(string where) { try { Type typeFromHandle = typeof(T); string strTableName = typeFromHandle.Name; if (!string.IsNullOrEmpty(where)) { where = " where " + where; } string sql = string.Format("select * from [" + strTableName + "] {0}", where); using (DbConnection Connection = new DBFactory().GetInstance()) { Connection.Open(); return(SqlMapper.Query <T>(Connection, sql, null, null, true, null, CommandType.Text).FirstOrDefault <T>()); } } catch (Exception ex) { return(default(T)); } }
/// <summary> /// 根据输入参数和输出参数的存储过程 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="storedProcedureName">存储过程名称</param> /// <param name="normalParas">普通参数 键值对</param> /// <param name="outPutParas">输出参数 键值对</param> /// <returns>查询结果泛型集合</returns> public int SelectByStored(string storedProcedureName, Hashtable normalParms, ref Hashtable outPutParms) { using (DbConnection Connection = new DBFactory().GetInstance()) { var parems = new DynamicParameters(); //添加普通参数 if (normalParms != null) { foreach (DictionaryEntry item in normalParms) { parems.Add("@" + item.Key, item.Value); } } //添加返回参数 if (outPutParms != null) { foreach (DictionaryEntry item in outPutParms) { //需要后期判断参数类型 得到 DbType parems.Add("@" + item.Key, item.Value, DbType.Int32, ParameterDirection.Output); //这样写返回值可能会出错 //parems.Add("@" + item.Key, ParameterDirection.Output); } } var retmes = Connection.QueryMultiple(storedProcedureName, parems, null, null, CommandType.StoredProcedure); string[] keys = new string[outPutParms.Keys.Count]; outPutParms.Keys.CopyTo(keys, 0); foreach (string item in keys) { outPutParms[item] = parems.Get <int>("@" + item); } var m = retmes.Read <int>().Single(); return(m); } }
/// <summary> /// 根据输入参数和输出参数的存储过程 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="storedProcedureName">存储过程名称</param> /// <param name="normalParas">普通参数 键值对</param> /// <returns>查询结果泛型集合</returns> public List <T> SelectByStoredProcedure <T>(string storedProcedureName, Hashtable normalParms) { try { using (DbConnection Connection = new DBFactory().GetInstance()) { var parems = new DynamicParameters(); //添加普通参数 if (normalParms != null) { foreach (DictionaryEntry item in normalParms) { parems.Add("@" + item.Key, item.Value); } } List <T> lists = Connection.Query <T>(storedProcedureName, parems, null, true, null, CommandType.StoredProcedure).ToList(); return(lists); } } catch (Exception ex) { return(null); } }
/// <summary> /// 判断重复 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="id">主键</param> /// <returns>是否存在</returns> public Enums.TickLingEnum IsExists <T>(string where, string id) { try { Type typeFromHandle = typeof(T); string strTableName = typeFromHandle.Name; string strwhere = where; if (!string.IsNullOrEmpty(id)) { strwhere += " and id='" + id + "'"; } string strSql = string.Format("select count(*) from [" + strTableName + "] where {0}", strwhere); using (DbConnection Connection = new DBFactory().GetInstance()) { Connection.Open(); return(Convert.ToInt32(Connection.Query <int>(strSql, null, null, false, null, CommandType.Text).ToList()[0]) > 0 ? Enums.TickLingEnum.Existence : Enums.TickLingEnum.NonExistence); } } catch (Exception ex) { return(Enums.TickLingEnum.Abnormity); } }
/// <summary> /// 此函数必须单独运行,同时停止其他持有数据库的程序的运行, /// 防止其他程序对数据库主键产生冲突。 /// 初始化过程,表中主键ID为人为直接制定 /// </summary> static void reset() { //================== #region 除所有数据 Queue <String> tableNames = new Queue <string>(); tableNames.Enqueue("person_role"); tableNames.Enqueue("role_permission"); tableNames.Enqueue("role"); tableNames.Enqueue("permission"); tableNames.Enqueue("voteOptionPersonResult"); tableNames.Enqueue("voteOption"); tableNames.Enqueue("vote"); tableNames.Enqueue("file"); tableNames.Enqueue("agenda"); tableNames.Enqueue("delegate"); tableNames.Enqueue("meeting"); tableNames.Enqueue("meetingPlace"); tableNames.Enqueue("person"); tableNames.Enqueue("device"); tableNames.Enqueue("person"); while (tableNames.Count != 0) { //删除数据库数据,不使用DAO string commandText = "delete from " + tableNames.Dequeue() + ";"; DBFactory.GetInstance().ExecuteNonQuery(commandText, null); } #endregion ///////////////////////////////////////////////////////////////////////// #region 初始化数据库表操作对象 PersonDAO personDao = Factory.getInstance <PersonDAO>(); RoleDAO roleDao = Factory.getInstance <RoleDAO>(); Person_RoleDAO person_roleDao = Factory.getInstance <Person_RoleDAO>(); PermissionDAO permissionDao = Factory.getInstance <PermissionDAO>(); Role_PermissionDAO role_permissionDao = Factory.getInstance <Role_PermissionDAO>(); DeviceDAO deviceDao = Factory.getInstance <DeviceDAO>(); MeetingPlaceDAO meetingPlaceDao = Factory.getInstance <MeetingPlaceDAO>(); MeetingDAO meetingDao = Factory.getInstance <MeetingDAO>(); DelegateDAO delegateDao = Factory.getInstance <DelegateDAO>(); AgendaDAO agendaDao = Factory.getInstance <AgendaDAO>(); FileDAO fileDao = Factory.getInstance <FileDAO>(); VoteDAO voteDao = Factory.getInstance <VoteDAO>(); VoteOptionDAO voteOptionDao = Factory.getInstance <VoteOptionDAO>(); VoteOptionPersonResultDAO voteOptionPersonResultDao = Factory.getInstance <VoteOptionPersonResultDAO>(); #endregion #region webServer 系统初始化数据 #region 添加超级管理员用户 Console.WriteLine("添加超级管理员用户"); PersonVO admin = new PersonVO(); int adminID = 1;//超级管理员用户ID固定为1 admin.personID = adminID; admin.personName = "超级管理员"; admin.personDepartment = "##"; admin.personJob = "##"; admin.personDescription = "***"; admin.personPassword = "******"; admin.personState = 1; admin.isAdmin = true; Console.WriteLine(personDao.insert <PersonVO>(admin)); #endregion #region 角色、权限 #region 添加系统基本角色 1-超级管理员,2-会议组织者,3-成员 Console.WriteLine("添加系统基本角色"); //系统角色,超级管理员角色ID=1,会议组织者=2,成员=3 int[] roleIDs = new int[3]; roleIDs[0] = 1; roleIDs[1] = 2; roleIDs[2] = 3; List <RoleVO> roles = new List <RoleVO>(); roles.Add(new RoleVO { roleID = roleIDs[0], roleName = "超级管理员", isIntegrant = true }); roles.Add(new RoleVO { roleID = roleIDs[1], roleName = "会议组织者", isIntegrant = true }); roles.Add(new RoleVO { roleID = roleIDs[2], roleName = "成员", isIntegrant = true }); foreach (RoleVO vo in roles) { Console.WriteLine(roleDao.insert <RoleVO>(vo)); } #endregion #region 添加权限(超级管理员角色) Console.WriteLine("添加权限(超级管理员角色)"); List <PermissionVO> permissions_admin = new List <PermissionVO>(); #region 超级管理员角色的权限列表 // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "进入管理员首页", permissionDescription = "Account-Admin" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "查看会场", permissionDescription = "MeetingPlace-GetMeetingPlaces" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "添加会场", permissionDescription = "MeetingPlace-CreateMeetingPlace" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新前获取会场", permissionDescription = "MeetingPlace-GetMeetingPlaceForUpdate" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新会场", permissionDescription = "MeetingPlace-UpdateMeetingPlace" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新会场状态", permissionDescription = "MeetingPlace-UpdateMeetingPlaceAvailable" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "查看设备", permissionDescription = "Device-GetDevices" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "添加设备", permissionDescription = "Device-CreateDevice" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新前获取设备", permissionDescription = "Device-GetDeviceForUpdate" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新设备", permissionDescription = "Device-UpdateDevice" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新设备状态", permissionDescription = "Device-UpdateDeviceAvailable" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "查看用户", permissionDescription = "User-GetUsers" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "添加用户", permissionDescription = "User-CreateUser" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新前获取用户", permissionDescription = "User-GetUserForUpdate" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新用户", permissionDescription = "User-UpdateUser" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "更新用户状态", permissionDescription = "User-UpdateUserAvailable" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "导入用户信息", permissionDescription = "User-Upload" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "查看权限", permissionDescription = "Role-GetPermissions" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "查看角色", permissionDescription = "Role-GetRoles" }); // permissions_admin.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "创建角色", permissionDescription = "Role-CreateRole" }); foreach (PermissionVO vo in permissions_admin) { Console.WriteLine(permissionDao.insert <PermissionVO>(vo)); } #endregion #region 建立超级管理员角色与权限关联 Console.WriteLine("建立超级管理员角色与权限关联"); //管理员角色、权限 List <Role_PermissionVO> role_permissions_admin = new System.Collections.Generic.List <Role_PermissionVO>(); foreach (PermissionVO permissionVo in permissions_admin) { role_permissions_admin.Add( new Role_PermissionVO { role_permissionID = Role_PermissionDAO.getID(), roleID = roleIDs[0], permissionID = permissionVo.permissionID }); } foreach (Role_PermissionVO vo in role_permissions_admin) { Console.WriteLine(role_permissionDao.insert <Role_PermissionVO>(vo)); } #endregion #endregion #region 添加权限(组织者角色) Console.WriteLine("建立权限(组织者角色)"); List <PermissionVO> permissions_org = new List <PermissionVO>(); #region 组织者角色的权限列表 // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "进入组织者首页", permissionDescription = "Account-Organizor" }); #region 会议 // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "展示会议", permissionDescription = "Meeting-Show_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "添加会议", permissionDescription = "Meeting-Add_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "编辑会议", permissionDescription = "Meeting-Edit_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "删除会议", permissionDescription = "Meeting-Delete_organizor" }); #endregion ///================================================= #region 参会人员 // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "参会人员首页", permissionDescription = "Delegate-Index_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "编辑参会人员", permissionDescription = "Delegate-Edit_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "添加参会人员", permissionDescription = "Delegate-Add_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "删除参会人员", permissionDescription = "Delegate-Delete_organizor" }); #endregion //=========================================== #region 议程 // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "议程首页", permissionDescription = "Agenda-Index_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "添加议程", permissionDescription = "Agenda-Add_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "编辑议程", permissionDescription = "Agenda-Edit_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "删除议程", permissionDescription = "Agenda-Delete_organizor" }); #endregion //=========================================== #region 附件 // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "附件首页", permissionDescription = "Document-Index_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "上传附件", permissionDescription = "Document-Add_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "转换附件", permissionDescription = "Document-StartConvert" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "下载附件", permissionDescription = "Document-Download" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "删除附件", permissionDescription = "Document-Delete_organizor" }); #endregion //================================================ #region 表决 // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "表决首页", permissionDescription = "Vote-Index_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "添加表决", permissionDescription = "Vote-Add_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "编辑表决", permissionDescription = "Vote-Edit_organizor" }); // permissions_org.Add( new PermissionVO { permissionID = PermissionDAO.getID(), permissionName = "删除表决", permissionDescription = "Vote-Delete_organizor" }); #endregion foreach (PermissionVO vo in permissions_org) { Console.WriteLine(permissionDao.insert <PermissionVO>(vo)); } #endregion //============================================== #region 组织者角色与权限关联 Console.WriteLine("建立组织者角色与权限关联"); //组织者角色、权限 List <Role_PermissionVO> role_permissions_org = new System.Collections.Generic.List <Role_PermissionVO>(); foreach (PermissionVO permissionVo in permissions_org) { role_permissions_org.Add( new Role_PermissionVO { role_permissionID = Role_PermissionDAO.getID(), roleID = roleIDs[1], permissionID = permissionVo.permissionID }); } foreach (Role_PermissionVO vo in role_permissions_org) { Console.WriteLine(role_permissionDao.insert <Role_PermissionVO>(vo)); } #endregion #endregion #region 超级管理员用户与超级管理员角色关联 Console.WriteLine("建立超级管理员用户与超级管理员角色关联"); int person_roleID = Person_RoleDAO.getID(); Person_RoleVO adminPR = new Person_RoleVO { person_roleID = person_roleID, personID = adminID, roleID = roleIDs[0] }; Console.WriteLine(person_roleDao.insert <Person_RoleVO>(adminPR)); #endregion #endregion #endregion }
public Dal_Imp() { myDB = DBFactory.getDB(); ////אתחול רשימת לימודים //lStudies = new ListOfTasks { ListOfTasksId = 3, CategoryList = "Studies" }; //myDB.Lists.Add(lStudies); //myDB.SaveChanges(); //Task t = new Task //{ // ListOfTasksId = 3, // TaskId = 1, // NameTask = "s1", // LocationTask = "Tel Aviv", // TypeTask = type1.studies, // DescriptionTask = "complete all three layers in the project", // DateTask = new DateTime(2017, 03, 26), // WhenToRemember = new DateTime(2017, 03, 25), // PriorityTask = priority.HIGH, //}; //myDB.Tasks.Add(t); //myDB.SaveChanges(); ////אתחול רשימת עבודה //lWork = new ListOfTasks { ListOfTasksId = 2, CategoryList = "Work" }; //myDB.Lists.Add(lWork); //myDB.SaveChanges(); //Task t1 = new Task //{ // ListOfTasksId = 2, // TaskId = 1, // NameTask = "w1", // LocationTask = "Tel Aviv", // TypeTask = type1.work, // DescriptionTask = "complete all three layers in the project", // DateTask = new DateTime(2017, 03, 26), // WhenToRemember = new DateTime(2017, 03, 25), // PriorityTask = priority.HIGH, //}; //myDB.Tasks.Add(t1); //myDB.SaveChanges(); //אתחול רשימת משפחה //lFamily = new ListOfTasks { ListOfTasksId = 1, CategoryList = "Family" }; //myDB.Lists.Add(lFamily); //myDB.SaveChanges(); //Task t = new Task //{ // ListOfTasksId = 1, // TaskId = 1, // NameTask = "finish project 2", // LocationTask = "ramat gan", // TypeTask = type1.family, // DescriptionTask = "complete all three layers in the project", // DateTask = new DateTime(2017, 03, 26), // WhenToRemember = new DateTime(2017, 03, 25), // PriorityTask = priority.HIGH, //}; //myDB.Tasks.Add(t); //myDB.SaveChanges(); // }