Tools
DBHelperMySQL.cs
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace Newbe.Mahua.Plugins.iChunqiuQQBoot.Beta.Tools
{
///
/// 数据访问抽象基础类
///
public clast DBHelperMySQL
{
//private readonly String connectionStr = "server=localhost;user id=root;pastword=root;database=ichunqiu;charset=utf8";
///
/// 根据SQL获取DataTable数据表
///
/// 查询语句
/// 返回表的表名
///
public static string GetContent(string sql, List parameter, string content)
{
string resultStr = "";
if (content == "" || "".Equals(content) || content.Length == 0 || content == null)
{
content = "\n【文章】";
}
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
// 添加参数
if (parameter.Count > 0)
{
mySqlCommand.Parameters.AddRange(parameter.ToArray());
}
// 提取数据
using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader())
{
while (myDataReader.Read() == true)
{
string tmpStr = content + myDataReader["satle"] + "\n" + myDataReader["url"] + "\n";
resultStr += tmpStr;
}
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (connection != null)
{
connection.Close();
}
// 关闭数据库链接
ConnectionPool.getPool().closeConnection(connection);
}
return resultStr;
}
///
/// 提取课程信息
///
/// sql
/// 参数信息
///
private static string geClast(string sql, List parameter)
{
string resultStr = "";
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
// 添加参数
if (parameter.Count > 0)
{
mySqlCommand.Parameters.AddRange(parameter.ToArray());
}
// 提取数据
using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader())
{
while (myDataReader.Read() == true)
{
string tmpStr = "\n【课程】" + myDataReader["clast_name"] + "\n" + myDataReader["clast_url"] + "\n";
resultStr += tmpStr;
}
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (connection != null)
{
connection.Close();
}
// 关闭数据库链接
ConnectionPool.getPool().closeConnection(connection);
}
return resultStr;
}
///
/// 判断是否为管理员
///
/// qq号
///
public static bool isAdmin(string qq)
{
bool flag = false;
string sql = "SELECT qq FROM ichunqiu_admin WHERE qq = @qq";
var parameter = new List
{
new MySqlParameter("@qq", qq)
};
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
while (connection == null)
{
}
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
// 添加参数
if (parameter.Count > 0)
{
mySqlCommand.Parameters.AddRange(parameter.ToArray());
}
// 提取数据
using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader())
{
int count = 0;
while (myDataReader.Read() == true)
{
count++;
}
if (count > 0)
{
flag = true;
}
}
}
}
catch (Exception)
{
flag = false;
}
finally
{
if (connection != null)
{
connection.Close();
}
// 关闭数据库链接
ConnectionPool.getPool().closeConnection(connection);
}
return flag;
}
///
/// 根据标题随机获取课程信息
///
///
///
public static string getClastRandomBysatle(string satle)
{
satle = "%" + satle + "%";
var param = new List
{
new MySqlParameter("@satle", satle)
};
string sql = "SELECT clast_name,clast_url FROM ichunqiu_clast WHERE clast_name LIKE @satle ORDER BY rand() LIMIT 0,1";
return geClast(sql, param);
}
///
/// 根据标题获取最新的课程信息
///
/// 标题信息
///
public static string getClastDateBysatle(string satle)
{
satle = "%" + satle + "%";
var param = new List
{
new MySqlParameter("@satle", satle)
};
string sql = "SELECT clast_name,clast_url FROM ichunqiu_clast WHERE clast_name LIKE @satle ORDER BY create_time DESC LIMIT 0,1";
return geClast(sql, param);
}
///
/// 根据作者随机提取课程信息
///
/// 作者名称
///
public static string getClastRandomByAuthor(string author)
{
author = "%" + author + "%";
var param = new List
{
new MySqlParameter("@author_name", author)
};
string sql = "SELECT clast_name,clast_url FROM ichunqiu_clast WHERE author_name LIKE @author_name ORDER BY rand() LIMIT 0,1";
return geClast(sql, param);
}
///
/// 根据作者提取最新的课程信息
///
/// 作者名称
///
public static string getClastDateByAuthor(string author)
{
author = "%" + author + "%";
// author_name
var param = new List
{
new MySqlParameter("@author_name", author)
};
string sql = "SELECT clast_name,clast_url FROM ichunqiu_clast WHERE author_name LIKE @author_name ORDER BY create_time DESC LIMIT 0,1";
return geClast(sql, param);
}
///
/// 通过标题查询文章(随机返回4条)
///
///
public static string GetContentRandomBysatle(string satle)
{
satle = "%" + satle + "%";
string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE content_id IN (SELECT content_id FROM ichunqiu_content WHERE fid = 59 OR (fid = 60) OR (fid = 61) OR (fid = 81 AND type_id = 158) OR (fid = 42 AND type_id = 29) ) AND satle LIKE @satle order by rand() LIMIT 0,4";
var param = new List
{
new MySqlParameter("@satle", satle)
};
return GetContent(sql, param, "\n【文章】");
}
///
/// 通过标题查询文章(最新4条)
///
///
public static string getContentDateBysatle(string satle)
{
satle = "%" + satle + "%";
//string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE satle LIKE @satle AND fid = 59 OR fid = 60 OR fid = 61 order by content_date DESC LIMIT 0,4";
string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE content_id IN (SELECT content_id FROM ichunqiu_content WHERE fid = 59 OR (fid = 60) OR (fid = 61) OR (fid = 81 AND type_id = 158) OR (fid = 42 AND type_id = 29) ) AND satle LIKE @satle order by content_date DESC LIMIT 0,4";
var param = new List();
param.Add(new MySqlParameter("@satle", satle));
return GetContent(sql, param, "\n【文章】");
}
///
/// 通过作者查询文章(最新4条)
///
///
public static string getContentDateByAuthor(string author)
{
author = "%" + author + "%";
string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE author LIKE @author order by content_date DESC LIMIT 0,4";
var param = new List();
param.Add(new MySqlParameter("@author", author));
return GetContent(sql, param, "\n【文章】");
}
///
/// 通过作者查询文章(随机4条)
///
///
public static string getContentRandomByAuthor(string author)
{
author = "%" + author + "%";
string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE author LIKE @author order by rand() DESC LIMIT 0,4";
var param = new List();
param.Add(new MySqlParameter("@author", author));
return GetContent(sql, param, "\n【文章】");
}
///
/// 今日最新||最新帖子
///
///
public static string getContentDateByToday()
{
string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE fid = 59 OR fid = 60 OR fid = 61 OR fid = 81 OR fid = 65 OR fid = 42 OR fid = 76 order by content_date DESC LIMIT 0,4";
var param = new List();
return GetContent(sql, param, "\n【文章】");
}
///
/// 通过标题获取随机教程信息
///
/// 标题
///
public static string getCourseRandomBysatle(string satle)
{
satle = "%" + satle + "%";
//string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE satle LIKE @satle AND fid = 81 OR fid = 42 AND type_id = 158 OR type_id = 29 order by rand() LIMIT 0,4";
string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE content_id IN (SELECT content_id FROM ichunqiu_content WHERE fid = 42 OR fid = 65 ) AND satle LIKE @satle order by rand() LIMIT 0,4";
var param = new List();
param.Add(new MySqlParameter("@satle", satle));
return GetContent(sql, param, "\n【教程】");
}
///
/// 通过标题获取最新教程信息
///
/// 标题
///
public static string getCourseDateBysatle(string satle)
{
satle = "%" + satle + "%";
//string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE satle LIKE @satle AND fid = 81 OR fid = 42 AND type_id = 158 OR type_id = 29 order by content_date DESC LIMIT 0,4";
string sql = "SELECT satle,url,author FROM ichunqiu_content WHERE content_id IN (SELECT content_id FROM ichunqiu_content WHERE fid = 42 OR fid = 65 ) AND satle LIKE @satle order by content_date DESC LIMIT 0,4";
var param = new List();
param.Add(new MySqlParameter("@satle", satle));
return GetContent(sql, param, "\n【教程】");
}
///
/// 通过QQ获取作家的余额信息
///
/// qq号
/// 余额
public static string getBlankMoney(string qq)
{
string sql = "SELECT user_money FROM ichunqiu_blank WHERE user_qq = @qq";
var parameter = new List();
string userMoney = "";
parameter.Add(new MySqlParameter("@qq", qq));
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
while (connection == null)
{
}
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
// 添加参数
if (parameter.Count > 0)
{
mySqlCommand.Parameters.AddRange(parameter.ToArray());
}
// 提取数据
using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader())
{
while (myDataReader.Read() == true)
{
userMoney = myDataReader["user_money"].ToString();
break;
}
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (connection != null)
{
connection.Close();
}
// 关闭数据库链接
ConnectionPool.getPool().closeConnection(connection);
}
return userMoney;
}
///
/// 提现记录
///
/// qq号
/// 提现记录
public static string getBlankHistory(string qq)
{
string sql = "SELECT money,create_date FROM ichunqiu_blank_history WHERE user_qq = @qq AND operation = '0' ;";
var parameter = new List();
string resultStr = "";
parameter.Add(new MySqlParameter("@qq", qq));
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
while (connection == null)
{
}
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
// 添加参数
if (parameter.Count > 0)
{
mySqlCommand.Parameters.AddRange(parameter.ToArray());
}
// 提取数据
using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader())
{
while (myDataReader.Read() == true)
{
resultStr += myDataReader["money"].ToString() + "\t" + myDataReader["create_date"] + "\n";
}
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (connection != null)
{
connection.Close();
}
// 关闭数据库链接
ConnectionPool.getPool().closeConnection(connection);
}
return resultStr;
}
///
/// 金额提现
///
/// QQ号
/// 金额
public static string updateBlank(string qq, decimal money,string operation)
{
string resultStr = "";
string sql = "UPDATE ichunqiu_blank SET user_money = user_money - @user_money WHERE user_qq [email protected] ";
// 加钱
if ("1".Equals(operation))
{
sql = "UPDATE ichunqiu_blank SET user_money = user_money + @user_money WHERE user_qq [email protected] ";
}
else
{
string tmpMoney = getBlankMoney(qq);
if (tmpMoney != null && tmpMoney.Length > 0)
{
if (Convert.ToDecimal(tmpMoney) < money)
{
return "提现金额必须小于存款金额。";
}
}
else
{
return "没有该作家！";
}
}
var parameter = new List();
parameter.Add(new MySqlParameter("@user_money", money));
parameter.Add(new MySqlParameter("@qq", qq));
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
while (connection == null)
{
}
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
// 添加参数
if (parameter.Count > 0)
{
mySqlCommand.Parameters.AddRange(parameter.ToArray());
}
// 提取数据
int count = mySqlCommand.ExecuteNonQuery();
// 执行数据插入
int flagCount = inserMoneyHistory(qq, money, operation);
if (count > 0 && flagCount > 0)
{
resultStr = "更新成功!";
}
else
{
resultStr = "更新失败！";
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
// 关闭数据库链接
ConnectionPool.getPool().closeConnection(connection);
}
return resultStr;
}
///
/// 插入提现记录
///
///
public static int inserMoneyHistory(string qq, decimal money,string operation)
{
string sql = "INSERT INTO ichunqiu_blank_history(id,user_qq,money,operation,create_date,update_date) VALUES(DEFAULT,@qq,@user_money,@operation,NOW(),NOW())";
var parameter = new List();
int count = 0;
parameter.Add(new MySqlParameter("@user_money", money));
parameter.Add(new MySqlParameter("@qq", qq));
parameter.Add(new MySqlParameter("@operation", operation));
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
// 链接为null就执行等待
while (connection == null)
{
}
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
// 添加参数
if (parameter.Count > 0)
{
mySqlCommand.Parameters.AddRange(parameter.ToArray());
}
// 提取数据
count = mySqlCommand.ExecuteNonQuery();
}
}
catch (Exception)
{
count = 0;
}
finally
{
if (connection != null)
{
connection.Close();
}
ConnectionPool.getPool().closeConnection(connection);
}
return count;
}
///
/// 日志记录
///
///
///
public static void addUserData(string qq, string content, string msg)
{
string sql = "INSERT INTO ichunqiu_user(id,user_qq,content,send_msg,create_date,update_date) VALUES(DEFAULT,@qq,@content,@msg,NOW(),NOW())";
var parameter = new List();
parameter.Add(new MySqlParameter("@qq", qq));
parameter.Add(new MySqlParameter("@content", content));
parameter.Add(new MySqlParameter("@msg", msg));
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
// 链接为null就执行等待
while (connection == null)
{
}
using (MySqlCommand cmd = new MySqlCommand(sql,connection))
{
// 添加参数
if (parameter.Count > 0)
{
cmd.Parameters.AddRange(parameter.ToArray());
}
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
ConnectionPool.getPool().closeConnection(connection);
}
}
///
/// 获取今日使用的数据
///
///
public static int getToday()
{
int count = 0;
string sql = "select user_qq from ichunqiu_user where to_days(create_date) = to_days(now()) GROUP BY user_qq;";
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
// 链接为null就执行等待
while (connection == null)
{
}
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader())
{
while (myDataReader.Read() == true)
{
count++;
}
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
ConnectionPool.getPool().closeConnection(connection);
}
return count;
}
///
/// 获取使用总数人
///
///
public static int getUserTotal()
{
int count = 0;
string sql = "SELECT user_qq FROM ichunqiu_user GROUP BY user_qq;";
MySqlConnection connection = null;
try
{
connection = ConnectionPool.getPool().getConnection();
// 链接为null就执行等待
while (connection == null)
{
}
using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection))
{
using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader())
{
while (myDataReader.Read() == true)
{
count++;
}
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
ConnectionPool.getPool().closeConnection(connection);
}
return count;
}
///
/// 获得某一个时间使用的人数
///
///
public static Hashtable getThisWeek()
{
string sql = "select user_qq,DATE_FORMAT(create_date,'%Y-%m-%d') as date from ichunqiu_user where DATE_SUB(CURDATE(), INTERVAL 7 DAY) 