THIS IS DATABASE (SPMS)Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

/// <summary>
/// Summary description for SPMS
/// </summary>
public class SPMS
{
    string cnnstr = System.Configuration.ConfigurationManager.AppSettings["xyzcon"];
    SqlConnection CNN = new SqlConnection();
    public SPMS()
    {
        CNN.ConnectionString = cnnstr;
    }

    public long AddNewRecords(DataSet ds)
    {
        SqlTransaction SQLT = null;
        int recId = 0;
        bool Berror = false;
        SqlCommand CMD = new SqlCommand();
        try
        {
              try
            {
                if (CNN.State == ConnectionState.Closed)
                    CNN.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
            SQLT = CNN.BeginTransaction();
            CMD.Transaction = SQLT;
            CMD.Connection = CNN;
            DataTable dttripstart = new DataTable();
            dttripstart = ds.Tables[0];
            CMD.CommandType = CommandType.StoredProcedure;
            CMD.CommandText = "SaveAddressRecord1";
            CMD.Parameters.Clear();
            CMD.Parameters.AddWithValue("@title", dttripstart.Rows[0]["Title"]);
            CMD.Parameters.AddWithValue("@Firstname", dttripstart.Rows[0]["Firstname"]);
            CMD.Parameters.AddWithValue("@Middlename", dttripstart.Rows[0]["Middlename"]);
            SqlParameter SQLPar = new SqlParameter();
            SQLPar = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt);
            SQLPar.Direction = ParameterDirection.ReturnValue;

            recId = CMD.ExecuteNonQuery();
           //int i = (int)CMD.Parameters["RETURN_VALUE"].Value;
            if (recId == 0)
            {
                Berror = true;

            }

            if (Berror == true)
            {
                CMD.Transaction.Rollback();
                return 0;
            }
            else
            {
                CMD.Transaction.Commit();

            }

        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (CNN.State != ConnectionState.Closed)
                CNN.Close();
        }
        return recId;

    }
    public long UpdateRecords(DataSet ds,int i)

    {
        SqlTransaction SQLT = null;
        int recId = 0;
        bool Berror = false;
        SqlCommand CMD = new SqlCommand();
        try
        {
            try
            {
                if (CNN.State == ConnectionState.Closed)
                    CNN.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
            SQLT = CNN.BeginTransaction();
            CMD.Transaction = SQLT;
            CMD.Connection = CNN;
            DataTable dttripstart = new DataTable();
            dttripstart = ds.Tables[0];
            CMD.CommandType = CommandType.StoredProcedure;
            CMD.CommandText = "updaterecord23";
            CMD.Parameters.Clear();
            CMD.Parameters.AddWithValue("@id", i);
            CMD.Parameters.AddWithValue("@title", dttripstart.Rows[0]["Title"]);
            CMD.Parameters.AddWithValue("@Firstname",dttripstart.Rows[0]["Firstname"]);
            CMD.Parameters.AddWithValue("@Middlename", dttripstart.Rows[0]["Middlename"]);
            SqlParameter SQLPar = new SqlParameter();
            SQLPar = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt);
          SQLPar.Direction = ParameterDirection.ReturnValue;
            recId = CMD.ExecuteNonQuery();
            //int i = (int)CMD.Parameters["RETURN_VALUE"].Value;
            if (recId == 0)
            {
                Berror = true;

            }

            if (Berror == true)
            {
                CMD.Transaction.Rollback();
                return 0;
            }
            else
            {
                CMD.Transaction.Commit();

            }

        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (CNN.State != ConnectionState.Closed)
                CNN.Close();
        }
        return recId;

    }

    public  DataSet GetRecordTripDetail()
    {
        DataSet dsGlobal = new DataSet();
        try
        {
            SqlDataAdapter adapGlobal = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();
          try
            {
                if (CNN.State == ConnectionState.Closed)
                    CNN.Open();
            }
            catch (Exception ex)
            {
                CNN.Close();

            }
            if (CNN.State == ConnectionState.Closed)
                CNN.Open();
            cmd.Connection = CNN;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
            cmd.CommandText = "jktveiwdata1";
            adapGlobal.SelectCommand = cmd;
            adapGlobal.Fill(dsGlobal);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            CNN.Close();

        }

        return dsGlobal;


    }
    public DataSet GetContryMaster()

    {
        DataSet dsGlobal = new DataSet();
        try
        {
            SqlDataAdapter adapGlobal = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand();
            try
            {
                if (CNN.State == ConnectionState.Closed)
                    CNN.Open();
            }
            catch (Exception ex)
            {
                CNN.Close();

            }
            if (CNN.State == ConnectionState.Closed)
                CNN.Open();
            cmd.Connection = CNN;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Clear();
            cmd.CommandText = "GetCountryName";
            adapGlobal.SelectCommand = cmd;
            adapGlobal.Fill(dsGlobal);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            CNN.Close();

        }

        return dsGlobal;


    }

    public DataSet GetDatabyRecid(int ik)
    {

        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        try
        {
            cmd.Connection = CNN;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetRecordsByRecId";
            cmd.Parameters.AddWithValue("@RecId", ik);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            if (ds.Tables.Count > 0)
            {
                return ds;

            }

        }
        catch (Exception ex)
        {
            CNN.Close();
            return ds;
        }
        return ds;

    }


    public DataSet GetStateName(int ik)

    {

        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        try
        {
            cmd.Connection = CNN;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetSateName";
            cmd.Parameters.AddWithValue("@Recid", ik);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            if (ds.Tables.Count > 0)
            {
                return ds;

            }

        }
        catch (Exception ex)
        {
            CNN.Close();
            return ds;
        }
        return ds;

    }

    public long DeletetripRecord(int ik)
    {
        SqlTransaction SQLT = null;

        int recId = 0;
        bool Berror = false;
        SqlCommand CMD = new SqlCommand();
        try
        {
            //CNN.ConnectionString = cnnstr;

            try
            {
                if (CNN.State == ConnectionState.Closed)
                    CNN.Open();
            }
            catch (Exception ex)
            {
                throw ex;

            }
            finally
            {
            }
            // CNN.Open();
            SQLT = CNN.BeginTransaction();
            CMD.Transaction = SQLT;
            CMD.Connection = CNN;
            //DataTable dttripstart = new DataTable();
            //dttripstart = ds.Tables[0];
            CMD.CommandType = CommandType.StoredProcedure;
            CMD.CommandText = "DelEeg11";
            CMD.Parameters.Clear();
            CMD.Parameters.AddWithValue("@RecId", ik);
            //CMD.Parameters.AddWithValue("@Starttrip", dttripstart.Rows[0]["TripStart"]);
            //CMD.Parameters.AddWithValue("@endtrip", dttripstart.Rows[0]["TripEnd"]);
            SqlParameter SQLPar = new SqlParameter();
            SQLPar = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt);
            SQLPar.Direction = ParameterDirection.ReturnValue;

            recId = CMD.ExecuteNonQuery();
            int i = (int)CMD.Parameters["RETURN_VALUE"].Value;
            if (i == 0)
            {
                Berror = true;

            }

            if (Berror == true)
            {
                CMD.Transaction.Rollback();
                return 0;
            }
            else
            {
                CMD.Transaction.Commit();

            }

        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (CNN.State != ConnectionState.Closed)
                CNN.Close();
        }
        return recId;




    }

}