<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>jQuery Example - Image Swap Preload - Design Chemical Bangkok</title>
    <link href="/css/style.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

      <script type="text/JavaScript">
        // prepare the form when the DOM is ready
        $(document).ready(function() {
            $(".img-swap-1").hover(
          function() {
              this.src = this.src.replace("_off", "_on");
              this.style.marginTop = "-25px";
          },
          function() {
              this.src = this.src.replace("_on", "_off");
              this.style.marginTop = "0px";
          });
        });
        $.fn.preload = function() {
            this.each(function() {
                $('<img/>')[0].src = this;
            });
        }
    </script>

    <style type="text/css">
        .onehalf
        {
            margin-bottom: 15px;
        }
        .onehalf h3
        {
            margin-bottom: 15px;
        }
        .onehalf ul li
        {
            margin-bottom: 5px;
        }
        .onehalf ul li img
        {
            cursor: pointer;
        }
        .dvOutPlan
        {
            float: left;
            cursor: pointer;
            width: 245px;
        }
    </style>
</head>
<body>
    <div style="background-color: #fafafa; width: 100%; padding-bottom: 10px; border-left: solid 1px #666666;
        border-right: solid 1px #666666; float: left; padding-top: 32px;" align="center">
        <div class="onehalf float-left">
            <div id="dvPremium" class="dvOutPlan">
                <img src="images/Premium_off.jpg" alt="1" class="img-swap-1" />
            </div>
            <div id="dvPlus" class="dvOutPlan">
                <img src="images/Premium_off.jpg" alt="2" class="img-swap-1" />
            </div>
            <div id="dvBasic" class="dvOutPlan">
                <img src="images/Premium_off.jpg" alt="3" class="img-swap-1" />
            </div>
            <div id="dvLite" class="dvOutPlan">
                <img src="images/Premium_off.jpg" alt="3" class="img-swap-1" />
            </div>
        </div>
    </div>
</body>
</html>
        On Login Button Click
==================================
           //Remeber me functionality
            if (chkRememberMe.Checked == true)
            {
                Response.Cookies["UName"].Value = txtLogin.Text;
                Response.Cookies["PWD"].Value = txtPassword.Text;
                Response.Cookies["UName"].Expires = DateTime.Now.AddMonths(2);
                Response.Cookies["PWD"].Expires = DateTime.Now.AddMonths(2);
            }
            else
            {
                Response.Cookies["UName"].Expires = DateTime.Now.AddMonths(-1);
                Response.Cookies["PWD"].Expires = DateTime.Now.AddMonths(-1);
            }


on page load
===================
//for remember me funcationality
            if (Request.Cookies["UName"] != null)
                txtLogin.Text = Request.Cookies["UName"].Value;
            if (Request.Cookies["PWD"] != null)
                txtPassword.Attributes.Add("value", Request.Cookies["PWD"].Value);
            if (Request.Cookies["UName"] != null && Request.Cookies["PWD"] != null)
                chkRememberMe.Checked = true;



in sql server : 2008

select object_name(referenced_object_id) as [primary table], object_name(parent_object_id) [child table]
from sys.foreign_keys
WHERE object_name(referenced_object_id) ='PatientMaster'
ORDER by object_name(parent_object_id)


in sql server : 2005
select o1.name, o2.name
from sysforeignkeys fk
join sysobjects o1 on o1.id = fk.fkeyid
join sysobjects o2 on o2.id = fk.rkeyid
where o2.name = 'PatientMaster'

/*************.ascx page*********************/

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<asp:TextBox ID="txtDate" runat="server" MaxLength="11"></asp:TextBox>
<ajax:CalendarExtender ID="clndrDate" runat="server" TargetControlID="txtDate" Format="MM/dd/yyyy"
    CssClass="MyCalendar">
