全能数据库操作类及调用示例代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】全能数据库操作类及调用示例
作者:Dezai.CN / 发布于2013/5/12/ 801

	using System;
	using System.Data;
	using System.Data.Common;
	using System.Configuration;
	namespace MSCL
	{
	 /// <summary>
	 /// DbHelper通用数据库类
	 /// </summary>
	 public class DbHelper
	 {
	 /// <summary>
	 /// <add key="DbHelperProvider" value="System.Data.SqlClient"/>
	 /// </summary>
	 private static string dbProviderName = ConfigurationManager.AppSettings["DbHelperProvider"];

	 /// <summary>
	 /// <add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa"/>
	 /// </summary>
	 private static string dbConnectionString = ConfigurationManager.AppSettings["ConnectionString"];

	 private DbConnection connection;
	 public DbHelper()
	 {
	 this.connection = CreateConnection(DbHelper.dbConnectionString);
	 }
	 public DbHelper(string connectionString)
	 {
	 this.connection = CreateConnection(connectionString);
	 }
	 public static DbConnection CreateConnection()
	 {
	 DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
	 DbConnection dbconn = dbfactory.CreateConnection();
	 dbconn.ConnectionString = DbHelper.dbConnectionString;
	 return dbconn;
	 }
	 public static DbConnection CreateConnection(string connectionString)
	 {
	 DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
	 DbConnection dbconn = dbfactory.CreateConnection();
	 dbconn.ConnectionString = connectionString;
	 return dbconn;
	 }

	 /// <summary>
	 /// 执行存储过程
	 /// </summary>
	 /// <param name="storedProcedure">存储过程名</param>
	 /// <returns></returns>
	 public DbCommand GetStoredProcCommand(string storedProcedure)
	 {
	 DbCommand dbCommand = connection.CreateCommand();
	 dbCommand.CommandText = storedProcedure;
	 dbCommand.CommandType = CommandType.StoredProcedure;
	 return dbCommand;
	 }

	 /// <summary>
	 /// 执行SQL语句
	 /// </summary>
	 /// <param name="sqlQuery">SQL语句</param>
	 /// <returns></returns>
	 public DbCommand GetSqlStringCommand(string sqlQuery)
	 {
	 DbCommand dbCommand = connection.CreateCommand();
	 dbCommand.CommandText = sqlQuery;
	 dbCommand.CommandType = CommandType.Text;
	 return dbCommand;
	 }

	 #region 增加参数

	 public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
	 {
	 foreach (DbParameter dbParameter in dbParameterCollection)
	 {
	 cmd.Parameters.Add(dbParameter);
	 }
	 }

	 /// <summary>
	 /// 增加输出参数
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="parameterName"></param>
	 /// <param name="dbType"></param>
	 /// <param name="size"></param>
	 public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
	 {
	 DbParameter dbParameter = cmd.CreateParameter();
	 dbParameter.DbType = dbType;
	 dbParameter.ParameterName = parameterName;
	 dbParameter.Size = size;
	 dbParameter.Direction = ParameterDirection.Output;
	 cmd.Parameters.Add(dbParameter);
	 }

	 /// <summary>
	 /// 增加输入参数
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="parameterName"></param>
	 /// <param name="dbType"></param>
	 /// <param name="value"></param>
	 public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
	 {
	 DbParameter dbParameter = cmd.CreateParameter();
	 dbParameter.DbType = dbType;
	 dbParameter.ParameterName = parameterName;
	 dbParameter.Value = value;
	 dbParameter.Direction = ParameterDirection.Input;
	 cmd.Parameters.Add(dbParameter);
	 }

	 /// <summary>
	 /// 增加返回参数
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="parameterName"></param>
	 /// <param name="dbType"></param>
	 public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
	 {
	 DbParameter dbParameter = cmd.CreateParameter();
	 dbParameter.DbType = dbType;
	 dbParameter.ParameterName = parameterName;
	 dbParameter.Direction = ParameterDirection.ReturnValue;
	 cmd.Parameters.Add(dbParameter);
	 }

	 public DbParameter GetParameter(DbCommand cmd, string parameterName)
	 {
	 return cmd.Parameters[parameterName];
	 }

	 #endregion

	 #region 执行

	 /// <summary>
	 /// 执行查询返回DataSet
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <returns></returns>
	 public DataSet ExecuteDataSet(DbCommand cmd)
	 {
	 DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
	 DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
	 dbDataAdapter.SelectCommand = cmd;
	 DataSet ds = new DataSet();
	 dbDataAdapter.Fill(ds);
	 return ds;
	 }

	 /// <summary>
	 /// 执行查询返回DataTable
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <returns></returns>
	 public DataTable ExecuteDataTable(DbCommand cmd)
	 {
	 DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
	 DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
	 dbDataAdapter.SelectCommand = cmd;
	 DataTable dataTable = new DataTable();
	 dbDataAdapter.Fill(dataTable);
	 return dataTable;
	 }

	 /// <summary>
	 /// 执行查询返回DataReader
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <returns></returns>
	 public DbDataReader ExecuteReader(DbCommand cmd)
	 {
	 cmd.Connection.Open();
	 DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
	 return reader;
	 }

	 /// <summary>
	 /// 执行SQL语句，返回影响行数
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <returns></returns>
	 public int ExecuteNonQuery(DbCommand cmd)
	 {
	 cmd.Connection.Open();
	 int ret = cmd.ExecuteNonQuery();
	 cmd.Connection.Close();
	 return ret;
	 }

	 /// <summary>
	 /// 返回首行首列对象
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <returns></returns>
	 public object ExecuteScalar(DbCommand cmd)
	 {
	 cmd.Connection.Open();
	 object ret = cmd.ExecuteScalar();
	 cmd.Connection.Close();
	 return ret;
	 }
	 #endregion

	 #region 执行事务

	 /// <summary>
	 /// 执行事务返回DataSet
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="t"></param>
	 /// <returns></returns>
	 public DataSet ExecuteDataSet(DbCommand cmd, Trans t)
	 {
	 cmd.Connection = t.DbConnection;
	 cmd.Transaction = t.DbTrans;
	 DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
	 DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
	 dbDataAdapter.SelectCommand = cmd;
	 DataSet ds = new DataSet();
	 dbDataAdapter.Fill(ds);
	 return ds;
	 }

	 /// <summary>
	 /// 执行事务返回DataTable
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="t"></param>
	 /// <returns></returns>
	 public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
	 {
	 cmd.Connection = t.DbConnection;
	 cmd.Transaction = t.DbTrans;
	 DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
	 DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
	 dbDataAdapter.SelectCommand = cmd;
	 DataTable dataTable = new DataTable();
	 dbDataAdapter.Fill(dataTable);
	 return dataTable;
	 }

	 /// <summary>
	 /// 执行事务返回DataReader
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="t"></param>
	 /// <returns></returns>
	 public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
	 {
	 cmd.Connection.Close();
	 cmd.Connection = t.DbConnection;
	 cmd.Transaction = t.DbTrans;
	 DbDataReader reader = cmd.ExecuteReader();
	 return reader;
	 }

	 /// <summary>
	 /// 执行事务SQL语句返回影响行数
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="t"></param>
	 /// <returns></returns>
	 public int ExecuteNonQuery(DbCommand cmd, Trans t)
	 {
	 cmd.Connection.Close();
	 cmd.Connection = t.DbConnection;
	 cmd.Transaction = t.DbTrans;
	 int ret = cmd.ExecuteNonQuery();
	 return ret;
	 }

	 /// <summary>
	 /// 执行事务SQL语句返回首行首列
	 /// </summary>
	 /// <param name="cmd"></param>
	 /// <param name="t"></param>
	 /// <returns></returns>
	 public object ExecuteScalar(DbCommand cmd, Trans t)
	 {
	 cmd.Connection.Close();
	 cmd.Connection = t.DbConnection;
	 cmd.Transaction = t.DbTrans;
	 object ret = cmd.ExecuteScalar();
	 return ret;
	 }
	 #endregion
	 }

	 public class Trans : IDisposable
	 {
	 private DbConnection conn;
	 private DbTransaction dbTrans;
	 public DbConnection DbConnection
	 {
	 get { return this.conn; }
	 }
	 public DbTransaction 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();
	 }
	 }
	 }
	}
试试其它关键字
　数据库操作　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)