public int insert(GroupMemberModels item) { int rs = 0; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("INSERT "); sqlBuilder.Append("INTO "); sqlBuilder.Append("gov_group_members "); sqlBuilder.Append("( "); sqlBuilder.Append("group_id, "); sqlBuilder.Append("username, "); sqlBuilder.Append("update_username, "); sqlBuilder.Append("update_datetime "); sqlBuilder.Append(") "); sqlBuilder.Append("values("); sqlBuilder.Append("@1, @2, @3, @4"); sqlBuilder.Append(")"); this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); objCmd.Parameters.AddWithValue("@1", item.GroupId); objCmd.Parameters.AddWithValue("@2", item.Username); objCmd.Parameters.AddWithValue("@3", item.UpdateUsername); objCmd.Parameters.AddWithValue("@4", item.UpdateDatetime); rs = objCmd.ExecuteNonQuery(); return rs; }
public int delete(GroupMemberModels item) { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("DELETE "); sqlBuilder.Append("FROM "); sqlBuilder.Append("gov_group_members "); sqlBuilder.Append("WHERE "); sqlBuilder.Append("group_id = @1 "); sqlBuilder.Append("AND "); sqlBuilder.Append("username = @2 "); this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); objCmd.Parameters.AddWithValue("@1", item.GroupId); objCmd.Parameters.AddWithValue("@2", item.Username); int rs = objCmd.ExecuteNonQuery(); getConnection().Close(); return rs; }
public List<GroupMemberModels> select(int page, int limit) { int offset = (page - 1) * limit; StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.Append("SELECT "); sqlBuilder.Append("* "); sqlBuilder.Append("FROM "); sqlBuilder.Append("gov_group_members ggm "); sqlBuilder.Append(getBaseSQL()); if (page > 0 && limit > 0) { sqlBuilder.Append(" LIMIT @limit "); sqlBuilder.Append(" OFFSET @offset "); } this.Sql = sqlBuilder.ToString(); MySqlCommand objCmd = new MySqlCommand(Sql, getConnection()); if (page > 0 && limit > 0) { objCmd.Parameters.AddWithValue("@limit", limit); objCmd.Parameters.AddWithValue("@offset", offset); } MySqlDataReader dataReader = objCmd.ExecuteReader(); List<GroupMemberModels> lstGroupMember = new List<GroupMemberModels>(); GroupMemberModels item; while (dataReader.Read()) { item = new GroupMemberModels(); item.GroupId = Convert.ToInt32(dataReader["group_id"]); item.Username = dataReader["username"].ToString(); item.UpdateDatetime = Convert.ToDateTime(dataReader["update_datetime"]); item.UpdateUsername = dataReader["update_username"].ToString(); lstGroupMember.Add(item); } getConnection().Close(); return lstGroupMember; }