Visit 'LiveWorld24.tk' for Live world..............

using System;

using System.Collections;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ComponentModel;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Drawing;

using System.Windows.Forms;

using net.ERP.Utility;

namespace net.ERP.Utility

{

    public class OperationGateway

    {

        private ArrayList mprialDependentTableNameList;

        public static int mpubiExecuteSQLStmt(string pstrSQL)

        {

            int liTotalEffectedRecords = 0;

            try

            {

                SqlCommand mprisqlcommSqlCommand = null;

                if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)

                {

                    DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                }

                DatabaseConnector.gmpubstasqlconnSQLConnection.Open();

                mprisqlcommSqlCommand = new SqlCommand(pstrSQL, DatabaseConnector.gmpubstasqlconnSQLConnection);

                liTotalEffectedRecords = mprisqlcommSqlCommand.ExecuteNonQuery();

                DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                return liTotalEffectedRecords;

            }

            catch (SqlException psqlexpStmtExecution)

            {

                DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                MessageBox.Show(psqlexpStmtExecution.Message.ToString());

                return liTotalEffectedRecords;

            }

        }

        public static bool mpubstabPrimaryKeyValueNotUsedAsForeignKeyValue(ArrayList larrLstlDependentTableNameList, ArrayList larrLstlInquiryColumnName, string lstrInquiryColumnValue)

        {

            bool PrimaryKeyValueNotUsedAsForeignKeyValue = true;

            try

            {

                if (DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.State == ConnectionState.Open)

                {

                    DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();

                }

                for (int i = 0; i < larrLstlDependentTableNameList.Count; i++)

                {

                    DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Open();

                    SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.CreateCommand();

                    lsqlcommSQLCommand.CommandText = "SELECT " + larrLstlInquiryColumnName[i] + " FROM " + larrLstlDependentTableNameList[i] + " WHERE " + larrLstlInquiryColumnName[i] + " = '" + lstrInquiryColumnValue + "'";

                    SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();

                    while (lsqldrSqlDataReader.Read())

                    {

                        PrimaryKeyValueNotUsedAsForeignKeyValue = false;

                        break;

                    }

                    lsqldrSqlDataReader.Close();

                    DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();

                }

                return PrimaryKeyValueNotUsedAsForeignKeyValue;

            }

            catch (SqlException psqlexpRecordExistance)

            {

                DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                MessageBox.Show(psqlexpRecordExistance.Message.ToString());

                return PrimaryKeyValueNotUsedAsForeignKeyValue;

            }

            finally

            {

                DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();

            }

        }

        public bool mpubiIsExistsRecord(string pstrTableName, string pstrColumnName, string pstrCheckingValue)

        {

            try

            {

                int liReturnValue = -1;

                if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)

                {

                    DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                }

                DatabaseConnector.gmpubstasqlconnSQLConnection.Open();

                SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnection.CreateCommand();

                lsqlcommSQLCommand.CommandText = "SELECT " + pstrColumnName + " FROM " + pstrTableName + " WHERE " + pstrColumnName + " = '" + pstrCheckingValue + "'";

                SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();

                while (lsqldrSqlDataReader.Read())

                {

                    liReturnValue = 1;

                }

                lsqldrSqlDataReader.Close();

                DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                if (liReturnValue == -1)

                {

                    return false;

                }

                else

                {

                    return true;

                }

            }

            catch (SqlException psqlexpRecordExistance)

            {

                DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                MessageBox.Show(psqlexpRecordExistance.Message.ToString());

                return false;

            }

        }

        //ADDED BY BINDESHWAR

        //METHOD PURPOSE: RETURNS A REQUIRED COLUMN VALUE IN A RECORD BY SELECTING IT USING WHERE CLAUSE WITH ANOTHER INQUIRY COLUMN VALUE IN THE SAME RECORD.

        public static string mpristrGetOneColumnValueByAnotherColumnValue(string lstrTableName, string lstrRequiredColumnName, string lstrInquiryColumnName, string lstrInquiryColumnValue)

        {

            string lstrReturnRequiredColumnValue = "";

            try

            {

                if (DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.State == ConnectionState.Open)

                {

                    DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();

                }

                DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Open();

                SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.CreateCommand();

                lsqlcommSQLCommand.CommandText = "SELECT " + lstrRequiredColumnName + " FROM " + lstrTableName + " WHERE " + lstrInquiryColumnName + " = '" + lstrInquiryColumnValue + "'";

                SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();

                while (lsqldrSqlDataReader.Read())

                {

                    //CODE MODIFIED BY BINDESHWAR

                    lstrReturnRequiredColumnValue = Convert.ToString(lsqldrSqlDataReader[lstrRequiredColumnName]);

                    //CODE MODIFIED BY BINDESHWAR

                }

                lsqldrSqlDataReader.Close();

                DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();

                return lstrReturnRequiredColumnValue;

            }

            catch (SqlException psqlexpRecordExistance)

            {

                DatabaseConnector.gmpubstasqlconnSQLConnectionTemp.Close();

                MessageBox.Show(psqlexpRecordExistance.Message.ToString());

                return lstrReturnRequiredColumnValue;

            }

            //finally

            //{

            //    DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

            //}

        }

        //METHOD PURPOSE: RETURNS A REQUIRED COLUMN VALUE IN A RECORD BY SELECTING IT USING WHERE CLAUSE WITH ANOTHER  TWO INQUIRY COLUMN VALUE IN THE SAME RECORD.

        public static string mpubstastrGetOneColumnValueByTwoColumnValues(string lstrTableName, string lstrRequiredColumnName, string lstrInquiryColumnNameOne, string lstrInquiryColumnValueOne, string lstrInquiryColumnNameTwo, string lstrInquiryColumnValueTwo)

        {

            string lstrReturnRequiredColumnValue = "";

            try

            {

                if (DatabaseConnector.gmpubstasqlconnSQLConnection.State == ConnectionState.Open)

                {

                    DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                }

                DatabaseConnector.gmpubstasqlconnSQLConnection.Open();

                SqlCommand lsqlcommSQLCommand = DatabaseConnector.gmpubstasqlconnSQLConnection.CreateCommand();

                lsqlcommSQLCommand.CommandText = "SELECT " + lstrRequiredColumnName + " FROM " + lstrTableName + " WHERE " + lstrInquiryColumnNameOne + " = '" + lstrInquiryColumnValueOne + "' AND " + lstrInquiryColumnNameTwo + " = '" + lstrInquiryColumnValueTwo + "'";

                SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();

                while (lsqldrSqlDataReader.Read())

                {

                    //CODE MODIFIED BY BINDESHWAR

                    lstrReturnRequiredColumnValue = Convert.ToString(lsqldrSqlDataReader[lstrRequiredColumnName]);

                    //CODE MODIFIED BY BINDESHWAR

                }

                lsqldrSqlDataReader.Close();

                DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                return lstrReturnRequiredColumnValue;

            }

            catch (SqlException psqlexpRecordExistance)

            {

                DatabaseConnector.gmpubstasqlconnSQLConnection.Close();

                MessageBox.Show(psqlexpRecordExistance.Message.ToString());

                return lstrReturnRequiredColumnValue;

            }

        }

        private void mprivInitDependentTableNameList()

        {

            mprialDependentTableNameList = new ArrayList();

            mprialDependentTableNameList.Clear();

            mprialDependentTableNameList.Add("LAND_REGISTRATION");

        }

    }

}