mysql数据库操作工具类_mysql 操作数据库 工具类

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using MySql.Data.MySqlClient;

using System.Collections;

namespace WcfService1.DBUtility

{

public abstract class MySqlHelper

{

// public static readonly string DBConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();

public static readonly string connectionString = "server=localhost;user id=root; password=root; database=test; pooling=false";

// 用于缓存参数的HASH表

private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

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;

}

}

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;

}

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;

}

///

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

///

/// 数据库连接字符串

/// 命令类型(存储过程或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;

}

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;

}

}

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;

}

}

public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)

{

parmCache[cacheKey] = commandParameters;

}

public static MySqlParameter[] GetCachedParameters(string cacheKey)

{

MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

if (cachedParms == null)

return null;

MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

for (int i = 0, j = cachedParms.Length; i < j; i++)

clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

return clonedParms;

}

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);

}

}

}

}