base.cs

电子商务网站,结合Visual Sttudio 2005和SQL 2005 用ASP.NET语言开发的网站.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
//51-A-s-p-x.com

namespace shop.DbBase
{
	/// <summary>
	/// SqlBase.
	/// </summary>
	public class SqlBase
	{

		#region "Fields of SqlBase calss"

		/// <summary>
		/// connecting to Database
		/// </summary>
		protected static string sConn = ConfigurationSettings.AppSettings["ConnectionString"];

		#endregion

		#region "Functions of SqlBase class"
		public SqlBase()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		/// <summary>
		/// executing SQL commands
		/// </summary>
		/// <param name="strSQL">string</param>
		/// <returns>return int</returns>
		public static int ExecuteSql(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(sConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				myCmd.ExecuteNonQuery();
				return 0;
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		///executing SQL commands
		/// </summary>
		/// <param name="strSQL">要执行的SQL语句,为字符串类型string</param>
		/// <returns>返回执行情况,整形int</returns>
		public static int ExecuteSqlEx(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(sConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			
			try
			{
				myCn.Open();				
				SqlDataReader myReader = myCmd.ExecuteReader();
				if(myReader.Read())
				{
					return 0;
				}	
				else
				{
					throw new Exception("Value Unavailable!");
				}
			}
			catch(System.Data.SqlClient.SqlException e)
			{								
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		/// get dataset
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(DataSet)</returns>
		public static DataSet ExecuteSql4Ds(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(sConn);			
			try
			{
				myCn.Open();
				SqlDataAdapter sda = new SqlDataAdapter(strSQL,myCn);
				DataSet ds = new DataSet("ds");
				sda.Fill(ds);
				return ds;
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCn.Close();
			}
		}


		/// <summary>
		/// get single value
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(int)</returns>
		public static int ExecuteSql4Value(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(sConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				object r = myCmd.ExecuteScalar();
				if(Object.Equals(r,null))
				{
					throw new Exception("value unavailable！");
				}
				else
				{
					return (int)r;
				}				
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}		


		/// <summary>
		/// get object
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(object)</returns>
		public static object ExecuteSql4ValueEx(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(sConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				object r = myCmd.ExecuteScalar();
				if(Object.Equals(r,null))
				{
					throw new Exception("object unavailable!");
				}
				else
				{
					return r;
				}				
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		/// execute multipul SQL commands 
		/// </summary>
		/// <param name="strSQLs">string</param>
		/// <returns>int</returns>
		public static int ExecuteSqls(string[] strSQLs)
		{
			SqlConnection myCn = new SqlConnection(sConn);			
			SqlCommand myCmd = new SqlCommand();			
			int j=strSQLs.Length;

			try
			{
				myCn.Open();				
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
			SqlTransaction myTrans = myCn.BeginTransaction();

			try
			{											
				myCmd.Connection = myCn;				
				myCmd.Transaction = myTrans;

				foreach(string str in strSQLs)
				{
					myCmd.CommandText = str;
					myCmd.ExecuteNonQuery();
				}
				myTrans.Commit();
				return 0;
			}
			catch(System.Data.SqlClient.SqlException e)
			{			
				myTrans.Rollback();
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}

		#endregion
	}


	
	/// <summary>
	/// OleBase.
	/// </summary>
	public class OleBase
	{

		#region "Fields of OleBase calss"

		/// <summary>
		/// connecting to Database
		/// </summary>
		protected static string strConn = ConfigurationSettings.AppSettings["ConnectionString"];

		#endregion

		#region "Functions of OleBase class"
		public OleBase()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		/// <summary>
		/// executing SQL commands
		/// </summary>
		/// <param name="strSQL">string</param>
		/// <returns>return int</returns>
		public static int ExecuteSql(string strSQL)
		{
			OleDbConnection myCn = new OleDbConnection(strConn);			
			OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				myCmd.ExecuteNonQuery();
				return 0;
			}
			catch(OleDbException e)
			{				
				throw new Exception(e.Message/* + " at databse :" + strConn*/);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		///executing SQL commands
		/// </summary>
		/// <param name="strSQL">要执行的SQL语句,为字符串类型string</param>
		/// <returns>返回执行情况,整形int</returns>
		public static int ExecuteSqlEx(string strSQL)
		{
			OleDbConnection myCn = new OleDbConnection(strConn);			
			OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
			
			try
			{
				myCn.Open();				
				OleDbDataReader myReader = myCmd.ExecuteReader();
				if(myReader.Read())
				{
					return 0;
				}	
				else
				{
					throw new Exception("Value Unavailable!");
				}
			}
			catch(OleDbException e)
			{								
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		/// get dataset
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(DataSet)</returns>
		public static DataSet ExecuteSql4Ds(string strSQL)
		{
			OleDbConnection myCn = new OleDbConnection(strConn);			
			try
			{
				myCn.Open();
				OleDbDataAdapter sda = new OleDbDataAdapter(strSQL,myCn);
				DataSet ds = new DataSet("ds");
				sda.Fill(ds);
				return ds;
			}
			catch(OleDbException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCn.Close();
			}
		}


		/// <summary>
		/// get single value
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(int)</returns>
		public static int ExecuteSql4Value(string strSQL)
		{
			OleDbConnection myCn = new OleDbConnection(strConn);			
			OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				object r = myCmd.ExecuteScalar();
				if(Object.Equals(r,null))
				{
					throw new Exception("value unavailable！");
				}
				else
				{
					return (int)r;
				}				
			}
			catch(OleDbException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}		


		/// <summary>
		/// get object
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(object)</returns>
		public static object ExecuteSql4ValueEx(string strSQL)
		{
			OleDbConnection myCn = new OleDbConnection(strConn);			
			OleDbCommand myCmd = new OleDbCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				object r = myCmd.ExecuteScalar();
				if(Object.Equals(r,null))
				{
					throw new Exception("object unavailable!");
				}
				else
				{
					return r;
				}				
			}
			catch(OleDbException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		/// execute multipul SQL commands 
		/// </summary>
		/// <param name="strSQLs">string</param>
		/// <returns>int</returns>
		public static int ExecuteSqls(string[] strSQLs)
		{
			OleDbConnection myCn = new OleDbConnection(strConn);			
			OleDbCommand myCmd = new OleDbCommand();			
			int j=strSQLs.Length;

			try
			{
				myCn.Open();				
			}
			catch(OleDbException e)
			{
				throw new Exception(e.Message);
			}
			OleDbTransaction myTrans = myCn.BeginTransaction();

			try
			{											
				myCmd.Connection = myCn;				
				myCmd.Transaction = myTrans;

				foreach(string str in strSQLs)
				{
					myCmd.CommandText = str;
					myCmd.ExecuteNonQuery();
				}
				myTrans.Commit();
				return 0;
			}
			catch(OleDbException e)
			{			
				myTrans.Rollback();
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}

		#endregion
	}
}