public Messages loadMessagesByUser(int ID)
{
SqlConnection loadConnection = new SqlConnection("server=tf-PC\\SQLEXPRESS;database=blogData;uid=admin;pwd=s1y2x3");
loadConnection.Open();
string loadArticleString = "SELECT * FROM Messages WHERE ID=@msgID";
SqlCommand loadCommand1 = new SqlCommand(loadArticleString, loadConnection);
loadCommand1.Parameters.Add(new SqlParameter("msgID", SqlDbType.Int));
loadCommand1.Parameters["msgID"].Value = ID;
SqlDataAdapter loadAdapter = new SqlDataAdapter(loadCommand1);
DataSet loadDataSet = new DataSet();
loadAdapter.Fill(loadDataSet, "Messages");
Messages message = new Messages()
{
ID = Convert.ToInt32(loadDataSet.Tables["Articles"].Rows[0]["ID"]),
receiverID = Convert.ToInt32(loadDataSet.Tables["Messages"].Rows[0]["receiverID"]),
senderID = Convert.ToInt32(loadDataSet.Tables["Messages"].Rows[0]["senderID"]),
content = loadDataSet.Tables["Messages"].Rows[0]["content"].ToString(),
sendtime = Convert.ToDateTime(loadDataSet.Tables["Messages"].Rows[0]["sendtime"])
};
return message;
}
public bool sendMessage(Messages newMessage)
{
bool flag;
SqlConnection insertConnection = new SqlConnection("server=tf-PC\\SQLEXPRESS;database=blogData;uid=admin;pwd=s1y2x3");
insertConnection.Open();
SqlTransaction trans = insertConnection.BeginTransaction();
string insertString = "INSERT INTO Messages (senderID,receiverID,[content],sendtime) VALUES (@senderID,@receiverID,@content,@sendtime)";
SqlCommand insertCommand = new SqlCommand(insertString, insertConnection);
insertCommand.Transaction = trans;
try
{
insertCommand.Parameters.Add(new SqlParameter("senderID", SqlDbType.Int));
insertCommand.Parameters["senderID"].Value = newMessage.senderID;
insertCommand.Parameters.Add(new SqlParameter("receiverID", SqlDbType.Int));
insertCommand.Parameters["receiverID"].Value = newMessage.receiverID;
insertCommand.Parameters.Add(new SqlParameter("content", SqlDbType.NVarChar));
insertCommand.Parameters["content"].Value = newMessage.content;
insertCommand.Parameters.Add(new SqlParameter("sendtime", SqlDbType.DateTime));
insertCommand.Parameters["sendtime"].Value = newMessage.sendtime;
insertCommand.ExecuteNonQuery();
trans.Commit();
flag = true;
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
flag = false;
}
finally
{
insertConnection.Close();
}
return flag;
}
public IEnumerable<Messages> getAllMessagesByUser(int userID)
{
SqlConnection loadConnection = new SqlConnection("server=tf-PC\\SQLEXPRESS;database=blogData;uid=admin;pwd=s1y2x3");
loadConnection.Open();
string loadString = "SELECT * FROM Messages WHERE receiverID=@userID ";
SqlCommand loadCommand = new SqlCommand(loadString, loadConnection);
loadCommand.Parameters.Add(new SqlParameter("userID", SqlDbType.Int));
loadCommand.Parameters["userID"].Value = userID;
SqlDataAdapter loadAdapter = new SqlDataAdapter(loadCommand);
DataSet loadDataSet = new DataSet();
loadAdapter.Fill(loadDataSet, "Messages");
Messages[] messages = new Messages[loadDataSet.Tables["Messages"].Rows.Count];
for (int i = 0; i < loadDataSet.Tables["Messages"].Rows.Count; i++)
{
messages[i] = new Messages()
{
ID = Convert.ToInt32(loadDataSet.Tables["Messages"].Rows[i]["ID"]),
receiverID = Convert.ToInt32(loadDataSet.Tables["Messages"].Rows[i]["receiverID"]),
senderID = Convert.ToInt32(loadDataSet.Tables["Messages"].Rows[i]["senderID"]),
content = loadDataSet.Tables["Messages"].Rows[i]["content"].ToString(),
sendtime = Convert.ToDateTime(loadDataSet.Tables["Messages"].Rows[i]["sendtime"])
};
}
return messages;
}