Web Config/ interface IService/class Service

web Config:

    <appSettings>
        <add key="connectionxyz" value="Initial Catalog=Test;server=con-23EC9119BAD;integrated security  =true"/>
    </appSettings>


 IService:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IService" in both code and config file together.
[ServiceContract]
public interface IService
{
    [OperationContract]
    DataSet getAllDataForUserList1();
    [OperationContract]
    DataSet GetDatabyRecid(int i);
    [OperationContract]
    long AddNewRecord(DataSet ds);
    [OperationContract]
    long UpdateRecords(DataSet ds1, long i);
    [OperationContract]
    long DeletetripRecord(int i);
    [OperationContract]
    DataSet GetAllCountry();
   
}

// Use a data contract as illustrated in the sample below to add composite types to service operations.
[DataContract]
public class CompositeType
{
    //bool boolValue = true;
    //string stringValue = "Hello ";

    //[DataMember]
    ////public bool BoolValue
    //{
    //    get { return boolValue; }
    //    set { boolValue = value; }
    //}

    //[DataMember]
    //public string StringValue
    //{
    //    get { return stringValue; }
    //    set { stringValue = value; }
    //}
}

Service:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;


// NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service" in code, svc and config file together.
public class Service : IService

{
    string cnnstr = System.Configuration.ConfigurationManager.AppSettings["connectionxyz"];
    SqlConnection CNN = new SqlConnection();
    public Service()
    {
        CNN.ConnectionString = cnnstr;
    }

    public DataSet GetAllCountry()

    {

        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 getAllDataForUserList1()
    {
        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 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 long AddNewRecord(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 ds1, long i)
   {
       SqlTransaction SQLT = null;
       long 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 = ds1.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 long DeletetripRecord(int ik1)
   {

       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;
      
           CMD.CommandType = CommandType.StoredProcedure;
           CMD.CommandText = "DelEeg11";
           CMD.Parameters.Clear();
           CMD.Parameters.AddWithValue("@RecId", ik1);
         
           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;


   }


    //public string GetData(int value)

    //{
    //    return string.Format("You entered: {0}", value);
    //}

    //public CompositeType GetDataUsingDataContract(CompositeType composite)
    //{
    //    if (composite == null)
    //    {
    //        throw new ArgumentNullException("composite");
    //    }
    //    if (composite.BoolValue)
    //    {
    //        composite.StringValue += "Suffix";
    //    }
    //    return composite;
    //}
}