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