using System;
using System.Data;
using System.Reflection;
using System.Resources;
using System.Configuration;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Collections.Specialized;
using System.Data.OracleClient;
using Ease.CoreV35.DataAccess;
using System.Data.SqlClient;

namespace Payroll.BO
{
	/// <summary>
	/// Summary description for DARunSQL.
	/// </summary>
	public class DARunSQL
	{
		public DARunSQL()
		{
			//
			// TODO: Add constructor logic here
			//
		}
		public void RunSQL(string SQL)
		{
            //try
            //{
            //    SQLHelper.ExecuteNonQuery(OleDbTransaction, CommandType.Text, SQL);
            //}
            //catch(Exception e)
            //{
            //    throw new Exception(e.Message);
			//}
		}
        public void SelectCommand(string sSQL, DataSet oDataSet, string sTableName)
        {
            ETransactionContext tc = new ETransactionContext();
            try
            {
                
                tc.Begin();
                tc.ExecuteDataSet(oDataSet,sTableName, sSQL);
                tc.End();
            }
            catch (Exception ex)
            {
                tc.End();
                throw new Exception(ex.Message);
            }
        }
		public void SelectCommand(string sSQL, string sDataSource, string sDB,string sProvider,string sUserId,string sPassword, DataSet oDataSet,string sTableName)
		{
			if(sSQL =="") return;

			string sConString="";
			if(sUserId=="") sUserId="sa";
			if(sProvider=="MSDAORA")
			{
				//Provider=" + sProvider + ";
				SQL.SQLSyntax=SQLSyntax.Oracle;
				sConString="Data Source=" + sDataSource +";User ID=" + sUserId + ";Password=" +sPassword +";";
				
				System.Data.OracleClient.OracleConnection oDSTSCon=new System.Data.OracleClient.OracleConnection(sConString);
				try
				{
					DataTable oTable=new DataTable(sTableName);
				
					oDSTSCon.Open();
					OracleDataAdapter oAdp=new OracleDataAdapter(sSQL,oDSTSCon);
					oAdp.Fill(oTable);
					oDataSet.Tables.Add(oTable);
				
				}
				catch(Exception e)
				{
					throw new Exception(e.Message);
				}
				finally
				{oDSTSCon.Close();}
			}
			else
			{
				SQL.SQLSyntax=SQLSyntax.SQL;
				sConString="Provider=" + sProvider + ";Data Source=" + sDataSource +";User ID=" + sUserId + ";Password=" +sPassword +";Initial Catalog="+ sDB;
				
				OleDbConnection oDSTSCon=new OleDbConnection(sConString);
				try
				{
					DataTable oTable=new DataTable(sTableName);
				
					oDSTSCon.Open();
					OleDbDataAdapter oAdp=new OleDbDataAdapter(sSQL,oDSTSCon);
					oAdp.Fill(oTable);
					oDataSet.Tables.Add(oTable);
				
				}
				catch(Exception e)
				{
					throw new Exception(e.Message);
				}
				finally
				{oDSTSCon.Close();}
			}

			
			
		}
		public int ExecuteQuery(string sSQL, string sDataSource, string sDB,string sProvider,string sUserId,string sPassword,bool isCheck)
		{
			if(sSQL =="") return 0;
			string sConString="";
			int res=0;
			if(sUserId=="") sUserId="sa";

			if(sProvider=="MSDAORA")
			{
				//Provider=" + sProvider + ";
				SQL.SQLSyntax=SQLSyntax.Oracle;
				sConString="Data Source=" + sDataSource +";User ID=" + sUserId + ";Password=" +sPassword +";";
			
				System.Data.OracleClient.OracleConnection oDSTSCon=new System.Data.OracleClient.OracleConnection(sConString);
				System.Data.OracleClient.OracleTransaction oTransaction;
				oDSTSCon.Open();
				oTransaction=oDSTSCon.BeginTransaction();

				try
				{	
					System.Data.OracleClient.OracleCommand oCMD=new System.Data.OracleClient.OracleCommand(sSQL,oDSTSCon,oTransaction);
					res= oCMD.ExecuteNonQuery();
                    if(isCheck)
                        oTransaction.Rollback();
                    else
					    oTransaction.Commit();
				}

				catch(Exception e)
				{
					oTransaction.Rollback();
					throw new Exception(e.Message);
				}
			}                           
			else
			{
                //SQL.SQLSyntax=SQLSyntax.SQL;
                //if(sUserId.ToLower()=="windows authentication")                    
                //    sConString="Provider=" + sProvider + ";server=" + sDataSource +";Database="+ sDB+";integrated security=yes;";
                //else
                //    sConString="Provider=" + sProvider + ";Data Source=" + sDataSource +";User ID=" + sUserId + ";Password=" +sPassword +";Initial Catalog="+ sDB;
				
                //Transa
                //OleDbConnection oDSTSCon=new OleDbConnection(sConString);
                //OleDbTransaction oTransaction;
                //oDSTSCon.Open();
                //oTransaction=oDSTSCon.BeginTransaction();
                //TransactionContext tc = null;
                //try
                //{	
                //    OleDbCommand oCMD=new OleDbCommand(sSQL,oDSTSCon,oTransaction);
                //    res= oCMD.ExecuteNonQuery();
                //    if (isCheck)
                //        oTransaction.Rollback();
                //    else
                //        oTransaction.Commit();
                //}

                //catch(Exception e)
                //{
                //    oTransaction.Rollback();
                //    throw new Exception(e.Message);
                //}
               
                try
                {
                    if (sUserId.ToLower() == "windows authentication")
                        sConString = "server=" + sDataSource + ";Database=" + sDB + ";integrated security=yes;";
                    else
                        sConString = "Provider=" + sProvider + ";Data Source=" + sDataSource + ";User ID=" + sUserId + ";Password=" + sPassword + ";Initial Catalog=" + sDB;

                    SqlConnection conn = new SqlConnection(sConString);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sSQL, conn);
                    res=cmd.ExecuteNonQuery();
                    
                }
                catch (Exception exp)
                {
                    throw new Exception(exp.Message);
                }
			}
			return res;
			
		}
	}
}