C#MysqlHelperMysql通用数据访问类

C#MysqlHelperMysql通用数据访问类

C#MysqlHelperMysql通用数据访问类

作者 ：1442235813欢快课堂 添加QQ群
【官方QQ一群-C#MysqlHelperMysql通用数据访问类 】：1044140462
【官方QQ二群-ASP.NET MVC】：707334855

【官方QQ三群-三层架构】：707334855

【官方QQ四群-数据库】：1044140462
【官方QQ五群-工控上位机】：707334855
【官方QQ六群-WPF DevExpress】：1044140462

【腾讯课堂主页】：https://ke.qq.com/course/3102236?tuin=55f6c5a5

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.IO;
using System.Net;
using Common;

namespace DBUtility
{
public class MYSQLHelper
{
// public static string connString = System.Configuration.ConfigurationSettings.AppSettings[“databasestring”];
public static string connString = “User Id = root; Password=123456;Host=127.0.0.1;Database=studentmanager;Charset=gb2312”;

public static int Update(string sql)
{

MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string info = “调用 public static int Update(string sql)出现错误”;
WriteLog(ex.Message);
throw new Exception(info);
}
finally
{
conn.Close();

}
}
#region 单一结果
public static object SingleResult(string sql)
{

MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
string info = “调用 public static object SingleResult(string sql)出现错误”;
WriteLog(ex.Message);
throw new Exception(info);
}
finally
{
conn.Close();

}
}
#endregion
#region 查询
public static MySqlDataReader getReader(string sql)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
string info = “调用public static MySqlDataReader getReader(string sql)出现错误”;
WriteLog(ex.Message);
throw new Exception(info);
}

}
#endregion
#region 编写带参数的sql语句的方法
public static int Update(string sql, MySqlParameter[] param)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string info = “调用 public static int Update(string sql, MySqlParameter[] param)出现错误”;
WriteLog(ex.Message);
throw new Exception(info);
}
finally
{
conn.Close();

}
}
#endregion
#region 单一结果
public static object SingleResult(string sql, MySqlParameter[] param)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
string info = “调用public static object SingleResult(string sql, MySqlParameter[] param)”;
WriteLog(ex.Message);
throw new Exception(info);
}
finally
{
conn.Close();

}
}
#endregion
#region 查询
public static MySqlDataReader getReader(string sql, MySqlParameter[] param)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
string info = “调用public static MySqlDataReader getReader(string sql, MySqlParameter[] param)出现错误”;
WriteLog(ex.Message);
throw new Exception(info);
}

}
#endregion
#region 存储过程

public static int UpdateByProcedure(string spname, MySqlParameter[] param)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(spname, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string info = “调用 UpdateByProcedure出现错误”;
WriteLog(ex.Message);
throw new Exception(info);
}
finally
{
conn.Close();

}
}
#endregion
#region
public static object SingleResultByProcedure(string spname, MySqlParameter[] param)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(spname, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
string info = “调用public static object SingleResult(string sql, MySqlParameter[] param)”;
WriteLog(ex.Message);
throw new Exception(info);
}
finally
{
conn.Close();

}
}
#endregion
#region 查询
public static MySqlDataReader getReaderByProcedure(string spname, MySqlParameter[] param)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(spname, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
cmd.CommandType = CommandType.StoredProcedure;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
string info = “调用public static MySqlDataReader getReader(string sql, MySqlParameter[] param)出现错误”;
WriteLog(ex.Message);
throw new Exception(info);
}

}
#endregion
///
/// 系统日志
///
///
public static void WriteLog(string log)
{
FileStream fs = new FileStream(“mysqlhelp.log”, FileMode.Append);
StreamWriter sw = new StreamWriter(fs);
sw.WriteLine(DateTime.Now.ToString() + “错误信息：” + log);
sw.Close();
fs.Close();
}
}
}