Rami Vemula

NOTE:-

1. In this tutorial we create a simple WCF service which is used to retrieve and save data from Pubs Database (Microsoft sample database).
2. The service also demos on how to use EF 4.0 and LINQ against database coding, with no requirement of writing SQL queries.
3. The continuation of this tutorial shows on how to create a client (say a WebForm in a website) to utilize this service.

Get Started :-

1. Open VS 2010
2. File -> New -> Project
3. Select Visual C# -> WCF (also select .net framework 4.0) -> select WCF Service Application Template.
4. Give Name -> samplePubsService (also give the preferred location to save the solution).
5. Click OK.

Initial Preparation of the Project in the solution:-

1. Delete IService1.cs and Service1.svc, which we get by default at the time of project creation.
2. Download Microsoft Sample Database – PUBS from HERE, else we can create our own database for operation.
3. Extract the Database, Open the SQL file (.sql) of Pubs database -> instpubs.sql in the VS 2010, once opened -> Right click anywhere on the text zone and select -> Execute SQL (this creates our sample database).
4. Now in Server Explorer (view –> server explorer) of VS 2010, Right Click -> Data Connections, Select -> Add Connection.
5. In the open Dialog, Select –> ServerName (typically SQLEXPRESS), and also Select -> Database Name from the DropDownList (pubs).
6. CLick OK. With this, the database part of the project is ready.

Adding a Service:-

1. Right Click the project folder in Solution Explorer of VS 2010, select -> Add new Item, select -> Web, select -> WCF Service Template.
2. Name it -> PubsService.svc

Adding a DataContract:-

1. Right Click the project, select -> Add new Item, select -> web, select -> Class Template.
2. Name it -> Author.cs

Place the below code in it:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace samplePubsService
{
    [DataContract]
    public class Author
    {
        [DataMember]
        public string authorId { get; set; }

        [DataMember]
        public string authorLName { get; set; }

        [DataMember]
        public string authorFName { get; set; }

        [DataMember]
        public string authorCity { get; set; }
    }
}

Similarly add one more DataContract, with name bookByAuthor.cs and place the following code in it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace samplePubsService
{
    [DataContract]
    public class bookByAuthor
    {
        [DataMember]
        public string titleId { get; set; }

        [DataMember]
        public string title { get; set; }

        [DataMember]
        public string type { get; set; }

        [DataMember]
        public DateTime pubdate { get; set; }
    }
}

Adding a ServiceContract:-

Open IPubsService.cs, and place the following code:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace samplePubsService
{
    [ServiceContract]
    public interface IPubsService
    {
        [OperationContract]
        Author GetAuthorById(string authorId);

        [OperationContract]
        List<bookByAuthor> GetBookOfAuthor(Author authorDetails);

        [OperationContract]
        void SaveBookDetails(List<bookByAuthor> booksDetails);

        [OperationContract]
        List<Author> GetAllAuthors();
    }
}

Implement the IPubsService interface in the PubsService.svc.cs:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;

namespace samplePubsService
{
    public class PubsService : IPubsService
    {
        public Author GetAuthorById(string authorId)
        {
            return null;
        }

        public List<bookByAuthor> GetBookOfAuthor(Author authorDetails)
        {
            return null;
        }

        public void SaveBookDetails(List<bookByAuthor> booksDetails)
        {

        }

        public List<Author> GetAllAuthors()
        {
            return null;
        }
    }
}

To Simplify DataBase coding, we Use Entity Framework ORM module, the step by step process is described below –

1. Right Click Project folder in Solution Explorer, Select -> Add new Item, Select -> Data, Select -> ADO.Net Entity Data Model, Name it -> PubsEntityModel.edmx
2. Select -> Generate from Database, Select -> pubs.dbo in the DropDownList, also Check the checkbox to save entity ConnectionString to the Web.Config file.
3. Check all tables; also check the two checkboxes at the bottom. (Checking the checkboxes of views and Stored Procedures is optional).
4. Give name to the namespace -> pubsModel, Click Finish.

The complete model would be (Sorry!! for not getting the image quite clearly) –

But we are interested in only in the below entities (only for the sake of this tutorial) –

This completes Entity Framework Part.

Now let’s code against pubsEntities i.e., the class generated from the Entity Framework (you can find it by double clicking the PubsEntityModel.Designer.cs). So let’s go back to PubsSevice.svc.cs:-

Import the name space System.Data.Entity to use the Entity models.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.Entity;

namespace samplePubsService
{
    public class PubsService : IPubsService
    {
        public Author GetAuthorById(string authorId)
        {
            using (pubsEntities pubs = new pubsEntities())
            {
                Author a = new Author();
                var author = (from p in pubs.authors
                              where p.au_id == authorId
                              select p).First();

                a.authorCity = author.city;
                a.authorFName = author.au_fname;
                a.authorId = author.au_id;
                a.authorLName = author.au_lname;

                return a;
            }
        }

        public List<bookByAuthor> GetBookOfAuthor(Author authorDetails)
        {
            List<bookByAuthor> books = new List<bookByAuthor>();
            using (pubsEntities pubs = new pubsEntities())
            {
                var titleIds = (from p in pubs.titleauthors
                                where p.au_id == authorDetails.authorId
                                select p);

                foreach(titleauthor titleId in titleIds)
                {
                    bookByAuthor book = new bookByAuthor();
                    var bookTitle = (from p in pubs.titles
                                  where p.title_id == titleId.title_id
                                  select p).First();

                    book.title = bookTitle.titleOfBook;
                    book.type = bookTitle.type;
                    book.titleId = bookTitle.title_id;
                    book.pubdate = bookTitle.pubdate;

                    books.Add(book);
                }
            }
            return books;
        }

        public void SaveBookDetails(List<bookByAuthor> booksDetails)
        {
            using (pubsEntities pubs = new pubsEntities())
            {
                foreach (var detail in booksDetails)
                {
                    var book = (from p in pubs.titles
                               where p.title_id == detail.titleId
                               select p).First();

                    book.type = detail.type;
                    book.titleOfBook = detail.title;
                    book.pubdate = detail.pubdate;

                    pubs.SaveChanges();
                }
            }
        }

        public List<Author> GetAllAuthors()
        {
            List<Author> lstAuthors = new List<Author>();
            using (pubsEntities pubs = new pubsEntities())
            {

                var authors = from p in pubs.authors
                              select p;
                foreach (author singleAuthor in authors)
                {
                    Author a = new Author();
                    a.authorCity = singleAuthor.city;
                    a.authorFName = singleAuthor.au_fname;
                    a.authorLName = singleAuthor.au_lname;
                    a.authorId = singleAuthor.au_id;

                    lstAuthors.Add(a);
                }
            }
            return lstAuthors;
        }
    }
}

The folder Structure:-

To Test the PubsService.svc:-

Its Simple -> Press Ctrl + F5

A WCF Test client will open, now double click on any of the method on the left pane, and enter the input manually as shown below and then press Invoke. Results should be displayed in the Bottom Pane.

This Completes the coding part of the Service.

To Follow up with the Client construction, which utilizes this Service. Please Click the below Link – PART II.

Create a Simple WCF Service Client against Pubs Database using EF, LINQ – Part II

Disclaimer:

All coding and suggestions made in the above discussion is strictly in point of view of the author. It is neither mentioned any where nor even concluded that this is the only possible way to go with, as there will be always a chance for a new approach in achieving the same requirement (in context of programming). The above mentioned code is strictly written and explained for the sake of new comers to C# and ASP.NET world.