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.