- 先下载c#的Mysql动态链接库,Mysql.data.dll,添加到数据访问层项目中
(找到该项目,里面有单独的一项叫引用,邮件浏览添加) - 方法如下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using System.Data;
//using System.Data.SqlClient;
//以上两句是用于sql server的
//引用App.config 所在的命名空间(当需要把连接字符串保存到windows项目中间接访问时)
using System.Configuration;
//using MySQLDriverCS;
//下面的代码基于这个dll
using MySql.Data;
using MySql.Data.MySqlClient;
namespace DAL
{
/// <summary>
/// 通用数据访问类
/// </summary>
public class SQLHelper
{
//public static string connString = "Database='SMDB';Data Source='localhost';User Id='root';Password='123456';charset='utf8'";
//config类ConfigManager的一个ConnectionStrings方法,将结点name放到[]里加引号
private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();
/// <summary>
/// 测试数据库是否连接成功
/// </summary>
public static bool connectionOK()
{
MySqlConnection conn = new MySqlConnection(connString);
bool flag = false;
try
{
conn.Open();
flag = true;
}
catch (Exception ex)
{
flag = false;
throw ex;
}
finally
{
conn.Close();
}
return flag;
}
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
catch(Exception ex)
{
//日志
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 获取单一结果查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
object result = cmd.ExecuteScalar();
return result;
}
catch(Exception ex)
{
//日志
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回结果集合
/// </summary>
public static MySqlDataReader GetReader(string sql)
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand cmd = new MySqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch(Exception ex)
{
//日志
conn.Close();
throw ex;
}
}
}
}