SqlHelper
目录
预处理
Appconfig中设置连接字符串
<connectionStrings>
<add name="connStr" connectionString="Server =127.0.0.1;Database= school;Uid = root; Pwd = a123456789"/>
</connectionStrings>
构造函数获取SQL连接字符串
string _connStr = string.Empty;
/// <summary>
/// 获取连接MySql连接字符串
/// </summary>
/// <param name="connKey">配置项中连接字符串的键connStr</param>
public MySqlHelper(string connKey)
{
_connStr = ConfigurationManager.ConnectionStrings[connKey].ConnectionString;
}
对数据进行预处理
#region 预处理过程
/// <summary>
/// 预处理
/// </summary>
/// <param name="conn">连接字符串</param>
/// <param name="type">命令类型</param>
/// <param name="cmdText">需要执行的SQL语句</param>
/// <param name="cmd"></param>
/// <param name="trans">SQL事务</param>
/// <param name="parameters">参数化查询</param>
public void Prepare(MySqlConnection conn, CommandType type, string cmdText, MySqlCommand cmd, MySqlTransaction trans, MySqlParameter[] parameters)
{
//判断是否为空
if (string.IsNullOrWhiteSpace(cmdText)) throw new NullReferenceException("执行语句不能为空！");
//判断数据库是否打开
if (conn.State != ConnectionState.Open) conn.Open();
cmd.Connection = conn;
//事务判断
if (trans != null) cmd.Transaction = trans;
//CommandType判断
if (type != CommandType.Text) cmd.CommandType = type;
cmd.CommandText = cmdText;
//参数处理
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
}
#endregion
返回MySqlConnections
#region 返回MySqlConnections
public MySqlConnection GetConnection(string connStr = "")
{
connStr = string.IsNullOrWhiteSpace(connStr) ? _connStr : connStr;
return new MySqlConnection(connStr);
}
#endregion
核心功能
执行非查询语句 ExecuteNonQuey 方式集合
#region 01 执行非查询语句 ExecuteNonQuey 方式集合
/// <summary>
/// ExecuteNonQuey 通过构造函数提供的连接字符串进行操作
/// </summary>
/// <param name="cmdText">需要执行的SQL语句</param>
/// <param name="parameter">参数列表</param>
/// <param name="connStr">连接字符串（可空）为空则使用构造函数中提供的连接字符串</param>
/// <param name="type">执行方式（存储过程 | 语句··）</param>
/// <returns>返回 执行后受影响的行数</returns>
public int ExecuteNonQuey(string cmdText, MySqlParameter[] parameter, string connStr = "", CommandType type = CommandType.Text)
{
//判断connStr是否为空，如果为空则使用配置文件中的连接字符串
connStr = string.IsNullOrWhiteSpace(connStr) ? _connStr : connStr;
using (MySqlConnection conn = new MySqlConnection(connStr))
{
using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
{
//避免多次判断，单独创建预处理过程
Prepare(conn, type, cmdText, cmd, null, parameter);
return cmd.ExecuteNonQuery();
}
}
}
public int ExecuteNonQuey(MySqlConnection conn, string cmdText, MySqlParameter[] parameter, CommandType type = CommandType.Text)
{
using (MySqlCommand cmd = new MySqlCommand())
{
Prepare(conn, type, cmdText, cmd, null, parameter);
return cmd.ExecuteNonQuery();
}
}
public int ExecuteNonQuey(MySqlTransaction trans, string cmdText, MySqlParameter[] parameter, CommandType type = CommandType.Text)
{
using (MySqlCommand cmd = new MySqlCommand())
{
Prepare(trans.Connection, type, cmdText, cmd, trans, parameter);
return cmd.ExecuteNonQuery();
}
}
#endregion
执行标量查询 ExecuteScalar 方式集合
#region 02 执行标量查询 ExecuteScalar 方式集合
/// <summary>
/// ExecuteScalar 标量查询
/// </summary>
/// <param name="cmdText">需要执行的SQL语句</param>
/// <param name="parameter">参数列表</param>
/// <param name="connStr">连接字符串（可空）为空则使用构造函数中提供的连接字符串</param>
/// <param name="type">执行方式（存储过程 | 语句··）</param>
/// <returns>返回 标量结果</returns>
public object ExecuteScalar(string cmdText, MySqlParameter[] parameter, string connStr = "", CommandType type = CommandType.Text)
{
//判断connStr是否为空，如果为空则使用配置文件中的连接字符串
connStr = string.IsNullOrWhiteSpace(connStr) ? _connStr : connStr;
using (MySqlConnection conn = new MySqlConnection(connStr))
{
using (MySqlCommand cmd = new MySqlCommand(cmdText, conn))
{
//避免多次判断，单独创建预处理过程
Prepare(conn, type, cmdText, cmd, null, parameter);
return cmd.ExecuteScalar();
}
}
}
public object ExecuteScalar(MySqlConnection conn, string cmdText, MySqlParameter[] parameter, CommandType type = CommandType.Text)
{
using (MySqlCommand cmd = new MySqlCommand())
{
Prepare(conn, type, cmdText, cmd, null, parameter);
return cmd.ExecuteScalar();
}
}
public object ExecuteScalar(MySqlTransaction trans, string cmdText, MySqlParameter[] parameter, CommandType type = CommandType.Text)
{
using (MySqlCommand cmd = new MySqlCommand())
{
Prepare(trans.Connection, type, cmdText, cmd, trans, parameter);
return cmd.ExecuteScalar();
}
}
#endregion
执行查询语句 DataReader 方式集合
#region 03 执行查询语句 DataReader 方式集合
/// <summary>
/// ExecuteDataReader 查询
/// </summary>
/// <param name="cmdText">需要执行的SQL语句</param>
/// <param name="parameter">参数列表</param>
/// <param name="connStr">连接字符串（可空）为空则使用构造函数中提供的连接字符串</param>
/// <param name="type">执行方式（存储过程 | 语句··）</param>
/// <returns>返回 DataReader</returns>
public MySqlDataReader ExecuteDataReader(string cmdText, MySqlParameter[] parameter, string connStr = "", CommandType type = CommandType.Text)
{
//判断connStr是否为空，如果为空则使用配置文件中的连接字符串
connStr = string.IsNullOrWhiteSpace(connStr) ? _connStr : connStr;
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand cmd = new MySqlCommand();
Prepare(conn, type, cmdText, cmd, null, parameter);
return cmd.ExecuteReader(CommandBehavior.CloseConnection); //CommandBehavior.CloseConnection 当关闭Reader的时候 自动关闭连接
}
public MySqlDataReader ExecuteDataReader(MySqlConnection conn, string cmdText, MySqlParameter[] parameter, CommandType type = CommandType.Text)
{
MySqlCommand cmd = new MySqlCommand();
Prepare(conn, type, cmdText, cmd, null, parameter);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public MySqlDataReader ExecuteDataReader(MySqlTransaction trans, string cmdText, MySqlParameter[] parameter, CommandType type = CommandType.Text)
{
MySqlCommand cmd = new MySqlCommand();
Prepare(trans.Connection, type, cmdText, cmd, trans, parameter);
return cmd.ExecuteReader();
}
#endregion
登峰造极的成就源于自律