</ajax:CalendarExtender>
<div class="mandatory">
<asp:RequiredFieldValidator ID="reqUCDate" runat="server"
    ControlToValidate="txtDate" Display="Dynamic" SetFocusOnError="true"
    ErrorMessage="please select date" Text="*"></asp:RequiredFieldValidator>
<asp:RangeValidator
    ID="rangeUCDate"
    runat="server"
    SetFocusOnError="true"
    ControlToValidate="txtDate"
    ErrorMessage="Please enter valid date"
    Type="Date" Display="Dynamic"
    Enabled="false" />
</div>
<input id="txtCurrDate" type="hidden" value="<%=DateTime.Now.AddHours(20).ToString("MM/dd/yyyy") %>"/>
<asp:TextBox ID="txtTempDate" Visible="false" runat="server" MaxLength="11"></asp:TextBox>   




/*************.                  Code Behind Page           *********************/
/// <summary>
    /// This Control is use for Date Textbox with Calender control on page
    /// For this control use on page some of functions are created in javascript for validation
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //DateRequired = false;
        }
    }

    #region Variable

    private string date;

    #endregion

    #region Property
    /// <summary>
    /// This Property use for get and set date in dd MMM yyyy format
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    public string Date
    {
        get
        {
            date = txtDate.Text;
            return date;

        }
        set
        {
            date = value;
            if (date != string.Empty)
            {
                txtDate.Text = Convert.ToDateTime(date).ToString("MM/dd/yyyy");
            }
            else
            {
                txtDate.Text = "";
            }
        }

    }

    /// <summary>
    /// This Property is used to set width of data control
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    public int DateControlWidth
    {
        set
        {
            txtDate.Width = value;
        }
    }

    /// <summary>
    /// This Property is used to set height of date control
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    public int DateControlHeight
    {
        set
        {
            txtDate.Height = value;
        }
    }

    /// <summary>
    /// This Property is used to make date cotrol read only
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    public bool DateControlReadOnly
    {
        set
        {
            txtDate.ReadOnly = value;
            clndrDate.TargetControlID = "txtTempDate";
        }
        get
        {
            return txtDate.ReadOnly;
        }
    }

    /// <summary>
    /// This property used to make date mandatory
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    public bool DateRequired
    {
        set
        {
            reqUCDate.Enabled = value;
        }
    }

    public string ValidationGroup
    {
        set
        {
            reqUCDate.ValidationGroup = value;
            rangeUCDate.ValidationGroup = value;
        }
    }

    #endregion

    #region Methods
    /// <summary>
    /// Used to set maximum date
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    /// <param name="dt"></param>
    public void DateMaximum(DateTime dt)
    {
        rangeUCDate.Enabled = true;
        rangeUCDate.MaximumValue = dt.ToString("MM/dd/yyyy");
        rangeUCDate.MinimumValue = new DateTime(1600, 01, 01).ToString("MM/dd/yyyy");
    }

    /// <summary>
    /// Used to set minimum date
    /// </summary>
    /// <CreatedBy>Khushant Dhingra</CreatedBy>
    /// <CreatedDate>20/may/2011</CreatedDate>
    /// <param name="dt"></param>
    public void DateMinimum(DateTime dt)
    {
        rangeUCDate.Enabled = true;
        rangeUCDate.MinimumValue = dt.ToString("MM/dd/yyyy");
        rangeUCDate.MaximumValue = DateTime.Now.AddYears(50).ToString("MM/dd/yyyy");
    }
   
    #endregion
/*Below Different Tab controls themes*/

/* ajax__tab_xp-theme theme */

