MYSQLDB
using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.Common;
/// <summary> /// Summary description for DbHelper /// </summary> public class DbHelper {
/*********************************************************************************************** * * * * * <!--配置数据库提供程序名称--> * <add key="DbProviderName" value="MySql.Data.MySqlClient"/> * <!--配置数据库链接字段串值--> *<add key="DbConnectionString" value="server=10.21.0.4;User Id=mts2live;password=evil2stm;Persist Security Info=True;database=mts2"/> * * * * * * ************************************************************************************************/
/// <summary> /// 获取当前数据库配置的提供程序名称值DbProviderName /// </summary> public static readonly string DbProviderName = System.Configuration.ConfigurationManager.AppSettings["DbProviderName"];
/// <summary> /// 获取当前数据库配置的连接字符串值DbConnectionString /// </summary> public static readonly string DbConnectionString = System.Configuration.ConfigurationManager.AppSettings["DbConnectionString"];
/// <summary> /// 当前默认配置的数据库提供程序DbProviderFactory /// </summary> private DbProviderFactory _dbFactory = null;
/// <summary> /// 当前数据库链接DbConnection对象 /// </summary> private DbConnection _dbConnection = null;
/// <summary> /// 当前的数据库提供程序 /// </summary> private string _dbProviderName = null;
/// <summary> /// 当前的数据库连接字符串 /// </summary> private string _dbConnectionString = null;
/// <summary> /// 根据配置的数据库提供程序和链接串进行初始化此对象实例 /// </summary> public DbHelper() : this(DbHelper.DbConnectionString, DbHelper.DbProviderName) { }
/// <summary> /// 根据数据库链接串和数据库提供程序名称两个参数进行初始化此对象实例 /// </summary> /// <param name="connectionString">数据库连接配置字符串</param> /// <param name="providerName">数据库提供程序的名称</param> public DbHelper(string connectionString, string providerName) { if (!string.IsNullOrEmpty(providerName)) { this._dbFactory = DbHelper.CreateDbProviderFactory(providerName);//创建默认配置的数据库提供程序 } else { throw new ArgumentNullException("providerName", "数据库提供程序名称参数值不能为空,请在配置文件中配置该项值!"); }
if (!string.IsNullOrEmpty(connectionString)) { this._dbConnection = DbHelper.CreateDbConnection(connectionString, providerName);//创建当前数据库链接对象 } else { throw new ArgumentNullException("connectionString", "数据库链接串参数值不能为空,请在配置文件中配置该项值!"); }
//保存当前连接字符串和数据库提供程序名称 this._dbConnectionString = connectionString; this._dbProviderName = providerName; }
/// <summary> /// 根据配置的数据库提供程序的DbProviderName名称来创建一个数据库配置的提供程序DbProviderFactory对象 /// </summary> private static DbProviderFactory CreateDbProviderFactory() { DbProviderFactory dbFactory = DbHelper.CreateDbProviderFactory(DbHelper.DbProviderName);
return dbFactory; }
/// <summary> /// 根据参数名称创建一个数据库提供程序DbProviderFactory对象 /// </summary> /// <param name="dbProviderName">数据库提供程序的名称</param> private static DbProviderFactory CreateDbProviderFactory(string dbProviderName) { DbProviderFactory dbFactory = DbProviderFactories.GetFactory(dbProviderName);
return dbFactory; }
/// <summary> /// 根据配置的数据库提供程序和链接串来创建数据库链接. /// </summary> private static DbConnection CreateDbConnection() { DbConnection dbConn = DbHelper.CreateDbConnection(DbHelper.DbConnectionString, DbHelper.DbProviderName);
return dbConn; }
/// <summary> /// 根据数据库连接字符串参数来创建数据库链接. /// </summary> /// <param name="connectionString">数据库连接配置字符串</param> /// <param name="dbProviderName">数据库提供程序的名称</param> /// <returns></returns> private static DbConnection CreateDbConnection(string connectionString, string dbProviderName) {
DbProviderFactory dbFactory = DbHelper.CreateDbProviderFactory(dbProviderName);
DbConnection dbConn = dbFactory.CreateConnection(); dbConn.ConnectionString = connectionString;
return dbConn; }
/// <summary> /// 执行相应的SQL命令，返回一个DataSet数据集合 /// </summary> /// <param name="sqlQuery">需要执行的SQL语句</param> /// <returns>返回一个DataSet数据集合</returns> public DataSet ExecuteDataSet(string sqlQuery) { DataSet ds = new DataSet();
if (!string.IsNullOrEmpty(sqlQuery)) { DbCommand cmd = GetSqlStringCommond(sqlQuery);
ds = ExecuteDataSet(cmd); }
return ds; }
/// <summary> /// 执行相应的SQL命令，返回一个DataTable数据集 /// </summary> /// <param name="sqlQuery">需要执行的SQL语句</param> /// <returns>返回一个DataTable数据集</returns> public DataTable ExecuteDataTable(string sqlQuery) { DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(sqlQuery)) { DbCommand cmd = GetSqlStringCommond(sqlQuery);
dt = ExecuteDataTable(cmd); }
return dt; }
/// <summary> /// 执行相应的SQL命令，返回一个DbDataReader数据对象，如果没有则返回null值 /// </summary> /// <param name="sqlQuery">需要执行的SQL命令</param> /// <returns>返回一个DbDataReader数据对象，如果没有则返回null值</returns> public DbDataReader ExecuteReader(string sqlQuery) { if (!string.IsNullOrEmpty(sqlQuery)) { DbCommand cmd = GetSqlStringCommond(sqlQuery);
DbDataReader reader = ExecuteReader(cmd);
return reader; }
return null; }
/// <summary> /// 执行相应的SQL命令，返回影响的数据记录数，如果不成功则返回-1 /// </summary> /// <param name="sqlQuery">需要执行的SQL命令</param> /// <returns>返回影响的数据记录数，如果不成功则返回-1</returns> public int ExecuteNonQuery(string sqlQuery) { if (!string.IsNullOrEmpty(sqlQuery)) { DbCommand cmd = GetSqlStringCommond(sqlQuery);
int retVal = ExecuteNonQuery(cmd);
return retVal; }
return -1; }
/// <summary> /// 执行相应的SQL命令，返回结果集中的第一行第一列的值，如果不成功则返回null值 /// </summary> /// <param name="sqlQuery">需要执行的SQL命令</param> /// <returns>返回结果集中的第一行第一列的值，如果不成功则返回null值</returns> public object ExecuteScalar(string sqlQuery) { if (!string.IsNullOrEmpty(sqlQuery)) { DbCommand cmd = GetSqlStringCommond(sqlQuery);
object retVal = ExecuteScalar(cmd);
return retVal; }
return null; } /// <summary> /// 根据SQL语句来构建当前数据库链接的DbCommand对象 /// </summary> /// <param name="sqlQuery">SQL查询语句</param> private DbCommand GetSqlStringCommond(string sqlQuery) { DbCommand dbCmd = this._dbConnection.CreateCommand();
dbCmd.CommandText = sqlQuery; dbCmd.CommandType = CommandType.Text;
return dbCmd; } /// <summary> /// 执行相应的命令，返回结果集中的第一行第一列的值，如果不成功则返回null值 /// </summary> /// <param name="cmd">需要执行的DbCommand命令对象</param> /// <returns>返回结果集中的第一行第一列的值，如果不成功则返回null值</returns> private object ExecuteScalar(DbCommand cmd) { if (cmd != null && cmd.Connection != null) { if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
object retVal = cmd.ExecuteScalar();
cmd.Connection.Close();
return retVal; }
return null; }
/// <summary> /// 执行相应的命令，返回一个DataSet数据集合 /// </summary> /// <param name="cmd">需要执行的DbCommand命令对象</param> /// <returns>返回一个DataSet数据集合</returns> private DataSet ExecuteDataSet(DbCommand cmd) { DataSet ds = new DataSet();
if (cmd != null) { DbDataAdapter dbDataAdapter = this._dbFactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd;
dbDataAdapter.Fill(ds); }
return ds; }
/// <summary> /// 执行相应的命令，返回一个DataTable数据集合 /// </summary> /// <param name="cmd">需要执行的DbCommand命令对象</param> /// <returns>返回一个DataTable数据集合</returns> private DataTable ExecuteDataTable(DbCommand cmd) { DataTable dataTable = new DataTable();
if (cmd != null) { DbDataAdapter dbDataAdapter = this._dbFactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = cmd;
dbDataAdapter.Fill(dataTable); }
return dataTable; }
/// <summary> /// 执行相应的命令，返回一个DbDataReader数据对象，如果没有则返回null值 /// </summary> /// <param name="cmd">需要执行的DbCommand命令对象</param> /// <returns>返回一个DbDataReader数据对象，如果没有则返回null值</returns> private DbDataReader ExecuteReader(DbCommand cmd) { if (cmd != null && cmd.Connection != null) { if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//当reader读取结束时自动关闭数据库链接
return reader; }
return null; }
/// <summary> /// 执行相应的命令，返回影响的数据记录数，如果不成功则返回-1 /// </summary> /// <param name="cmd">需要执行的DbCommand命令对象</param> /// <returns>返回影响的数据记录数，如果不成功则返回-1</returns> private int ExecuteNonQuery(DbCommand cmd) { if (cmd != null && cmd.Connection != null) { if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
int retVal = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return retVal; }
return -1; } }
