public List <Group> GetGroupsByUserid(int id)
{
string sql = "SELECT e_groupid FROM en_usertogroup where e_userid=@id";
MySqlParameter[] param =
{
new MySqlParameter("@id", MySqlDbType.Int32)
};
param[0].Value = id;
//2 执行
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql, CommandType.Text, param);
//3 关系--》对象
List <int> groupsid = new List <int>();
foreach (DataRow dr in dt.Rows)
{
groupsid.Add(Convert.ToInt32(dr["e_groupid"]));
}
List <Group> groups = new List <Group>();
foreach (int i in groupsid)
{
groups.Add(GetGroupById(i));
}
return(groups);
}
public int Delete(int id)
{
string sql = "DELETE FROM en_user WHERE e_id =" + id;
MySqlDbHelper db = new MySqlDbHelper();
return(db.ExecuteNonQuery(sql));
}
public List <User> GetUsersByGroupId(int id)
{
string sql = "SELECT e_userid FROM en_usertogroup where e_groupid=@groupid";
MySqlParameter[] param =
{
new MySqlParameter("@groupid", MySqlDbType.Int32)
};
param[0].Value = id;
//2 执行sql语句
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql, CommandType.Text, param);
//3 关系--》对象
List <int> idlist = new List <int>();
foreach (DataRow dr in dt.Rows)
{
//行转化成对象
idlist.Add(Convert.ToInt32(dr["e_userid"]));
}
List <User> userlist = new List <User>();
foreach (int i in idlist)
{
userlist.Add(GetUserByUserId(i));
}
return(userlist);
}
public List <Message> GetSomeMessage(User sender, User receiver, int id)
{
string sql = "SELECT * FROM en_message where e_userid=@userid and e_receiverid=@receiverid or e_userid=@receiverid and e_receiverid=@userid"
+ " order by e_sendtime desc limit " + id.ToString();
MySqlParameter[] param =
{
new MySqlParameter("@userid", MySqlDbType.Int32),
new MySqlParameter("@receiverid", MySqlDbType.Int32)
};
param[0].Value = sender.Id;
param[1].Value = receiver.Id;
//2 执行
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql, CommandType.Text, param);
//3 关系--》对象
List <Message> msgs = new List <Message>();
foreach (DataRow dr in dt.Rows)
{
msgs.Add(DataRowToMessage(dr));
}
msgs.Reverse();
return(msgs);
}
public int AddMessage(Message message)
{
//sql语句
String sql = "INSERT INTO en_message(e_userid,e_receiverid,e_content,e_sendtime,e_msgstate,e_sendtype,e_file) " +
"VALUES(@userid,@receiverid,@content,@sendtime,@msgstate,@sendtype,@file)";
//参数列表
MySqlParameter[] param = { new MySqlParameter("@userid", MySqlDbType.Int32),
new MySqlParameter("@receiverid", MySqlDbType.Int32),
new MySqlParameter("@content", MySqlDbType.VarChar),
new MySqlParameter("@sendtime", MySqlDbType.DateTime),
new MySqlParameter("@msgstate", MySqlDbType.Int16),
new MySqlParameter("@sendtype", MySqlDbType.Int16),
new MySqlParameter("@file", MySqlDbType.LongBlob) };
//参数赋值
param[0].Value = message.Userid;
param[1].Value = message.Receiverid;
param[2].Value = message.Content;
param[3].Value = message.Sendtime;
param[4].Value = message.State;
param[5].Value = message.Sendtype;
param[6].Value = message.Sendfile;;
MySqlDbHelper db = new MySqlDbHelper();
return(db.ExecuteNonQuery(sql, CommandType.Text, param));
}
public int AddUserToGroup(int id, int groupid)
{
//sql语句
String sql = "INSERT INTO en_usertogroup(e_userid,e_groupid) VALUES(@id,@group)";
//参数列表
MySqlParameter[] param = { new MySqlParameter("@id", MySqlDbType.Int32),
new MySqlParameter("@group", MySqlDbType.Int32) };
//参数赋值
param[0].Value = id;
param[1].Value = groupid;
MySqlDbHelper db = new MySqlDbHelper();
return(db.ExecuteNonQuery(sql, CommandType.Text, param));
}
public List <Group> GetAllGroup()
{
string sql = "SELECT * FROM en_group";
//2 执行
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql);
//3 关系--》对象
List <Group> groups = new List <Group>();
foreach (DataRow dr in dt.Rows)
{
//行转化成对象
Group group = DataRowToGroup(dr);
groups.Add(group);
}
return(groups);
}
public List <User> GetAllUsers()
{
string sql = "SELECT * FROM en_user";
//2 执行
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql);
//3 关系--》对象
List <User> userlist = new List <User>();
foreach (DataRow dr in dt.Rows)
{
//行转化成对象
User user = DataRowToUser(dr);
userlist.Add(user);
}
return(userlist);
}
public List <Department> GetAllDepartment()
{
string sql = "SELECT * FROM en_department";
//2 执行
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql);
//3 关系--》对象
List <Department> departments = new List <Department>();
foreach (DataRow dr in dt.Rows)
{
//行转化成对象
Department department = DataRowToDepartment(dr);
departments.Add(department);
}
return(departments);
}
/// <summary>
/// 添加员工信息
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int AddUser(User user)
{
//sql语句
String sql = "INSERT INTO en_user(e_deptid, e_worknum, e_name, e_username, e_password, e_gender, e_phone, e_position, e_state, e_control, e_ipaddress, e_check, e_avatar) " +
"VALUES(@deptid, @worknum, @name, @username, @password, @gender, @phone, @position, @state, @control, @ipaddress, @check, @avatar)";
//参数列表
MySqlParameter[] param =
{
new MySqlParameter("@deptid", MySqlDbType.Int32),
new MySqlParameter("@worknum", MySqlDbType.Int32),
new MySqlParameter("@name", MySqlDbType.VarChar),
new MySqlParameter("@username", MySqlDbType.VarChar),
new MySqlParameter("@password", MySqlDbType.VarChar),
new MySqlParameter("@gender", MySqlDbType.Int16),
new MySqlParameter("@phone", MySqlDbType.VarChar),
new MySqlParameter("@position", MySqlDbType.VarChar),
new MySqlParameter("@state", MySqlDbType.Int16),
new MySqlParameter("@control", MySqlDbType.Int16),
new MySqlParameter("@ipaddress", MySqlDbType.VarChar),
new MySqlParameter("@check", MySqlDbType.Int16),
new MySqlParameter("@avatar", MySqlDbType.MediumBlob)
};
//参数赋值
param[0].Value = user.Deptid;
param[1].Value = user.Worknum;
param[2].Value = user.Name;
param[3].Value = user.Username;
param[4].Value = user.Password;
param[5].Value = user.Gender;
param[6].Value = user.Phone;
param[7].Value = user.Position;
param[8].Value = user.State;
param[9].Value = user.Control;
param[10].Value = user.Ipaddress;
param[11].Value = user.Check;
param[12].Value = user.Avatar;
MySqlDbHelper db = new MySqlDbHelper();
return(db.ExecuteNonQuery(sql, CommandType.Text, param));
}
/// <summary>
/// 更新用户
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int UpdateUser(User user)
{
//1.sql语句
string sql = "UPDATE en_user SET e_deptid= @deptid, e_worknum= @worknum, e_name= @name, e_username= @username, " +
"e_password= @password, e_gender= @gender, e_phone= @phone, e_position= @position, e_state=@state, " +
"e_control= @control, e_ipaddress=@ipaddress, e_check= @check, e_avatar=@avatar WHERE e_id=@id";
MySqlParameter[] param =
{
new MySqlParameter("@deptid", MySqlDbType.Int32),
new MySqlParameter("@worknum", MySqlDbType.Int32),
new MySqlParameter("@name", MySqlDbType.VarChar),
new MySqlParameter("@username", MySqlDbType.VarChar),
new MySqlParameter("@password", MySqlDbType.VarChar),
new MySqlParameter("@gender", MySqlDbType.Int16),
new MySqlParameter("@phone", MySqlDbType.VarChar),
new MySqlParameter("@position", MySqlDbType.VarChar),
new MySqlParameter("@state", MySqlDbType.Int16),
new MySqlParameter("@control", MySqlDbType.Int16),
new MySqlParameter("@ipaddress", MySqlDbType.VarChar),
new MySqlParameter("@check", MySqlDbType.Int16),
new MySqlParameter("@avatar", MySqlDbType.MediumBlob),
new MySqlParameter("@id", MySqlDbType.Int32)
};
param[0].Value = user.Deptid;
param[1].Value = user.Worknum;
param[2].Value = user.Name;
param[3].Value = user.Username;
param[4].Value = user.Password;
param[5].Value = user.Gender;
param[6].Value = user.Phone;
param[7].Value = user.Position;
param[8].Value = user.State;
param[9].Value = user.Control;
param[10].Value = user.Ipaddress;
param[11].Value = user.Check;
param[12].Value = user.Avatar;
param[13].Value = user.Id;
MySqlDbHelper db = new MySqlDbHelper();
return(db.ExecuteNonQuery(sql, CommandType.Text, param));
}
public Department GetDepartmentById(int id)
{
string sql = "SELECT * FROM en_department where e_id=@id";
MySqlParameter[] param =
{
new MySqlParameter("@id", MySqlDbType.Int32)
};
param[0].Value = id;
//2 执行
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql, CommandType.Text, param);
//3 关系--》对象
Department dept = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
dept = DataRowToDepartment(dr);
}
return(dept);
}
public Group GetGroupById(int id)
{
//1 sql语句
string sql = "SELECT * FROM en_group WHERE e_id=@id";
MySqlParameter[] param =
{
new MySqlParameter("@id", MySqlDbType.Int32)
};
param[0].Value = id;
//2 执行sql语句
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql, CommandType.Text, param);
//3 关系--》对象 orm --》 ef nhibernate
Group group = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
group = DataRowToGroup(dr);
}
return(group);
}
public int AddGroup(string name)
{
//sql语句
String sql = "INSERT INTO en_group(e_name) VALUES(@name)";
//参数列表
MySqlParameter[] param = { new MySqlParameter("@name", MySqlDbType.VarChar) };
//参数赋值
param[0].Value = name;
MySqlDbHelper db = new MySqlDbHelper();
db.ExecuteNonQuery(sql, CommandType.Text, param);
sql = "SELECT LAST_INSERT_ID()";
DataTable dt = db.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
return(Convert.ToInt32(dr[0]));
}
return(0);
}
public User GetUserByUserId(int id)
{
//1 sql语句
string sql = "SELECT * FROM en_user WHERE e_id=@userid";
MySqlParameter[] param =
{
new MySqlParameter("@userid", MySqlDbType.Int32)
};
param[0].Value = id;
//2 执行sql语句
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql, CommandType.Text, param);
//3 关系--》对象 orm --》 ef nhibernate
User user = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
user = DataRowToUser(dr);
}
return(user);
}
public List <GroupMessage> GetSomeMessage(Group group, int n)
{
string sql = "SELECT * FROM en_groupmsg where e_groupid=@groupid"
+ " order by e_sendtime desc limit " + n.ToString();
MySqlParameter[] param =
{
new MySqlParameter("@groupid", MySqlDbType.Int32),
};
param[0].Value = group.Id;
//2 执行
MySqlDbHelper db = new MySqlDbHelper();
DataTable dt = db.ExecuteDataTable(sql, CommandType.Text, param);
//3 关系--》对象
List <GroupMessage> msgs = new List <GroupMessage>();
foreach (DataRow dr in dt.Rows)
{
msgs.Add(DataRowToGroupMessage(dr));
}
msgs.Reverse();
return(msgs);
}
