c dbhelper类下载mysql_C# MySQL DBHelper帮助类

下载：Download Connector/Net

DBHelper类

using System;

using System.Configuration;

using System.Data;

using MySql.Data.MySqlClient;

using RecordingSystem.Common;

namespace MySQLDemo

{

public class DBHelper : IDisposable

{

private static string dbConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

private MySqlConnection connection;

public DBHelper()

{

try

{

connection = CreateConnection(dbConnectionString);

}

catch (Exception ex)

{

EventsLog.WriteLog("ErrorInfo" + ex.Message);

}

}

public DBHelper(string connectionString)

{

this.connection = CreateConnection(connectionString);

}

public static MySqlConnection CreateConnection()

{

MySqlConnection dbconn = new MySqlConnection();

dbconn.ConnectionString = DBHelper.dbConnectionString;

return dbconn;

}

public static MySqlConnection CreateConnection(string connectionString)

{

MySqlConnection dbconn = new MySqlConnection();

dbconn.ConnectionString = connectionString;

return dbconn;

}

public MySqlCommand GetStoredProcCommond(string storedProcedure)

{

MySqlCommand MySqlCommand = connection.CreateCommand();

MySqlCommand.CommandText = storedProcedure;

MySqlCommand.CommandType = CommandType.StoredProcedure;

return MySqlCommand;

}

public MySqlCommand GetSqlStringCommond(string sqlQuery)

{

MySqlCommand MySqlCommand = connection.CreateCommand();

MySqlCommand.CommandText = sqlQuery;

MySqlCommand.CommandType = CommandType.Text;

return MySqlCommand;

}

//增加参数#region 增加参数

#region

public void AddParameterCollection(MySqlCommand cmd, MySqlParameterCollection SqlParameterCollection)

{

foreach (MySqlParameter para in SqlParameterCollection)

{

cmd.Parameters.Add(para);

}

}

public void AddOutParameter(MySqlCommand cmd, string parameterName, DbType dbType, int size)

{

MySqlParameter MySqlParameter = cmd.CreateParameter();

MySqlParameter.DbType = dbType;

MySqlParameter.ParameterName = parameterName;

MySqlParameter.Size = size;

MySqlParameter.Direction = ParameterDirection.Output;

cmd.Parameters.Add(MySqlParameter);

}

public void AddInParameter(MySqlCommand cmd, string parameterName, DbType dbType, object value)

{

MySqlParameter MySqlParameter = cmd.CreateParameter();

MySqlParameter.DbType = dbType;

MySqlParameter.ParameterName = parameterName;

MySqlParameter.Value = value;

MySqlParameter.Direction = ParameterDirection.Input;

cmd.Parameters.Add(MySqlParameter);

}

public void AddInParameter(MySqlCommand cmd, string parameterName, T value)

{

var dbParameter = cmd.CreateParameter();

dbParameter.DbType = DbTypeConvert.TypeToDbType(typeof(T));

dbParameter.ParameterName = parameterName;

dbParameter.Value = value;

dbParameter.Direction = ParameterDirection.Input;

cmd.Parameters.Add(dbParameter);

}

public void AddInParameter(MySqlCommand cmd, string parameterName, DbType dbType)

{

MySqlParameter MySqlParameter = cmd.CreateParameter();

MySqlParameter.DbType = dbType;

MySqlParameter.ParameterName = parameterName;

MySqlParameter.Direction = ParameterDirection.Input;

cmd.Parameters.Add(MySqlParameter);

}

public void AddReturnParameter(MySqlCommand cmd, string parameterName, DbType dbType)

{

MySqlParameter MySqlParameter = cmd.CreateParameter();

MySqlParameter.DbType = dbType;

MySqlParameter.ParameterName = parameterName;

MySqlParameter.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(MySqlParameter);

}

public MySqlParameter GetParameter(MySqlCommand cmd, string parameterName)

{

return cmd.Parameters[parameterName];

}

#endregion

//执行#region 执行

#region

public DataSet ExecuteDataSet(MySqlCommand cmd)

{

//DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);

MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter();

MySqlDataAdapter.SelectCommand = cmd;

DataSet ds = new DataSet();

MySqlDataAdapter.Fill(ds);

return ds;

}

public DataTable ExecuteDataTable(MySqlCommand cmd)

{

//DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);

MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter();

MySqlDataAdapter.SelectCommand = cmd;

DataTable dataTable = new DataTable();

MySqlDataAdapter.Fill(dataTable);

return dataTable;

}

public MySqlDataReader ExecuteReader(MySqlCommand cmd)

{

try

{

cmd.Connection.Open();

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

return reader;

}

catch

{

cmd.Connection.Close();

throw;

}

}

public int ExecuteNonQuery(MySqlCommand cmd)

{

try

{

cmd.Connection.Open();

int ret = cmd.ExecuteNonQuery();

return ret;

}

catch

{

throw;

}

finally

{

cmd.Connection.Close();

}

}

public object ExecuteScalar(MySqlCommand cmd)

{

cmd.Connection.Open();

object ret = cmd.ExecuteScalar();

cmd.Connection.Close();

return ret;

}

#endregion

//执行事务#region 执行事务

#region

public DataSet ExecuteDataSet(MySqlCommand cmd, Trans t)

{

cmd.Connection = t.DbConnection;

cmd.Transaction = t.DbTrans;

//DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);

MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter();

MySqlDataAdapter.SelectCommand = cmd;

DataSet ds = new DataSet();

MySqlDataAdapter.Fill(ds);

return ds;

}

public DataTable ExecuteDataTable(MySqlCommand cmd, Trans t)

{

cmd.Connection = t.DbConnection;

cmd.Transaction = t.DbTrans;

//DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DBHelper.dbProviderName);

MySqlDataAdapter MySqlDataAdapter = new MySqlDataAdapter();

MySqlDataAdapter.SelectCommand = cmd;

DataTable dataTable = new DataTable();

MySqlDataAdapter.Fill(dataTable);

return dataTable;

}

public MySqlDataReader ExecuteReader(MySqlCommand cmd, Trans t)

{

cmd.Connection.Close();

cmd.Connection = t.DbConnection;

cmd.Transaction = t.DbTrans;

MySqlDataReader reader = cmd.ExecuteReader();

DataTable dt = new DataTable();

return reader;

}

public int ExecuteNonQuery(MySqlCommand cmd, Trans t)

{

cmd.Connection.Close();

cmd.Connection = t.DbConnection;

cmd.Transaction = t.DbTrans;

int ret = cmd.ExecuteNonQuery();

return ret;

}

public object ExecuteScalar(MySqlCommand cmd, Trans t)

{

cmd.Connection.Close();

cmd.Connection = t.DbConnection;

cmd.Transaction = t.DbTrans;

object ret = cmd.ExecuteScalar();

return ret;

}

#endregion

#region IDisposable Members

public void Dispose()

{

if (this.connection.State != ConnectionState.Closed)

{

this.connection.Close();

}

this.connection.Dispose();

}

#endregion

}

public class Trans : IDisposable

{

private MySqlConnection conn;

private MySqlTransaction dbTrans;

public MySqlConnection DbConnection

{

get { return this.conn; }

}

public MySqlTransaction DbTrans

{

get { return this.dbTrans; }

}

public Trans()

{

conn = DBHelper.CreateConnection();

conn.Open();

dbTrans = conn.BeginTransaction();

}

public Trans(string connectionString)

{

conn = DBHelper.CreateConnection(connectionString);

conn.Open();

dbTrans = conn.BeginTransaction();

}

public void Commit()

{

dbTrans.Commit();

this.Colse();

}

public void RollBack()

{

dbTrans.Rollback();

this.Colse();

}

public void Dispose()

{

this.Colse();

}

public void Colse()

{

if (conn.State == System.Data.ConnectionState.Open)

{

conn.Close();

}

}

}

}