TechSapphire Wednesday, 2018-11-21, 5:45 AM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • 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.

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2018