C# mysql helper

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
 
namespace GameManager
{
  public  class SqlHelper
    {
 
 
        public static string connstr = "server=ip;port=3306;user=root;password=root; database=jbgamedb;SslMode=None;Allow User Variables=True;Charset=utf8;";
 
 
        /// <summary>
        /// 封装一个执行的sql 返回受影响的行数
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlText, params MySqlParameter[] parameters)
        {
 
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
 
                using (MySqlCommand cmd = conn.CreateCommand())
                {
 
                    conn.Open();
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
 
                }
 
            }
 
        }
 
        /// <summary>
        /// 执行sql,返回查询结果中的第一行第一列的值
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlText, params MySqlParameter[] parameters)
        {
 
            using (MySqlConnection conn = new MySqlConnection(connstr))
            {
 
                using (MySqlCommand cmd = conn.CreateCommand())
                {
 
                    conn.Open();
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
 
                }
 
            }
 
        }
 
        /// <summary>
        /// 执行sql 返回一个DataTable
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sqlText, params MySqlParameter[] parameters)
        {
 
            using (MySqlDataAdapter adapter = new MySqlDataAdapter(sqlText, connstr))
            {
 
                DataTable dt = new DataTable();
                adapter.SelectCommand.Parameters.AddRange(parameters);
                adapter.Fill(dt);
                return dt;
 
            }
 
        }
 
        /// <summary>
        /// 执行sql脚本
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecuteReader(string sqlText, params MySqlParameter[] parameters)
        {
 
            MySqlConnection conn = new MySqlConnection(connstr);
            MySqlCommand cmd = conn.CreateCommand();
            conn.Open();
            cmd.CommandText = sqlText;
            cmd.Parameters.AddRange(parameters);
 
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
        }
 
    }
}

使用方法：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
 
 
namespace GameManager
{
    public class gameinfoService
    {
 
