Performing DataGrid Insert Using WCF

in this lab you will be doing Insert using Silverlight DataGrid and WCF Service.
 
Task 1: Designing Database Table:
 
Start a SQL Server instance and create a database of named "Company" and table "PersonMaster" using the following queries:

Create Database Company

Use Company

CREATE TABLE [dbo].[PersonMaster]( [PersonId] [int] NOT NULL, [PersonName] [varchar](50) NOT NULL, [Address] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [State] [varchar](50) NOT NULL, [Age] [int] NOT NULL,

CONSTRAINT [PK_PersonMaster] PRIMARY KEY CLUSTERED

(

[PersonId]

ASC

)WITH (PAD_INDEX

= OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS

= ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
 
Task 2: Creating WCF Service.
 
In this task you will create a WCF Service. This service will have methods for retrieving all Persons from the table and create a new Person.
 
Step 1: Open VS2010 and create a blank solution, name it "SILV4_DataGrid_DML_Lab".
 
Step 2: In this solution add a new WCF Service Application, name it "WCF40_DMLService".
 
(Note: You need to choose .NET Framework 4.0.). Rename "IService1.cs" to "IService.cs" and rename "Service1.svc" to "Service.svc".
 
Step 3: Open IService.cs and write the following interface and class with a DataContract in it:

using System.Runtime.Serialization; using System.ServiceModel;

namespace WCF40_DMLService

{

    [ServiceContract]

    public interface IService

    {

        [OperationContract]

        PersonMaster[] GetAllPerson();

        [OperationContract]

        void CreatePersonRecord(PersonMaster objPer);

    }

    [DataContract]

    public class PersonMaster

    {

        [DataMember]

        public int PersonId { get; set; }

        [DataMember]

        public string PersonName { get; set; }

        [DataMember]

        public string Address { get; set; }

        [DataMember]

        public string City { get; set; }

        [DataMember]

        public string State { get; set; }

        [DataMember]

        public int Age { get; set; }

    }

}
 
Step 4: Right-click on Service.Svc and select "View Markup" and write the following code in it:
 

<%@ ServiceHost Language="C#" Debug="true" Service="WCF40_DMLService.Service" CodeBehind="Service.svc.cs" %>


Step 5: Open Service.svc.cs and implement the "IService" interface in the "Service" class as below:
 

using System;

using System.Data;

using System.Data.SqlClient;

 

namespace WCF40_DMLService

{

    public class Service : IService

    {

        SqlConnection Conn; SqlCommand Cmd;

        public Service()

        {

            Conn = new SqlConnection("Data Source=.;Initial Catalog=Company;Integrated Security=SSPI");

        }

        public PersonMaster[] GetAllPerson()

        {

            PersonMaster[] lstPerson = null; Conn.Open();

            Cmd = new SqlCommand();

            Cmd.Connection = Conn;

            Cmd.CommandText = "Select * from PersonMaster";

 

            SqlDataReader Reader = Cmd.ExecuteReader();

            DataTable DtPerson = new DataTable();

            DtPerson.Load(Reader);

            lstPerson = new PersonMaster[DtPerson.Rows.Count]; int i = 0;

            foreach (DataRow rw in DtPerson.Rows)

            {

                lstPerson[i] = new PersonMaster();

                lstPerson[i].PersonId = Convert.ToInt32(rw["PersonId"]); lstPerson[i].PersonName = rw["PersonName"].ToString(); lstPerson[i].Address = rw["Address"].ToString(); lstPerson[i].City = rw["City"].ToString(); lstPerson[i].State = rw["State"].ToString(); lstPerson[i].Age = Convert.ToInt32(rw["Age"]);

                i++;

            }

            Conn.Close(); return lstPerson;

        }

        public void CreatePersonRecord(PersonMaster objPer)

        {

            Conn.Open();

            Cmd = new SqlCommand(); Cmd.Connection = Conn;

            Cmd.CommandText = "Insert into PersonMaster Values(@PersonId,@PersonName,@Address,@City,@State,@Age)";

            Cmd.Parameters.AddWithValue("@PersonId", objPer.PersonId); Cmd.Parameters.AddWithValue("@PersonName", objPer.PersonName); Cmd.Parameters.AddWithValue("@Address", objPer.Address); Cmd.Parameters.AddWithValue("@City", objPer.City); Cmd.Parameters.AddWithValue("@State", objPer.State); Cmd.Parameters.AddWithValue("@Age", objPer.Age);

            Cmd.ExecuteNonQuery();

            Conn.Close();

        }

    }

}


The two methods above perform the required operations.
 
Step 6:
Build the service and publish it on IIS.