public static int UserIconId(int userId)
{
var cmd = new MySqlCommand("select * from m_user where id = @user_id;", Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@user_id", userId, MySqlDbType.Int32, 10));
var reader = cmd.ExecuteReader();
reader.Read();
int val = DBNull.Value != reader["icon_id"] ? Convert.ToInt32(reader.GetString("icon_id")) : 0;
Conection.DisConnectDB();
return(val);
}
public static string UserName(int userId)
{
var cmd = new MySqlCommand("select * from m_user where id = @user_id;", Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@user_id", userId, MySqlDbType.Int32, 10));
var reader = cmd.ExecuteReader();
reader.Read();
var str = DBNull.Value != reader["user_name"] ? reader.GetString("user_name") : "";
Conection.DisConnectDB();
return(str);
}
public static void Online(User?user)
{
if (user == null)
{
return;
}
var command = new StringBuilder();
command.Append("update m_user set is_online = @isOnline where id = @id;");
var cmd = new MySqlCommand(command.ToString(), Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@isOnline", user.IsOnline, MySqlDbType.Byte, 1));
cmd.Parameters.Add(CreateParameter("@id", user.Id, MySqlDbType.Int32, 16));
cmd.ExecuteNonQuery();
Conection.DisConnectDB();
}
public static User?User(string mailAdress, string password)
{
var command = new StringBuilder();
command.Append("select * from m_user where mail_address = @mail and password = @pass;");
var cmd = new MySqlCommand(command.ToString(), Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@mail", mailAdress, MySqlDbType.VarChar, 255));
cmd.Parameters.Add(CreateParameter("@pass", password, MySqlDbType.VarChar, 40));
var reader = cmd.ExecuteReader();
reader.Read();
if (false == reader.HasRows)
{
Conection.DisConnectDB();
return(null);
}
bool isDeleted = DBNull.Value != reader["is_deleted"] ? reader.GetBoolean("is_deleted") : false;
if (isDeleted)
{
return(null);
}
var user = new User();
user.Id = DBNull.Value != reader["Id"] ? Convert.ToInt32(reader.GetString("Id")) : 0;
user.IconId = DBNull.Value != reader["icon_id"] ? Convert.ToInt32(reader.GetString("icon_id")) : 0;
user.Name = DBNull.Value != reader["user_name"] ? reader.GetString("user_name") : "";
user.MailAddress = DBNull.Value != reader["mail_address"] ? reader.GetString("mail_address") : "";
user.Password = DBNull.Value != reader["password"] ? reader.GetString("password") : "";
user.IsOnline = true;
string iconNum = user.IconId.ToString();
if (10 > user.IconId)
{
iconNum = "0" + iconNum;
}
user.IconPath = System.Configuration.ConfigurationManager.AppSettings[0] + "icon" + iconNum + ".png";
Conection.DisConnectDB();
return(user);
}
public static void SendMessage(string inputText, int channelId)
{
if (null == LoginService.User)
{
return;
}
var command = new StringBuilder();
command.Append("insert into t_message( text, channel_id, user_id, time) values ( @text, @channel_id, @user_id, @time);");
MySqlCommand cmd = new MySqlCommand(command.ToString(), Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@text", inputText, MySqlDbType.VarChar, 280));
cmd.Parameters.Add(CreateParameter("@channel_id", channelId, MySqlDbType.Int32, 16));
cmd.Parameters.Add(CreateParameter("@user_id", LoginService.User.Id, MySqlDbType.Int32, 16));
cmd.Parameters.Add(CreateParameter("@time", DateTime.Now, MySqlDbType.DateTime, 16));
cmd.ExecuteNonQuery();
Conection.DisConnectDB();
}
public static int GetMaxMessageIdInChannel(int channelId)
{
var cmd = new MySqlCommand("select max(id)as maxId from t_message where channel_id = @channel_id;", Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@channel_id", channelId, MySqlDbType.Int32, 16));
var reader = cmd.ExecuteReader();
reader.Read();
int value;
value = DBNull.Value != reader["maxId"] ? Convert.ToInt32(reader.GetString("maxId")) : 0;
Conection.DisConnectDB();
return(value);
}
private static void GetChildMessages(Message message)
{
var cmd = new MySqlCommand("select * from t_message where parent_message_id = @parent_message_id order by time;", Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@parent_message_id", message.Id, MySqlDbType.Int32, 16));
var reader = cmd.ExecuteReader();
while (reader.Read())
{
bool isDeleted = DBNull.Value != reader["is_deleted"] ? reader.GetBoolean("is_deleted") : false;
if (isDeleted)
{
continue;
}
var childMessage = new Message();
childMessage.Id = DBNull.Value != reader["Id"] ? Convert.ToInt32(reader.GetString("Id")) : 0;
childMessage.Text = DBNull.Value != reader["text"] ? reader.GetString("text") : "";
childMessage.Time = DateTime.Parse(DBNull.Value != reader["time"] ? reader.GetString("time") : "");
childMessage.UserId = DBNull.Value != reader["user_id"] ? Convert.ToInt32(reader.GetString("user_id")) : 0;
childMessage.ParentId = message.Id;
message.Child.Add(childMessage);
message.ExistChild = System.Windows.Visibility.Visible;
}
Conection.DisConnectDB();
}
public static void MessageList(ObservableCollection <Message> list, int channelId)
{
var cmd = new MySqlCommand("select * from t_message where channel_id = @channel_id order by time;", Conection.ConnectDB());
cmd.Parameters.Add(CreateParameter("@channel_id", channelId, MySqlDbType.Int32, 16));
var reader = cmd.ExecuteReader();
while (reader.Read())
{
bool isDeleted = DBNull.Value != reader["is_deleted"] ? reader.GetBoolean("is_deleted") : false;
if (isDeleted)
{
continue;
}
var message = new Message();
message.ChannelId = channelId;
message.Id = DBNull.Value != reader["Id"] ? Convert.ToInt32(reader.GetString("Id")) : 0;
message.Text = DBNull.Value != reader["text"] ? reader.GetString("text") : "";
message.Time = DateTime.Parse(DBNull.Value != reader["time"] ? reader.GetString("time") : "");
message.UserId = DBNull.Value != reader["user_id"] ? Convert.ToInt32(reader.GetString("user_id")) : 0;
message.ParentId = DBNull.Value != reader["parent_message_id"] ? Convert.ToInt32(reader.GetString("parent_message_id")) : 0;
if (message.ParentId != 0)
{
continue;
}
list.Add(message);
}
Conection.DisConnectDB();
foreach (Message message in list)
{
GetChildMessages(message);
message.UserName = UserDAO.UserName(message.UserId);
int iconId = UserDAO.UserIconId(message.UserId);
string iconNum = iconId.ToString("D2");
message.IconPath = System.Configuration.ConfigurationManager.AppSettings[0] + "icon" + iconNum + ".png";
foreach (Message child in message.Child)
{
int childIconId = UserDAO.UserIconId(child.UserId);
string childIconNum = childIconId.ToString("D2");
child.UserName = UserDAO.UserName(child.UserId);
child.IconPath = System.Configuration.ConfigurationManager.AppSettings[0] + "icon" + childIconNum + ".png";
}
}
}