using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace HouseSystem.SQLDAL
{
	/// <summary>
	/// SQLHelper 的摘要說明。
	/// </summary>
	public class SQLHelper
	{
		// Fields
		public static readonly string CONN_STRING;
		private static Hashtable parmCache;

		static SQLHelper()
		{
			SQLHelper.CONN_STRING = System.Configuration.ConfigurationSettings.AppSettings["CONNSTR_SQL"];
			SQLHelper.parmCache = Hashtable.Synchronized(new Hashtable());
		}
 
		public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
		{
			SQLHelper.parmCache[cacheKey] = cmdParms;
		}
 
		public static void ClearParameterValues(params SqlParameter[] cmdParms)
		{
			SqlParameter[] parameterArray1 = cmdParms;
			for (int num1 = 0; num1 < parameterArray1.Length; num1++)
			{
				SqlParameter parameter1 = parameterArray1[num1];
				parameter1.Value = DBNull.Value;
			}
		}
 
		public static SqlParameter CreateReturnParameter(string parameterName)
		{
			return new SqlParameter(parameterName, SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null);
		}
 
		public static DataRow ExecuteDataRow(string cmdText)
		{
			DataSet set1 = SQLHelper.ExecuteDataset(cmdText);
			if ((set1.Tables.Count > 0) && (set1.Tables[0].Rows.Count > 0))
			{
				return set1.Tables[0].Rows[0];
			}
			return null;
		}
 
		public static DataSet ExecuteDataset(string cmdText)
		{
			DataSet set2;
			SqlCommand command1 = new SqlCommand();
			SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING);
			DataSet set1 = new DataSet();
			try
			{
				if (connection1.State != ConnectionState.Open)
				{
					connection1.Open();
				}
				command1.Connection = connection1;
				command1.CommandText = cmdText;
				command1.CommandType = CommandType.Text;
				SqlDataAdapter adapter1 = new SqlDataAdapter();
				adapter1.SelectCommand = command1;
				adapter1.Fill(set1);
				set2 = set1;
			}
			catch
			{
				throw;
			}
			finally
			{
				connection1.Close();
			}
			return set2;
		}
 
		public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
		{
			return SQLHelper.ExecuteDataset(connection, commandType, commandText, (SqlParameter[]) null);
		}
 
		public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
		{
			return SQLHelper.ExecuteDataset(transaction, commandType, commandText, (SqlParameter[]) null);
		}
 
		public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
		{
			return SQLHelper.ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[]) null);
		}
 
		public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			SqlCommand command1 = new SqlCommand();
			SQLHelper.PrepareCommand(command1, connection, null, commandType, commandText, commandParameters);
			SqlDataAdapter adapter1 = new SqlDataAdapter(command1);
			DataSet set1 = new DataSet();
			adapter1.Fill(set1);
			command1.Parameters.Clear();
			return set1;
		}
 
		public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			SqlCommand command1 = new SqlCommand();
			SQLHelper.PrepareCommand(command1, transaction.Connection, transaction, commandType, commandText, commandParameters);
			SqlDataAdapter adapter1 = new SqlDataAdapter(command1);
			DataSet set1 = new DataSet();
			adapter1.Fill(set1);
			command1.Parameters.Clear();
			return set1;
		}
 
		public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			DataSet set1;
			using (SqlConnection connection1 = new SqlConnection(connectionString))
			{
				connection1.Open();
				set1 = SQLHelper.ExecuteDataset(connection1, commandType, commandText, commandParameters);
			}
			return set1;
		}
 
		public static DataSet ExecuteDataset(string connString, CommandType commandType, string commandText, SqlParameter[] commandParameters, int startRecord, int maxRecords, string srcTable)
		{
			SqlConnection connection1 = new SqlConnection(connString);
			SqlCommand command1 = new SqlCommand();
			SQLHelper.PrepareCommand(command1, connection1, null, commandType, commandText, commandParameters);
			SqlDataAdapter adapter1 = new SqlDataAdapter(command1);
			DataSet set1 = new DataSet();
			adapter1.Fill(set1, startRecord, maxRecords, srcTable);
			command1.Parameters.Clear();
			return set1;
		}
 
		public static DataTable ExecuteDataTable(string cmdText)
		{
			DataSet set1 = SQLHelper.ExecuteDataset(cmdText);
			if (set1.Tables.Count > 0)
			{
				return set1.Tables[0];
			}
			return null;
		}
 
		public static int ExecuteNonQuery(string cmdText)
		{
			int num2;
			SqlCommand command1 = new SqlCommand();
			using (SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING))
			{
				SQLHelper.PrepareCommand(command1, connection1, null, CommandType.Text, cmdText, null);
				int num1 = command1.ExecuteNonQuery();
				command1.Parameters.Clear();
				num2 = num1;
			}
			return num2;
		}
 
		public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
		{
			SqlCommand command1 = new SqlCommand();
			SQLHelper.PrepareCommand(command1, conn, null, cmdType, cmdText, cmdParms);
			int num1 = command1.ExecuteNonQuery();
			command1.Parameters.Clear();
			return num1;
		}
 
		public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
		{
			SqlCommand command1 = new SqlCommand();
			SQLHelper.PrepareCommand(command1, trans.Connection, trans, cmdType, cmdText, cmdParms);
			int num1 = command1.ExecuteNonQuery();
			command1.Parameters.Clear();
			return num1;
		}
 
		public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
		{
			int num2;
			SqlCommand command1 = new SqlCommand();
			using (SqlConnection connection1 = new SqlConnection(connString))
			{
				SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms);
				int num1 = command1.ExecuteNonQuery();
				command1.Parameters.Clear();
				num2 = num1;
			}
			return num2;
		}
 
		public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
		{
			SqlDataReader reader2;
			SqlCommand command1 = new SqlCommand();
			SqlConnection connection1 = new SqlConnection(connString);
			try
			{
				SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms);
				SqlDataReader reader1 = command1.ExecuteReader(CommandBehavior.CloseConnection);
				command1.Parameters.Clear();
				reader2 = reader1;
			}
			catch
			{
				connection1.Close();
				throw;
			}
			return reader2;
		}
 
		public static object ExecuteScalar(string cmdText)
		{
			object obj2;
			SqlCommand command1 = new SqlCommand();
			using (SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING))
			{
				SQLHelper.PrepareCommand(command1, connection1, null, CommandType.Text, cmdText, null);
				object obj1 = command1.ExecuteScalar();
				command1.Parameters.Clear();
				obj2 = obj1;
			}
			return obj2;
		}
 
		public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
		{
			SqlCommand command1 = new SqlCommand();
			SQLHelper.PrepareCommand(command1, conn, null, cmdType, cmdText, cmdParms);
			object obj1 = command1.ExecuteScalar();
			command1.Parameters.Clear();
			return obj1;
		}
 
		public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
		{
			object obj2;
			SqlCommand command1 = new SqlCommand();
			using (SqlConnection connection1 = new SqlConnection(connString))
			{
				SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms);
				object obj1 = command1.ExecuteScalar();
				command1.Parameters.Clear();
				obj2 = obj1;
			}
			return obj2;
		}
 
		public static SqlParameter[] GetCachedParameters(string cacheKey)
		{
			SqlParameter[] parameterArray1 = (SqlParameter[]) SQLHelper.parmCache[cacheKey];
			if (parameterArray1 == null)
			{
				return null;
			}
			SqlParameter[] parameterArray2 = new SqlParameter[parameterArray1.Length];
			int num1 = 0;
			int num2 = parameterArray1.Length;
			while (num1 < num2)
			{
				parameterArray2[num1] = (SqlParameter) ((ICloneable) parameterArray1[num1]).Clone();
				num1++;
			}
			return parameterArray2;
		}
 
		private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
		{
			if (conn.State != ConnectionState.Open)
			{
				conn.Open();
			}
			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			if (trans != null)
			{
				cmd.Transaction = trans;
			}
			cmd.CommandType = cmdType;
			if (cmdParms != null)
			{
				SqlParameter[] parameterArray1 = cmdParms;
				for (int num1 = 0; num1 < parameterArray1.Length; num1++)
				{
					SqlParameter parameter1 = parameterArray1[num1];
					cmd.Parameters.Add(parameter1);
				}
			}
		}

	}
}