sqlhelper.cs

来自「该项目中对 SQLHelper 类进行了简单封装」· CS 代码 · 共 191 行

/* 
 * SQLHelper.cs @Microsoft Visual Studio 2005 <.NET Framework 2.0>
 * AfritXia
 * 2007-05-01
 * 
 * Copyright(c) http://www.AfritXia.NET/
 * 
 */

using System;
using System.Data;
using System.Data.Common;

namespace NET.AfritXia.DBUtility
{
	/// <summary>查询帮助类</summary>
	public partial class SQLHelper
	{
		#region 类 SQLHelper 构造器
		/// <summary>
		/// 类 SQLHelper 默认构造器
		/// </summary>
		internal SQLHelper()
		{
		}
		#endregion

		/// <summary>
		/// 执行 SQL 查询，包括插入、更新、删除语句
		/// </summary>
		/// <param name="cmdText">查询命令</param>
		/// <param name="cmdType">查询命令类型</param>
		/// <param name="dbParams">参数列表</param>
		/// <param name="objDbConn">此次查询所使用的数据库联接</param>
		/// <param name="objDbTran">此次查询所使用的数据库事务</param>
		/// <returns>查询所影响的行数</returns>
		public int ExecuteNoneQuery(string cmdText, CommandType cmdType, DbParameter[] dbParams,
			DbConnection objDbConn, DbTransaction objDbTran)
		{
			if (objDbConn == null)
				throw new ArgumentNullException("数据库连接为 null 空值");

			int val = 0;

			DbConnection dbConn = null;
			DbCommand dbCmd = null;

			dbConn = objDbConn;
			dbCmd = dbConn.CreateCommand();

			// 设置查询命令文本
			dbCmd.CommandText = cmdText;
			// 事务过程对象
			dbCmd.Transaction = objDbTran;
			// 查询命令类型
			dbCmd.CommandType = cmdType;

			if (dbParams != null)
			{
				foreach (DbParameter dbParam in dbParams)
					dbCmd.Parameters.Add(dbParam);
			}

			try
			{
				if (dbConn.State != ConnectionState.Open)
					dbConn.Open();

				val = dbCmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (objDbTran == null)
					dbConn.Close();
			}

			return val;
		}

		/// <summary>
		/// 执行 SQL 查询
		/// </summary>
		/// <param name="cmdText">查询命令</param>
		/// <param name="dbParams">参数列表</param>
		/// <param name="cmdType">查询命令类型</param>
		/// <param name="objDbConn">此次查询所使用的数据库联接</param>
		/// <param name="objDbTran">此次查询所使用的数据库事务</param>
		/// <returns>返回查询阅读器</returns>
		public DbDataReader ExecuteReader(string cmdText, CommandType cmdType, DbParameter[] dbParams,
			DbConnection objDbConn, DbTransaction objDbTran)
		{
			if (objDbConn == null)
				throw new ArgumentNullException("数据库连接为 null 空值");

			DbConnection dbConn = null;
			DbCommand dbCmd = null;

			dbConn = objDbConn;
			dbCmd = dbConn.CreateCommand();

			// 设置查询命令文本
			dbCmd.CommandText = cmdText;
			// 事务过程对象
			dbCmd.Transaction = objDbTran;
			// 查询命令类型
			dbCmd.CommandType = cmdType;

			if (dbParams != null)
			{
				foreach (DbParameter dbParam in dbParams)
					dbCmd.Parameters.Add(dbParam);
			}

			try
			{
				if (dbConn.State != ConnectionState.Open)
					dbConn.Open();

				DbDataReader dr = dbCmd.ExecuteReader(
					objDbTran == null ? CommandBehavior.Default : CommandBehavior.CloseConnection);

				return dr;
			}
			catch
			{
				if (objDbTran == null)
					dbConn.Close();

				throw;
			}
		}

		/// <summary>
		/// 执行 SQL 查询，返回查询结果的第一行的第一列，忽略其它行和列
		/// </summary>
		/// <param name="cmdText">查询命令</param>
		/// <param name="cmdType">查询命令类型</param>
		/// <param name="dbParams">参数列表</param>
		/// <param name="objDbConn">此次查询所使用的数据库联接</param>
		/// <param name="objDbTran">此次查询所使用的数据库事务</param>
		/// <returns></returns>
		public object ExecuteScalar(string cmdText, CommandType cmdType, DbParameter[] dbParams,
			DbConnection objDbConn, DbTransaction objDbTran)
		{
			if (objDbConn == null)
				throw new ArgumentNullException("数据库连接为 null 空值");

			object value = null;

			DbConnection dbConn = null;
			DbCommand dbCmd = null;

			dbConn = objDbConn;
			dbCmd = dbConn.CreateCommand();

			// 设置查询命令文本
			dbCmd.CommandText = cmdText;
			// 事务过程对象
			dbCmd.Transaction = objDbTran;
			// 查询命令类型
			dbCmd.CommandType = cmdType;

			if (dbParams != null)
			{
				foreach (DbParameter dbParam in dbParams)
					dbCmd.Parameters.Add(dbParam);
			}

			try
			{
				if (dbConn.State != ConnectionState.Open)
					dbConn.Open();

				value = dbCmd.ExecuteScalar();
			}
			catch
			{
				if (objDbTran == null)
					dbConn.Close();

				throw;
			}

			return value;
		}
	}
}