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.