using System;

namespace DTier
{
	/// <summary>
	/// This class is wrapper class for database activities.
	/// The class provides way to connect to the database. The connection parameters
	/// are stored in the registry and accessed whenever required.
	/// </summary>
	using System;
	using Microsoft.Win32;	
	using System.Data;
	using System.Data.SqlClient;
	using System.IO;
	using System.Collections;
	using Com;

	public class DataConnector
	{

		private String strServer;
		private String strUser;
		private String strPass;
		private String strDatabase;
		
		/// <summary>
		/// This is the default constructor.
		/// The constructor initiates the database connection.
		/// Connections are created as and when required.
		/// <param>none</param>
		/// </summary>
		public DataConnector()
		{
			setDatabaseProperties();
		}
	
		public SqlConnection getPooledConnection(SqlConnection sConn)
		{
			String strConnString = "server=" + strServer.Trim() + ";uid=" + strUser.Trim() + ";pwd=" + strPass + ";database=" + strDatabase +";"  +
												"connection reset = false;" + "connection lifetime=5;" + "min pool size=1;" + "max pool size=50;";	
			sConn = new SqlConnection(strConnString);
			//open and close a connection so that it is available in the pool
			sConn.Open();
			sConn.Close();
			Logger.Log("Created Connection in pool..");
			return(sConn);
		}
		

		public bool closeConnection(SqlConnection sConn)
		{
			bool blnRetVal = false;
			try
			{
				if(sConn.State.Equals("Open"))
				{
					sConn.Close();
				}
				sConn.Close();
				sConn.Dispose();
				blnRetVal = true;
			}
			catch(Exception exp)
			{
				Logger.Log("Error closing connection. Possible reasons :- " + exp.ToString());
			}
			return(blnRetVal);
		}
		
		/// <summary>
		/// The method connects to the specific table and fetches the 
		/// data accordingly
		/// </summary>
		/// <param name="strTable"></param>
		/// <param name="strFields"></param>
		/// <param name="strWhere"></param>
		/// <param name="strOrder"></param>
		/// <param name="strGroup"></param>
		/// <param name="strDataSetName"></param>
		/// <returns name="dSet"></returns>
		public DataSet getDataSetValues(String strTable,String strFields,String strWhere,String strGroup,String strOrder,String strDataSetName,SqlConnection sConn)
		{
			
			String strQuery="";
			DataSet dSet = new DataSet();
			try
			{
				if(strFields != null)
				{
					strQuery = "SELECT " + strFields + " FROM " + strTable ;
				}
				else
				{
					strQuery = "SELECT * FROM " + strTable;
				}
				if(strWhere != null)
				{
					strQuery = strQuery + " WHERE " + strWhere;
				}
				if(strGroup != null)
				{
					strQuery = strQuery + " GROUP BY " + strGroup;
				}
				if(strOrder != null)
				{
					strQuery = strQuery + " ORDER BY " + strOrder;
				}
				sConn.Open();
				SqlDataAdapter sAdapter = new SqlDataAdapter(strQuery,sConn);
				sAdapter.Fill(dSet,strDataSetName);
				sConn.Close();
			}
			catch(Exception exp)
			{
				Logger.Log("Error fetching records for the query:- " + strQuery + ". Possible cause:- " + exp.ToString() );
				sConn.Close();
			}
				
			return(dSet);
		}

		public DataSet execStoredProc(String strProcName,String strParameters,String strDataSetName,SqlConnection sConn)
		{
			String strQuery = "EXEC " + strProcName.Trim();
			DataSet dSet = new DataSet();
			if(strParameters != null)
			{
				strQuery = strQuery + " "  + strParameters;
			}
			try
			{
				sConn.Open();
				SqlDataAdapter sAdapter = new SqlDataAdapter(strQuery,sConn);
				sAdapter.Fill(dSet,strDataSetName);
				sConn.Close();
			}
			catch(Exception exp)
			{
				Logger.Log("Error fetching records for the query:- " + strQuery + ". Possible cause:- " + exp.ToString() );
				sConn.Close();
			}
			return(dSet);

		}


		public Int32 insertTable(String strTable,String strFields,String strValues,SqlConnection sConn)
		{
			Int32 iReturn = 0;
			String strSql;
			strSql = "INSERT INTO " + strTable.Trim() + "(" + strFields.Trim() + ") VALUES (" +  strValues + ")";
			try
			{
				sConn.Open();
				SqlCommand	sCommand = new SqlCommand(strSql,sConn);
				iReturn = sCommand.ExecuteNonQuery();
				sConn.Close();
			}
			catch(Exception e)
			{
				Logger.Log("Error inserting into database: " + e.ToString() + ". The SQL :" + strSql); 
				sConn.Close();
			}
			return (iReturn);
		}

		public Int32 deleteTable(String strTable,String strWhereClause,SqlConnection sConn)
		{
			Int32 iReturn = 0;
			String strSql;

			if(strWhereClause != null)
			{
				strSql = "DELETE " + strTable.Trim() + " WHERE " + strWhereClause.Trim();
			}
			else
			{
				strSql = "DELETE " + strTable.Trim();
			}

			try
			{
				sConn.Open();
				SqlCommand	sCommand = new SqlCommand(strSql,sConn);
				iReturn = sCommand.ExecuteNonQuery();
				sConn.Close();
			}
			catch(Exception exp)
			{
				Logger.Log("Error deleteing table; the query:- " + strSql + ". Exception : " + exp.ToString());
				sConn.Close();
			}
			return(iReturn);
		}

		public Int32 updateTable(String strTable,String strSetClause,String strWhere,SqlConnection sConn)
		{
			Int32 iReturn = 0;
			String strSql = "UPDATE " + strTable.Trim() +  " SET " + strSetClause ;
			if(strWhere != null)
			{
				strSql =  strSql + " WHERE "  + strWhere.Trim();
			}
			try
			{
				sConn.Open();
				SqlCommand sCommand = new SqlCommand(strSql,sConn);
				iReturn = sCommand.ExecuteNonQuery();
				sConn.Close();
			}
			catch(Exception exp)
			{
				Logger.Log("Error updating table; the query:- " + strSql + ". Exception : " + exp.ToString());
				sConn.Close();
			}
			return(iReturn);
		}
		
		private void setDatabaseProperties()
		{
			RegistryKey rKeyHKLM = Registry.LocalMachine;
			RegistryKey rKeySOFT = rKeyHKLM.OpenSubKey("SOFTWARE");
			RegistryKey rKeyDigitralis = rKeySOFT.OpenSubKey("cepo");
			strServer = (String)rKeyDigitralis.GetValue("Server");
			strUser = (String)rKeyDigitralis.GetValue("User");
			strPass = (String)rKeyDigitralis.GetValue("Pass");
			strDatabase = (String)rKeyDigitralis.GetValue("Database");
		}


	}
}