基于mysql的数据层基类_MySqlHelper

using System;

using System.Data;

using MySql.Data;

using MySql.Data.MySqlClient;

namespace DBUtility

{

///

/// 基于MySQL的数据层基类

///

public abstract class MySqlHelper

{

#region PrepareCommand

///

/// Command预处理

///

/// MySqlConnection对象

/// MySqlTransaction对象,可为null

/// MySqlCommand对象

/// CommandType,存储过程或命令行

/// SQL语句或存储过程名

/// MySqlCommand参数数组,可为null

private static void PrepareCommand(MySqlConnection conn, MySqlTransaction trans, MySqlCommand cmd, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

if (trans != null)

cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)

{

foreach (MySqlParameter parm in cmdParms)

cmd.Parameters.Add(parm);

}

}

#endregion

#region ExecuteNonQuery

///

/// 执行命令

///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回受引响的记录行数

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)

{

MySqlCommand cmd = new MySqlCommand();

using (MySqlConnection conn = new MySqlConnection(connectionString))

{

PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

}

///

/// 执行命令

///

/// Connection对象

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回受引响的记录行数

public static int ExecuteNonQuery(MySqlConnection conn, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)

{

MySqlCommand cmd = new MySqlCommand();

PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

///

/// 执行事务

///

/// MySqlTransaction对象

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回受引响的记录行数

public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)

{

MySqlCommand cmd = new MySqlCommand();

PrepareCommand(trans.Connection, trans, cmd, cmdType, cmdText, cmdParms);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

#endregion

#region ExecuteScalar

///

/// 执行命令,返回第一行第一列的值

///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回Object对象

public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)

{

MySqlCommand cmd = new MySqlCommand();

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

PrepareCommand(connection, null, cmd, cmdType, cmdText, cmdParms);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

}

///

/// 执行命令,返回第一行第一列的值

///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

/// 返回Object对象

public static object ExecuteScalar(MySqlConnection conn, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)

{

MySqlCommand cmd = new MySqlCommand();

PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

#endregion

#region ExecuteReader

///

/// 执行命令或存储过程,返回MySqlDataReader对象

/// 注意MySqlDataReader对象使用完后必须Close以释放MySqlConnection资源

///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组

///

public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)

{

MySqlCommand cmd = new MySqlCommand();

MySqlConnection conn = new MySqlConnection(connectionString);

try

{

PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);

MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();

return dr;

}

catch

{

conn.Close();

throw;

}

}

#endregion

#region ExecuteDataSet

///

/// 执行命令或存储过程,返回DataSet对象

///

/// 数据库连接字符串

/// 命令类型(存储过程或SQL语句)

/// SQL语句或存储过程名

/// MySqlCommand参数数组(可为null值)

///

public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] cmdParms)

{

MySqlCommand cmd = new MySqlCommand();

using (MySqlConnection conn = new MySqlConnection(connectionString))

{

PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);

MySqlDataAdapter da = new MySqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

conn.Close();

cmd.Parameters.Clear();

return ds;

}

}

#endregion

}

}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值