dbhelpersql.cs

一个非常实用的无限级菜单源码

using System;
using System.Text;
using System.Collections;
using System.Collections.Specialized;
using System.Data.SqlClient;

using System.Data;
namespace test
{
	//该源码下载自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)

	/// <summary>
	/// DbHelperSQL 的摘要说明。
	/// </summary>
	public class DbHelperSQL
	{
		public DbHelperSQL()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		//数据库连接字符串(web.config来配置)
		//<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />		
		protected  static  string connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];


		#region 公用方法
        

		public  int GetMaxID(string FieldName, string TableName)
		{
			string strsql = "select max(" + FieldName + ")+1 from " + TableName;
			object obj = GetSingle(strsql);
			if (obj == null)
			{
				return 1;
			}
			else
			{
				return int.Parse(obj.ToString());
			}
		}
		public  bool Exists(string strSql, params SqlParameter[] cmdParms)
		{
			object obj = GetSingle(strSql, cmdParms);
			int cmdresult;
			if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
			{
				cmdresult = 0;
			}
			else
			{
				cmdresult = int.Parse(obj.ToString());
			}
			if (cmdresult == 0)
			{
				return false;
			}
			else
			{
				return true;
			}
		}
		#endregion

		#region  执行简单SQL语句

		/// <summary>
		/// 执行SQL语句，返回影响的记录数
		/// </summary>
		/// <param name="SQLString">SQL语句</param>
		/// <returns>影响的记录数</returns>
		public   int ExecuteSql(string SQLString)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				using (SqlCommand cmd = new SqlCommand(SQLString, connection))
				{
					try
					{
						connection.Open();
						int rows = cmd.ExecuteNonQuery();
						connection.Close();
						return rows;
					}
					catch (System.Data.SqlClient.SqlException E)
					{
						connection.Close();
						throw new Exception(E.Message);
					}
				}
			}
		}

		/// <summary>
		/// 执行多条SQL语句，实现数据库事务。
		/// </summary>
		/// <param name="SQLStringList">多条SQL语句</param>		
		public   void ExecuteSqlTran(ArrayList SQLStringList)
		{
			using (SqlConnection conn = new SqlConnection(connectionString))
			{
				conn.Open();
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				SqlTransaction tx = conn.BeginTransaction();
				cmd.Transaction = tx;
				try
				{
					for (int n = 0; n < SQLStringList.Count; n++)
					{
						string strsql = SQLStringList[n].ToString();
						if (strsql.Trim().Length > 1)
						{
							cmd.CommandText = strsql;
							cmd.ExecuteNonQuery();
						}
					}
					tx.Commit();
					conn.Close();
				}
				catch (System.Data.SqlClient.SqlException E)
				{
					conn.Close();
					tx.Rollback();
					throw new Exception(E.Message);
				}
			}
		}
		/// <summary>
		/// 执行带一个存储过程参数的的SQL语句。
		/// </summary>
		/// <param name="SQLString">SQL语句</param>
		/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
		/// <returns>影响的记录数</returns>
		public   int ExecuteSql(string SQLString, string content)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				SqlCommand cmd = new SqlCommand(SQLString, connection);
				System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
				myParameter.Value = content;
				cmd.Parameters.Add(myParameter);
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					connection.Close();
					return rows;
				}
				catch (System.Data.SqlClient.SqlException E)
				{
					throw new Exception(E.Message);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
		}
		/// <summary>
		/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
		/// </summary>
		/// <param name="strSQL">SQL语句</param>
		/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
		/// <returns>影响的记录数</returns>
		public   int ExecuteSqlInsertImg(string strSQL, byte[] fs)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				SqlCommand cmd = new SqlCommand(strSQL, connection);
				System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
				myParameter.Value = fs;
				cmd.Parameters.Add(myParameter);
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					connection.Close();
					return rows;
				}
				catch (System.Data.SqlClient.SqlException E)
				{
					throw new Exception(E.Message);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
		}

		/// <summary>
		/// 执行一条计算查询结果语句，返回查询结果（object）。
		/// </summary>
		/// <param name="SQLString">计算查询结果语句</param>
		/// <returns>查询结果（object）</returns>
		public   object GetSingle(string SQLString)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				using (SqlCommand cmd = new SqlCommand(SQLString, connection))
				{
					try
					{
						connection.Open();
						object obj = cmd.ExecuteScalar();
						if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
						{
							connection.Close();
							return null;
						}
						else
						{
							connection.Close();
							return obj;
						}
					}
					catch (System.Data.SqlClient.SqlException e)
					{
						connection.Close();
						throw new Exception(e.Message);
					}
				}
			}
		}
		/// <summary>
		/// 执行查询语句，返回SqlDataReader
		/// </summary>
		/// <param name="strSQL">查询语句</param>
		/// <returns>SqlDataReader</returns>
		public   SqlDataReader ExecuteReader(string strSQL)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			SqlCommand cmd = new SqlCommand(strSQL, connection);
			try
			{
				connection.Open();
				SqlDataReader myReader = cmd.ExecuteReader();
				connection.Close();
				return myReader;
			}
			catch (System.Data.SqlClient.SqlException e)
			{
				connection.Close();
				throw new Exception(e.Message);
			}

		}
		/// <summary>
		/// 执行查询语句，返回DataSet
		/// </summary>
		/// <param name="SQLString">查询语句</param>
		/// <returns>DataSet</returns>
		public   DataSet Query(string SQLString)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				DataSet ds = new DataSet();
				try
				{
					connection.Open();
					SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
					command.Fill(ds, "ds");

				}
				catch (System.Data.SqlClient.SqlException ex)
				{
					throw new Exception(ex.Message);
				}
				connection.Close();
				return ds;
			}
		}


		#endregion

		#region 执行带参数的SQL语句

		/// <summary>
		/// 执行SQL语句，返回影响的记录数
		/// </summary>
		/// <param name="SQLString">SQL语句</param>
		/// <returns>影响的记录数</returns>
		public   int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
		{
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				using (SqlCommand cmd = new SqlCommand())