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
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
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
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
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
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.
|