.Tab .ajax__tab_header
{
    color: #4682b4;
    font-family: Calibri;
    font-size: 14px;
    font-weight: bold;
    background: url("../../Images/black_navi.jpg") repeat-x;
    margin-left: 0px;
    width: 980px;
    height: 38px;
    vertical-align: middle;
}
/*Body*/
.Tab .ajax__tab_body
{
    border: 1px solid #b4cbdf;
    padding-top: 0px;
    width: 978px;
    padding-bottom: 10px;
}
/*Tab Active*/
.Tab .ajax__tab_active .ajax__tab_tab
{
    color: #75BECF;
    background: url("../../Images/tab_active.gif") repeat-x;
    height: 20px;
}
.Tab .ajax__tab_active .ajax__tab_inner
{
    color: #75BECF;
    background: url("../../Images/tab_active.gif") no-repeat left;
    padding-left: 10px;
}
.Tab .ajax__tab_active .ajax__tab_outer
{
    color: #75BECF;
    background: url("../../Images/tab_active.gif") no-repeat right;
    padding-right: 6px;
    margin-top: 8px;
}
/*Tab Hover*/
.Tab .ajax__tab_hover .ajax__tab_tab
{
    color: #75BECF;
    background: url("../../Images/tab_hover.gif") repeat-x;
    height: 20px;
}
.Tab .ajax__tab_hover .ajax__tab_inner
{
    color: #75BECF;
    background: url("../../Images/tab_hover.gif") no-repeat left;
    padding-left: 10px;
}
.Tab .ajax__tab_hover .ajax__tab_outer
{
    color: #75BECF;
    background: url("../../Images/tab_hover.gif") no-repeat right;
    padding-right: 6px;
}
/*Tab Inactive*/
.Tab .ajax__tab_tab
{
    color: #ffffff;
    background: url("../../Images/tab_Inactive.gif") repeat-x;
    height: 20px;
}
.Tab .ajax__tab_inner
{
    color: #ffffff;
    background: url("../../Images/tab_inactive.gif") no-repeat left;
    padding-left: 10px;
}
.Tab .ajax__tab_outer
{
    padding-right: 6px;
    margin-right: 1px;
    border-right: 1px solid #FFFFFF;
}
/*Above Different Tab controls theme*/


/*Below Calendar controls themes*/
.MyCalendar .ajax__calendar_container
{
    background-color: #e2e2e2;
    border: solid 1px #cccccc;
}

.MyCalendar .ajax__calendar_header
{
    background-color: #ffffff;
    margin-bottom: 4px;
}


.MyCalendar .MyCalendar .ajax__calendar_next, .MyCalendar .ajax__calendar_prev
{
    color: #004080;
    padding-top: 3px;
}

.MyCalendar .ajax__calendar_title
{
    color: #004080;
    padding-bottom:5px;
}

.MyCalendar .ajax__calendar_body
{
    background-color: #e9e9e9;
    border: solid 1px #cccccc;
}

.MyCalendar .ajax__calendar_dayname
{
    text-align: center;
    font-weight: bold;
    margin-bottom: 4px;
    margin-top: 2px;
}

.MyCalendar .ajax__calendar_day
{
    text-align: center;
}

.MyCalendar .ajax__calendar_hover .ajax__calendar_day, .MyCalendar .ajax__calendar_hover .ajax__calendar_month, .MyCalendar .ajax__calendar_hover .ajax__calendar_year, .MyCalendar .ajax__calendar_active
{
    color: #004080;
    font-weight: bold;
    background-color: #ffffff;
}

.MyCalendar .ajax__calendar_today
{
    font-weight: bold;
    padding-top:0px;
    padding-bottom:5px;
}

