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