public List <String> selectChatUsers(String userID)
{
List <String> usersID1 = new List <string>();
List <String> usersID2 = new List <string>();
usersID1 = DatabaseTool.ExecSqlReturnItem(String.Format(selectSendUser, userID), "send_id");
usersID2 = DatabaseTool.ExecSqlReturnItem(String.Format(selectReceiveUser, userID), "receive_id");
List <String> result = usersID1.Union(usersID2).ToList <String>();
return(result);
}
public int insertRelease(String goodName, String goodImg, Double price, String address, String category, int bargin, String contact, String newLevel, String detail, String userID)
{
if (DatabaseTool.ExecSql(String.Format(insertSql, category, price, bargin, contact, goodImg, detail, newLevel, userID, goodName, address)))
{
return(1);
}
else
{
return(-1);
}
}
public int deleteGood(int goodID)
{
if (DatabaseTool.ExecSql(String.Format(deleteSql, goodID)))
{
return(1);
}
else
{
return(-1);
}
}
public int insertUser(String phoneNum, String name, String school, String campus, String college, String password, String userID)
{
if (DatabaseTool.ExecSql(String.Format(insertSql, phoneNum, name, school, campus, college, password, userID)))
{
return(1);
}
else
{
return(-1);
}
}
public int updateRead(String myID, String otherID)
{
if (DatabaseTool.ExecSql(String.Format(setRead, myID, otherID)))
{
return(1);
}
else
{
return(-1);
}
}
public int sendMessage(String myID, String otherID, String content)
{
if (DatabaseTool.ExecSql(String.Format(addMessage, myID, otherID, DateTime.Now, content, 0)))
{
return(1);
}
else
{
return(-1);
}
}
public int updateGoodNotPic(String category, Double prize, int bargin, String contact, String description, String new_level, String goodsName, String address, int goodID)
{
//调用数据库语句，更新商品信息,没写呢
if (DatabaseTool.ExecSql(String.Format(updateSql1, category, prize, bargin, contact, description, new_level, goodsName, address, goodID)))
{
return(1);
}
else
{
return(-1);
}
}
public int selectUser(String value, String fieldName)
{
Console.WriteLine(String.Format(fieldName, value));
if (DatabaseTool.ExcelSelectResult(String.Format(selectSql, fieldName, value))) //如果查询到，返回1，否则返回-1
{
return(1);
}
else
{
return(-1);
}
}
public Model.Chat selectRecentMessage(String myID, String otherID)
{
List <Dictionary <String, Object> > sqlResult = DatabaseTool.ExecSqlReturn(String.Format(selectMessage, otherID, myID));
Model.Chat chat = new Model.Chat();
if (sqlResult == null || sqlResult.Count < 1)
{
//如果没找到和这个人的聊天记录，说明这个人可能是该用户在首页点击的chat_id
return(null);
}
else
{
return(Model.Chat.createChat(sqlResult[0]));
}
}
public static List <String> ExecSqlReturnItem(String sql, String item) //返回查找结果集中的某个字段的值，以list形式返回
{
MySqlConnection conn = DatabaseTool.GetSqlConnection();
MySqlCommand command = new MySqlCommand(sql, conn);
MySqlDataReader reader = command.ExecuteReader();
List <String> lst = new List <string>();
while (reader.Read())
{
lst.Add(reader[item].ToString());
}
conn.Close();
reader.Close();
return(lst);
}
public int insertColl(String userID, String goodID)
{
if (DatabaseTool.ExcelSelectResult(String.Format(selectCollection, userID, goodID)))
{
return(-1);
}
else if (DatabaseTool.ExecSql(String.Format(insertCollection, userID, goodID)))
{
return(1);
}
else
{
return(-1);
}
}
public static bool ExecSql(String sql)
{
MySqlConnection conn = DatabaseTool.GetSqlConnection();
MySqlCommand command = new MySqlCommand(sql, conn);
if (command.ExecuteNonQuery() > 0)
{
conn.Close();
return(true);
}
else
{
conn.Close();
return(false);
}
}
public String selectNewMessage(String myID, String otherID, DateTime lastTime)
{
List <Dictionary <String, Object> > sqlResult = DatabaseTool.ExecSqlReturn(String.Format(selectNew, myID, otherID, lastTime));
Model.Chat chat = new Model.Chat();
if (sqlResult == null || sqlResult.Count < 1)
{
//如果没找到和这个人的聊天记录，说明这个人可能是该用户在首页点击的chat_id
return(null);
}
else
{
chat = Model.Chat.createChat(sqlResult[0]);
return(chat.content);
}
}
public List <Model.Release> selectGoodsRandom()
{
List <Dictionary <String, Object> > sqlResult = DatabaseTool.ExecSqlReturn(selectAll);
List <Model.Release> release = new List <Model.Release>();
if (sqlResult == null || sqlResult.Count < 1)
{
return(release);
}
else
{
foreach (Dictionary <String, Object> dic in sqlResult)
{
release.Add(Model.Release.createRelease(dic));
}
return(release);
}
}
public List <Model.Release> selectGoodsByGoodID(int goodsID)
{
List <Dictionary <String, Object> > sqlResult = DatabaseTool.ExecSqlReturn(String.Format(selectByID, goodsID));
List <Model.Release> release = new List <Model.Release>();
if (sqlResult == null || sqlResult.Count < 1)
{
return(release);
}
else
{
foreach (Dictionary <String, Object> dic in sqlResult)
{
release.Add(Model.Release.createRelease(dic));
}
return(release);
}
}
public static bool ExcelSelectResult(String sql) //查询到返回true,查询不到返回false
{
MySqlConnection conn = DatabaseTool.GetSqlConnection();
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
{
conn.Close();
return(false);
}
else
{
conn.Close();
return(true);
}
}
public List <Model.Chat> selectAllMessage(String myID, String otherID)
{
List <Dictionary <String, Object> > sqlResult = DatabaseTool.ExecSqlReturn(String.Format(selectAllMessages, myID, otherID, otherID, myID));
List <Model.Chat> message = new List <Model.Chat>();
if (sqlResult == null || sqlResult.Count < 1)
{
//如果没找到和这个人的聊天记录，说明这个人可能是该用户在首页点击的chat_id
return(null);
}
else
{
foreach (Dictionary <String, Object> dic in sqlResult)
{
message.Add(Model.Chat.createChat(dic));
}
return(message);
}
}
public List <Model.Release> selectGoodsByCategory(String Category)
{
String sql = String.Format(selectByCategory, Category);
List <Dictionary <String, Object> > sqlResult = DatabaseTool.ExecSqlReturn(sql);
List <Model.Release> release = new List <Model.Release>();
if (sqlResult == null || sqlResult.Count < 1)
{
return(release);
}
else
{
foreach (Dictionary <String, Object> dic in sqlResult)
{
release.Add(Model.Release.createRelease(dic));
}
return(release);
}
}
public static List <Dictionary <String, Object> > ExecSqlReturn(String sql) //返回查找结果集的
{
List <Dictionary <String, Object> > list = new List <Dictionary <String, Object> >();
MySqlConnection conn = DatabaseTool.GetSqlConnection();
MySqlCommand command = new MySqlCommand(sql, conn);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary <string, Object> keyValues = new Dictionary <string, Object>();
for (int i = 0; i < reader.FieldCount; i++)
{
keyValues.Add(reader.GetName(i), reader.GetFieldValue <Object>(i));
}
list.Add(keyValues);
}
reader.Close();
conn.Close();
return(list);
}
public bool searchNotRead(String userID)
{
return(DatabaseTool.ExcelSelectResult(String.Format(selectNotRead, userID)));
}