TechSapphire - Insert, Delete, Update and Select using WCF services

Insert, Delete, Update and Select using WCF services

WCF (Window communication foundation) services are easy to build. Steps need to follow are:
Step 1: Define Data contract (classes which you want to share among client for communication format)
Step 2: Define Service Contract (Interface containing declaration of all methods exposed to client) and declaring method in service interface which you want to expose to client.
Step 3: Implementing and defining methods in service class.
Step 4: Adding reference to client project.
Step 5: Making service client object.
Step 6: You are ready to use now.

For database creation:

Step 1: Create database with name "".

Step 2: Run below script:

USE [WCFContactDB]
GO

/****** Object: Table [dbo].[ContactTable] Script Date: 02/17/2016 08:48:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ContactTable] (
 [ContactID] [int] IDENTITY(1, 1) NOT NULL
 ,[ContactName] [varchar](100) NOT NULL
 ,[ContactAddress] [varchar](100) NULL
 ,[ContactNumber] [varchar](100) NULL
 ,[ContactDOB] [datetime] NULL
 ,[ContactIncome] [money] NULL
 ,CONSTRAINT [PK_ContactTable] PRIMARY KEY CLUSTERED ([ContactID] ASC) WITH (
 PAD_INDEX = OFF
 ,STATISTICS_NORECOMPUTE = OFF
 ,IGNORE_DUP_KEY = OFF
 ,ALLOW_ROW_LOCKS = ON
 ,ALLOW_PAGE_LOCKS = ON
 ) ON [PRIMARY]
 ) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

/****** Object: StoredProcedure [dbo].[UpdateContact] Script Date: 02/17/2016 08:48:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateContact] (
 @ContactID INT
 ,@ContactName VARCHAR(100)
 ,@ContactAddress VARCHAR(100)
 ,@ContactNumber VARCHAR(100)
 ,@ContactDOB DATETIME
 ,@ContactIncome MONEY
 )
AS
BEGIN
 UPDATE dbo.ContactTable
 SET ContactName = @ContactName
 ,ContactAddress = @ContactAddress
 ,ContactNumber = @ContactNumber
 ,ContactDOB = @ContactDOB
 ,ContactIncome = @ContactIncome
 WHERE ContactID = @ContactID
END
GO

/****** Object: StoredProcedure [dbo].[SelectAllContact] Script Date: 02/17/2016 08:48:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SelectAllContact]
AS
BEGIN
 SELECT ContactID
 ,ContactName
 ,ContactAddress
 ,ContactNumber
 ,ContactDOB
 ,ContactIncome
 FROM ContactTable
END
GO

/****** Object: StoredProcedure [dbo].[InsertContact] Script Date: 02/17/2016 08:48:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertContact] (
 @ContactName VARCHAR(100)
 ,@ContactAddress VARCHAR(100)
 ,@ContactNumber VARCHAR(100)
 ,@ContactDOB DATETIME
 ,@ContactIncome MONEY
 )
AS
BEGIN
 INSERT INTO dbo.ContactTable (
 ContactName
 ,ContactAddress
 ,ContactNumber
 ,ContactDOB
 ,ContactIncome
 )
 VALUES (
 @ContactName
 ,@ContactAddress
 ,@ContactNumber
 ,@ContactDOB
 ,@ContactIncome
 );

 SELECT SCOPE_IDENTITY();
END
GO

/****** Object: StoredProcedure [dbo].[DeleteContact] Script Date: 02/17/2016 08:48:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteContact] (@ContactID INT)
AS
BEGIN
 DELETE
 FROM dbo.ContactTable
 WHERE ContactID = @ContactID
END
GO

C# code:

Service contract:

[ServiceContract]
 public interface IWCFContact
 {

 [OperationContract]
 Int32 Insert(WCFContact ContactPar);

 [OperationContract]
 int Update(WCFContact ContactPar);

 [OperationContract]
 List<WCFContact> SelectAll();

 [OperationContract]
 int Delete(WCFContact ContactPar);
 // TODO: Add your service operations here
 }

Data contract:

 [DataContract]
 public class WCFContact
 {
 private Int32 contactID;
 private String contactName;
 private String contactAddress;
 private String contactNumber;
 private DateTime contactDOB;
 private Decimal contactIncome;

 [DataMember]
 public Int32 ContactID
 {
 get
 {
 return contactID;
 }
 set
 {
 contactID = value;
 }
 }
 [DataMember]
 public string ContactName
 {
 get
 {
 return contactName;
 }
 set
 {
 contactName = value;
 }
 }
 [DataMember]
 public string ContactAddress
 {
 get
 {
 return contactAddress;
 }
 set
 {
 contactAddress = value;
 }
 }
 [DataMember]
 public string ContactNumber
 {
 get
 {
 return contactNumber;
 }
 set
 {
 contactNumber = value;
 }
 }
 [DataMember]
 public DateTime ContactDOB
 {
 get
 {
 return contactDOB;
 }
 set
 {
 contactDOB = value;
 }
 }
 [DataMember]
 public decimal ContactIncome
 {
 get
 {
 return contactIncome;
 }
 set
 {
 contactIncome = value;
 }
 }
 }

Implementation of service contact in service.svc

public class WCFContactService : IWCFContact
 {
 string ConString = "Server=YOGESHMEHLA-PC;Database=WCFContactDB;Trusted_Connection=True;";
 public int Insert(WCFContact ContactPar)
 {
 using (SqlConnection con = new SqlConnection(ConString))
 {
 using (SqlCommand cmd = new SqlCommand("dbo.InsertContact", con))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@ContactName", SqlDbType.VarChar, 100).Value = ContactPar.ContactName;
 cmd.Parameters.Add("@ContactAddress", SqlDbType.VarChar, 100).Value = ContactPar.ContactAddress;
 cmd.Parameters.Add("@ContactNumber", SqlDbType.VarChar, 100).Value = ContactPar.ContactNumber;
 cmd.Parameters.Add("@ContactDOB", SqlDbType.DateTime).Value = ContactPar.ContactDOB;
 cmd.Parameters.Add("@ContactIncome", SqlDbType.Money).Value = ContactPar.ContactIncome;
 con.Open();
 var ID = cmd.ExecuteScalar();
 ContactPar.ContactID = Convert.ToInt32(ID.ToString());
 }
 }
 return ContactPar.ContactID;
 }
 public int Update(WCFContact ContactPar)
 {
 using (SqlConnection con = new SqlConnection(ConString))
 {
 using (SqlCommand cmd = new SqlCommand("dbo.UpdateContact", con))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactPar.ContactID;
 cmd.Parameters.Add("@ContactName", SqlDbType.VarChar, 100).Value = ContactPar.ContactName;
 cmd.Parameters.Add("@ContactAddress", SqlDbType.VarChar, 100).Value = ContactPar.ContactAddress;
 cmd.Parameters.Add("@ContactNumber", SqlDbType.VarChar, 100).Value = ContactPar.ContactNumber;
 cmd.Parameters.Add("@ContactDOB", SqlDbType.DateTime).Value = ContactPar.ContactDOB;
 cmd.Parameters.Add("@ContactIncome", SqlDbType.Money).Value = ContactPar.ContactIncome;
 con.Open();
 var ID = cmd.ExecuteNonQuery();
 return ID;
 }
 }
 }
 public List<WCFContact> SelectAll()
 {
 List<WCFContact> SelectList = new List<WCFContact>();
 using (SqlConnection con = new SqlConnection(ConString))
 {
 using (SqlCommand cmd = new SqlCommand("dbo.SelectAllContact", con))
 {
 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 DataSet ds = new DataSet();
 da.Fill(ds);
 if (ds.Tables.Count > 0)
 {
 foreach (DataRow dr in ds.Tables[0].Rows)
 {
 SelectList.Add(new WCFContact
 {
 ContactAddress = dr["ContactAddress"].ToString(),
 ContactDOB = Convert.ToDateTime(dr["ContactDOB"]),
 ContactID = Convert.ToInt32(dr["ContactID"]),
 ContactIncome = Convert.ToDecimal(dr["ContactIncome"]),
 ContactName = Convert.ToString(dr["ContactName"]),
 ContactNumber = Convert.ToString(dr["ContactNumber"])
 });
 }
 }
 }
 }
 }
 return SelectList;
 }
 public int Delete(WCFContact ContactPar)
 {
 using (SqlConnection con = new SqlConnection(ConString))
 {
 using (SqlCommand cmd = new SqlCommand("dbo.DeleteContact", con))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactPar.ContactID;
 con.Open();
 var ID = cmd.ExecuteNonQuery();
 return ID;
 }
 }
 }
 }

Note: To use first create database then run sql script.

Click here to download scripts and project.