using System;

namespace _36Hang.Data.SqlClient
{
	/// <summary>
	/// Connection 的摘要說明。
	/// </summary>
	public class Connection
	{
		private string _Server;
		private string _Database;
		private string _Username;
		private string _Password;
		
		private System.Data.SqlClient.SqlConnection objSqlConnection;

		public Connection(string Server,string Database,string Username,string Password)
		{
			_Server = Server;
			_Database = Database;
			_Username = Username;
			_Password = Password;
		}

		/// <summary>
		/// 打開數據庫連接
		/// </summary>
		private void Open()
		{
			try
			{
				string strConnection = "server=" + _Server + ";";
				strConnection += "database=" + _Database + ";";
				strConnection += "uid=" + _Username + ";";
				strConnection += "pwd=" + _Password;

				objSqlConnection = new System.Data.SqlClient.SqlConnection(strConnection);
				objSqlConnection.Open();
			}
			catch(Exception e)
			{
				throw(e);
			}
		}

		/// <summary>
		/// 關閉數據庫連接
		/// </summary>
		private void Close()
		{
			try
			{
				objSqlConnection.Close();
			}
			catch(Exception e)
			{
				throw(e);
			}
		}

		/// <summary>
		/// 執行數據庫查詢
		/// </summary>
		/// <param name="Sql">數據庫查詢語句</param>
		public void Execute(string Sql)
		{
			try
			{
				Open();
				try
				{
					System.Data.SqlClient.SqlCommand objSqlCommand = new System.Data.SqlClient.SqlCommand(Sql,objSqlConnection);
					objSqlCommand.ExecuteNonQuery();
					Close();
				}
				catch(Exception e)
				{
					Close();
					throw(e);
				}
			}
			catch(Exception e)
			{
				throw(e);
			}
		}

		/// <summary>
		/// 獲得數據庫查詢結果
		/// 用于獲得全部查詢結果
		/// </summary>
		/// <param name="Sql">數據庫查詢語句</param>
		/// <returns>數據庫查詢結果</returns>
		public string Records(string Sql)
		{
			try
			{
				Open();
				try
				{
					string strRecords = "<?xml version=\"1.0\" encoding=\"gb2312\" ?><Root>";

					System.Data.SqlClient.SqlCommand objSqlCommand = new System.Data.SqlClient.SqlCommand(Sql,objSqlConnection);
					System.Data.SqlClient.SqlDataReader objSqlDataReader = objSqlCommand.ExecuteReader();
					
					while(objSqlDataReader.Read())
					{
						strRecords = strRecords + "<Recordset>";
						for(int i=0; i<objSqlDataReader.FieldCount; i++)
						{
							strRecords = strRecords + "<" + objSqlDataReader.GetName(i) + ">";
							strRecords = strRecords + objSqlDataReader.GetValue(i);
							strRecords = strRecords + "</" + objSqlDataReader.GetName(i) + ">";
						}
						strRecords = strRecords + "</Recordset>";
					}

					strRecords = strRecords + "</Root>";

					objSqlDataReader.Close();
					Close();

					return strRecords;
				}
				catch(Exception e)
				{
					Close();
					throw(e);
				}
			}
			catch(Exception e)
			{
				throw(e);
			}
		}

		/// <summary>
		/// 獲得數據庫查詢結果
		/// 用于獲取由 Page、Length 參數指定條件的查詢結果
		/// </summary>
		/// <param name="Sql">數據庫查詢語句</param>
		/// <param name="Page">數據庫查詢結果的頁數</param>
		/// <param name="Length">數據庫查詢結果的頁長</param>
		/// <returns>數據庫查詢結果</returns>
		public string Records(string Sql,int Page,int Length)
		{
			try
			{
				Open();
				try
				{
					string strRecords = "<?xml version=\"1.0\" encoding=\"gb2312\" ?><Root>";

					Sql = "select top " + Page * Length + " * from (" + Sql + ") derivedtbl";
					System.Data.SqlClient.SqlCommand objSqlCommand = new System.Data.SqlClient.SqlCommand(Sql,objSqlConnection);
					System.Data.SqlClient.SqlDataReader objSqlDataReader = objSqlCommand.ExecuteReader();
					
					long lngCount = 0;
					while(objSqlDataReader.Read())
					{
						lngCount = lngCount + 1;
						if(lngCount > (Page - 1) * Length)
						{
							strRecords = strRecords + "<Recordset>";
							for(int i=0; i<objSqlDataReader.FieldCount; i++)
							{
								strRecords = strRecords + "<" + objSqlDataReader.GetName(i) + ">";
								strRecords = strRecords + objSqlDataReader.GetValue(i);
								strRecords = strRecords + "</" + objSqlDataReader.GetName(i) + ">";
							}
							strRecords = strRecords + "</Recordset>";
						}
					}

					strRecords = strRecords + "</Root>";

					objSqlDataReader.Close();
					Close();

					return strRecords;
				}
				catch(Exception e)
				{
					Close();
					throw(e);
				}
			}
			catch(Exception e)
			{
				throw(e);
			}
		}
	}
}