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;
//}
}