        /// <summary>
        ///  添加
        /// </summary>
        /// <param name="gameinfo"></param>
        /// <returns></returns>
        public bool AddGameInfo(gameinfo gameinfo) {
 
            try
            {
                string sql = "insert into gameinfo(name,info, code, url,admin,zpurl,zpadmin,time,picurl,pinyin) values(@name, @info, @code, @url,@admin,@zpurl,@zpadmin,Now(),@picurl,@pinyin)";
 
                MySqlParameter[] sqlParameter = new MySqlParameter[] {
 
                new MySqlParameter("name",gameinfo.name),
                new MySqlParameter("info",gameinfo.info),
                new MySqlParameter("code",gameinfo.code),
                new MySqlParameter("url",gameinfo.url),
                new MySqlParameter("admin",gameinfo.admin),
                new MySqlParameter("zpurl",gameinfo.zpurl),
                new MySqlParameter("zpadmin",gameinfo.zpadmin),
                new MySqlParameter("picurl",gameinfo.picurl),
                new MySqlParameter("pinyin",gameinfo.pinyin)
 
               
               };
 
                int x = SqlHelper.ExecuteNonQuery(sql, sqlParameter);
 
                return x >= 1;
 
            }
            catch (Exception ex)
            {
 
                Console.WriteLine(ex);
 
            }
 
            return false;
 
        }
 
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool deleteGameInfo(string  id) {
 
            try
            {
                string sql = "delete from gameinfo where id=@id";
 
                MySqlParameter[] sqlParameter = new MySqlParameter[] {
 
                new MySqlParameter("id",id),
 
               };
 
                return SqlHelper.ExecuteNonQuery(sql, sqlParameter)>=1;
 
 
 
            }
            catch (Exception ex)
            {
 
                Console.WriteLine(ex);
            }
 
            return false;
 
        }
 
 
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="gameinfo"></param>
        /// <returns></returns>
        public bool UpdataGameInfo(gameinfo gameinfo) {
 
            try
            {
                string sql = "update gameinfo set name=@name,info=@info,code=@code,url=@url,admin=@admin,zpurl=@zpurl,zpadmin=@zpadmin,time=Now(),picurl=@picurl,pinyin=@pinyin where id=@id";
 
                MySqlParameter[] sqlParameter = new MySqlParameter[] {
 
                new MySqlParameter("name",gameinfo.name),
                new MySqlParameter("info",gameinfo.info),
                new MySqlParameter("code",gameinfo.code),
                new MySqlParameter("url",gameinfo.url),
                new MySqlParameter("admin",gameinfo.admin),
                new MySqlParameter("zpurl",gameinfo.zpurl),
                new MySqlParameter("zpadmin",gameinfo.zpadmin),
                new MySqlParameter("picurl",gameinfo.picurl),
                new MySqlParameter("id",gameinfo.id),
                new MySqlParameter("pinyin",gameinfo.pinyin)
 
 
               };
 
                return  SqlHelper.ExecuteNonQuery(sql,sqlParameter)>=1;
 
            }
            catch (Exception ex)
            {
 
                Console.WriteLine(ex);
            }
 
            return false;
         }
 
 
        /// <summary>
        /// 查找
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public gameinfo GetGameInfo(string id) {
 
            try
            {
                string sql = "select * from gameinfo where id=@id";
 
                MySqlParameter[] sqlParameters = new MySqlParameter[] {
 
                    new MySqlParameter("id",id)
 
                };
 
                DataTable x = SqlHelper.ExecuteDataTable(sql, sqlParameters);
 
                gameinfo gameinfo = null;
 
                if (x.Rows.Count > 0) {
 
                    gameinfo = new gameinfo()
                    {
                        id = Convert.ToInt32(x.Rows[0]["id"]),
                        name = x.Rows[0]["name"].ToString(),
                        info = x.Rows[0]["info"].ToString(),
                        code = x.Rows[0]["code"].ToString(),
                        url = x.Rows[0]["url"].ToString(),
                        admin = x.Rows[0]["admin"].ToString(),
                        zpurl = x.Rows[0]["zpurl"].ToString(),
                        zpadmin = x.Rows[0]["zpadmin"].ToString(),
                        time = Convert.ToDateTime(x.Rows[0]["time"]),
                        picurl = x.Rows[0]["picurl"].ToString(),
                        pinyin=x.Rows[0]["pinyin"].ToString()
 
                    };
 
                }
 
                x.Dispose();
 
                return gameinfo;
 
         
 
            }
            catch (Exception ex)
            {
 
                Console.WriteLine(ex);
            }
 
            return null;
 
        }
 
        /// <summary>
        /// 获取所有游戏信息
        /// </summary>
        /// <returns></returns>
        public List<gameinfo> GetGameInfoList() {
 
 
            try
            {
                string sql = "select * from gameinfo";
 
                MySqlParameter[] mySqlParameters = new MySqlParameter[] {
 
                };
 
                MySqlDataReader reader = SqlHelper.ExecuteReader(sql, mySqlParameters);
 
                List<gameinfo> gameinfos = new List<gameinfo>();
 
                while (reader.Read())
                {
 
                    gameinfo  gameinfo = new gameinfo()
                    {
                        id = Convert.ToInt32(reader["id"].ToString()),
                        name = reader["name"].ToString(),
                        info = reader["info"].ToString(),
                        code = reader["code"].ToString(),
                        url = reader["url"].ToString(),
                        admin = reader["admin"].ToString(),
                        zpurl = reader["zpurl"].ToString(),
                        zpadmin = reader["zpadmin"].ToString(),  
                        time = Convert.ToDateTime(reader["time"]),
                        picurl = reader["picurl"].ToString(),
                        pinyin = reader["pinyin"].ToString()
 
                    };
 
                
                    gameinfos.Add(gameinfo);
 
 
                }
 
                reader.Dispose();
 
                return gameinfos;
 
 
 
            }
            catch (Exception ex)
            {
 
                Console.WriteLine(ex);
            }
 
            return null;
 
 
 
        }
 
 
 
 
 
 
 
    }
}