csharp/0nise/ichunqiu_qqbot/Newbe.Mahua.Plugins.iChunqiuQQBot/Newbe.Mahua.Plugins.iChunqiuQQBoot.Beta/Tools/DBHelperMySQL.cs

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)