.MyCalendar .ajax__calendar_other, .MyCalendar .ajax__calendar_hover .ajax__calendar_today, .MyCalendar .ajax__calendar_hover .ajax__calendar_title
{
    color: #bbbbbb;
}
/*Above Calendar controls theme*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace DataBaseService
{
    public partial class DataBaseService : IDataBaseService
    {
        public static string ConnectionString = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["con"]);

        static SqlConnection GlobalConn()
        {
            SqlConnection sql = new SqlConnection(ConnectionString);
            return sql;
        }

        public  DataSet GetDataSet(string uspName, string[] paramArray, object[] valueArray)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            int i;
            dataSet = new DataSet();
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                sqlDataAdapter.Fill(dataSet);

            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public DataSet GetDataSetWithOutParam(string spName)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            dataSet = new DataSet();
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = spName;
                sqlDataAdapter.Fill(dataSet);
            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public DataSet GetDataSetReturnWithParam(string uspName, string[] paramArray, object[] valueArray, string OutParamName, out Int32 OutParam)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            int i;
            dataSet = new DataSet();
            OutParam = 0;
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
               
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                //Set out parameter
                sqlCommand.Parameters.AddWithValue(OutParamName, OutParam);
                sqlCommand.Parameters[OutParamName].Direction = ParameterDirection.Output;
                sqlCommand.Parameters[OutParamName].DbType = System.Data.DbType.Int32;
                sqlDataAdapter.Fill(dataSet);
                if (sqlCommand.Parameters[OutParamName] != null)
                {
                    OutParam = Convert.ToInt32(sqlCommand.Parameters[OutParamName].Value); //sqlCommand.Parameters.Add(OutParamName, SqlDbType.Int)
                }
            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public DataSet GetDataSetWithReturnValue(string uspName, string[] paramArray, object[] valueArray, out Int32 OutParam)
        {
            SqlConnection con = null;
            DataSet dataSet;
            SqlDataAdapter sqlDataAdapter;
            SqlCommand sqlCommand;
            int i;
            dataSet = new DataSet();
            OutParam = 0;
            string OutParamName = "@ReturnValue";

            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;

                sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                //Set out parameter
                sqlCommand.Parameters.AddWithValue(OutParamName, OutParam);
                sqlCommand.Parameters[OutParamName].Direction = ParameterDirection.ReturnValue;
                sqlCommand.Parameters[OutParamName].SqlDbType = SqlDbType.Int;
                sqlDataAdapter.Fill(dataSet);
                if (sqlCommand.Parameters[OutParamName] != null)
                {
                    OutParam = Convert.ToInt32(sqlCommand.Parameters[OutParamName].Value); //sqlCommand.Parameters.Add(OutParamName, SqlDbType.Int)
                }
            }
            catch (Exception Ex)
            {
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataSet;
        }

        public  DataTable GetDataTable(string uspName, string strParam, string strMain)
        {
            SqlConnection con = null;
            DataTable dataTable;
            SqlDataAdapter sqlDataAdapter;
            dataTable = new DataTable();
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;
                SqlCommand sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                sqlCommand.Parameters.Add(strParam, SqlDbType.VarChar).Value = strMain;
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem in Getting Data.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return dataTable;
        }

        public  void ExecuteSPReturn(string uspName)
        {
            SqlConnection con = null;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlCommand.Connection = con;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                sqlCommand.ExecuteNonQuery();
               
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
        }

        public  object ExecuteSPReturnWithParam(string uspName, string[] ParamArr, object[] ValueArr, object OutParam)
        {
            SqlConnection con = null;
            string str;
            SqlCommand sqlCommand;
            int i;
            try
            {
                str = "";
                sqlCommand = new SqlCommand();
                con = GlobalConn();
               
                SqlConnection sqlCon = con;
                sqlCon.Open();
                sqlCommand.Connection = sqlCon;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                if ((ParamArr.Length.Equals(0)) || (ValueArr.Length.Equals(0)))
                {
                    return "";
                }
                if (!ParamArr.Length.Equals(ValueArr.Length))
                {
                    return "";
                }
                i = 0;
                while (i < ParamArr.Length)
                {
                    if (ParamArr[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(ParamArr[i], ValueArr[i]);
                    }
                    str = string.Concat(str, ",", ValueArr[i].ToString());
                    i++;
                }
                sqlCommand.Parameters["@OutParam"].Direction = ParameterDirection.Output;
                sqlCommand.Parameters["@OutParam"].DbType = System.Data.DbType.Int64;
                sqlCommand.ExecuteNonQuery();
                OutParam = sqlCommand.Parameters.Add("@OutParam", SqlDbType.BigInt).Value;
                sqlCon.Close();
               
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return OutParam;
        }

        public  long SetAddDataXML(string uspName, DataSet ds, long ID )
        {
            SqlConnection con = null;
            object obj;
            string str;
            SqlDataAdapter sqlDataAdapter;
            obj = new object();
            str = "";
            obj = 0;
            try
            {
                sqlDataAdapter = new SqlDataAdapter();
                sqlDataAdapter.SelectCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlDataAdapter.SelectCommand.Connection = con;
               
                SqlCommand sqlCommand = sqlDataAdapter.SelectCommand;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                MemoryStream memoryStream = new MemoryStream();
                ds.WriteXml(memoryStream);
                memoryStream.Position = 0;
                StreamReader streamReader = new StreamReader(memoryStream);
                str = streamReader.ReadToEnd();
                sqlCommand.Parameters.AddWithValue("@AddData", str);
                //sqlCommand.Parameters.AddWithValue("@MasterType", mstType);
                sqlCommand.Parameters["@Outparam"].Direction = ParameterDirection.Output;
                sqlCommand.ExecuteNonQuery();
                obj = sqlCommand.Parameters.Add("@Outparam", SqlDbType.BigInt).Value;

              


            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution. Unable to save data.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return Convert.ToInt64(obj);
        }

        public  void ExecuteSQLQuery(string sStr)
        {
            SqlConnection con = null;
            try
            {
               
                SqlCommand sqlCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlCommand.Connection = con;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sStr;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution. Unable to do the operation.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
        }


        public int ExecuteNonQueryWithReturn(string uspName, string[] paramArray, object[] valueArray)
        {
            SqlConnection sqlCon = null;

            try
            {

                int result = 0;
                SqlCommand sqlCommand = new SqlCommand();
                sqlCon = GlobalConn();
                sqlCon.Open();

                sqlCommand.Connection = sqlCon;

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                int i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;


                return result;

            }
            catch (Exception Ex)
            {

                throw new Exception(Ex.Message, Ex);

            }
            finally
            {
                sqlCon.Close();
            }
          
        }

        public int ExecuteNonQueryWithReturn(string uspName, string param, System.Data.SqlTypes.SqlXml value)
        {
            SqlConnection sqlCon = null;

            try
            {

                int result = 0;
                SqlCommand sqlCommand = new SqlCommand();
                sqlCon = GlobalConn();
                sqlCon.Open();

                sqlCommand.Connection = sqlCon;

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
               
                sqlCommand.Parameters.Add(param, SqlDbType.Xml);
                sqlCommand.Parameters[param].Value = value.Value.Replace(" xmlns=\"http://tempuri.org/\"", "");
               
                SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;


                return result;

            }
            catch (Exception Ex)
            {

                throw new Exception(Ex.Message, Ex);

            }
            finally
            {
                sqlCon.Close();
            }

        }



        public int ExecuteNonQueryWithSaveXML(string uspName, string[] paramArray, object[] valueArray, string xmlparam, System.Data.SqlTypes.SqlXml value)
        {
            SqlConnection sqlCon = null;

            try
            {

                int result = 0;
                SqlCommand sqlCommand = new SqlCommand();
                sqlCon = GlobalConn();
                sqlCon.Open();

                sqlCommand.Connection = sqlCon;

                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                int i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
              
                sqlCommand.Parameters.Add(xmlparam, SqlDbType.Xml);
                sqlCommand.Parameters[xmlparam].Value = value.Value.Replace(" xmlns=\"http://tempuri.org/\"", "");

                SqlParameter myParm = sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;


                return result;

            }
            catch (Exception Ex)
            {

                throw new Exception(Ex.Message, Ex);

            }
            finally
            {
                sqlCon.Close();
            }

        }




        public int ExecuteSPWithReturn(string uspName)
        {
            SqlConnection con = null;
            int result = 0;
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                con = GlobalConn();
                con.Open();

                sqlCommand.Connection = con;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
               
                sqlCommand.Parameters.Add("@ReturnValue", SqlDbType.Int);
                sqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

                sqlCommand.ExecuteNonQuery();
                result = (Int32)sqlCommand.Parameters["@ReturnValue"].Value;
            }
            catch (Exception Ex)
            {
                //GlobalConnection.dtlMessageBox = new SgtDetailMsgBox("Problem occured during execution.", Ex.Message);
                throw new Exception(Ex.Message, Ex);
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int ExecuteNonQuery(string uspName, string[] paramArray, object[] valueArray)
        {
            SqlConnection sqlCon = null;
            try
            {
                int result=0;
                SqlCommand sqlCommand = new SqlCommand();
                 sqlCon= GlobalConn();
                sqlCon.Open();
                sqlCommand.Connection = sqlCon;
                   
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = uspName;
                int i = 0;
                while ((((!paramArray.Length.Equals(0)) && (!valueArray.Length.Equals(0))) && (paramArray.Length.Equals(valueArray.Length))) && (i < paramArray.Length))
                {
                    if (paramArray[i] != null)
                    {
                        sqlCommand.Parameters.AddWithValue(paramArray[i], valueArray[i]);
                    }
                    i++;
                }
                result = sqlCommand.ExecuteNonQuery();
                sqlCon.Close();
              return result;
             
            }
            catch (Exception Ex)
            {
               
                throw new Exception(Ex.Message, Ex);
               
            }
            finally
            {
                sqlCon.Close();
            }
          
        }

        
    }
}
/// <summary>
            /// Encrypt a string using dual encryption method. Return a encrypted cipher Text
            /// </summary>
            /// <param name="toEncrypt">string to be encrypted</param>
            /// <param name="useHashing">use hashing? send to for extra secirity</param>
            /// <returns></returns>
            public static string Encrypt(string toEncrypt, bool useHashing)
            {
                byte[] keyArray;
                byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);

                //System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
                // Get the key from config file
                //string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));
                //By Rohit kumar
                string key = "truworth infotech limited";
                //System.Windows.Forms.MessageBox.Show(key);
                if (useHashing)
                {
                    MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                    keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                    hashmd5.Clear();
                }
                else
                    keyArray = UTF8Encoding.UTF8.GetBytes(key);

                TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
                tdes.Key = keyArray;
                tdes.Mode = CipherMode.ECB;
                tdes.Padding = PaddingMode.PKCS7;

                ICryptoTransform cTransform = tdes.CreateEncryptor();
                byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
                tdes.Clear();
                return Convert.ToBase64String(resultArray, 0, resultArray.Length);
            }


            /// <summary>
            /// DeCrypt a string using dual encryption method. Return a DeCrypted clear string
            /// </summary>
            /// <param name="cipherString">encrypted string</param>
            /// <param name="useHashing">Did you use hashing to encrypt this data? pass true is yes</param>
            /// <returns></returns>
            public static string Decrypt(string cipherString, bool useHashing)
            {
                byte[] keyArray;
                byte[] toEncryptArray = Convert.FromBase64String(cipherString);

                //System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
                //Get your key from config file to open the lock!
                //string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));
                //BY Rohit kumar
                string key = "truworth infotech limited";

                if (useHashing)
                {
                    MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
                    keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
                    hashmd5.Clear();
                }
                else
                    keyArray = UTF8Encoding.UTF8.GetBytes(key);

                TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
                tdes.Key = keyArray;
                tdes.Mode = CipherMode.ECB;
                tdes.Padding = PaddingMode.PKCS7;

                ICryptoTransform cTransform = tdes.CreateDecryptor();
                byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);

                tdes.Clear();
                return UTF8Encoding.UTF8.GetString(resultArray);
            }