Connecting Windows 8 Store Application with SQL Server using WCF - Part 1

Hi Readers This post is about connecting Windows 8 Store application and SQL server using WCF. The Part 1 is about creating the WCF service. First lets create the Database.

CREATE DATABASE LearnerDB;

USE LearnerDB;

CREATE TABLE Students
(
 ID INT IDENTITY PRIMARY KEY,
 FirstName VARCHAR(100),
 LastName VARCHAR(100)
);

INSERT INTO Students VALUES('John','Doe');
INSERT INTO Students VALUES('Mark','Moe');
INSERT INTO Students VALUES('Grace','Goe');
INSERT INTO Students VALUES('Paula','Poe');
INSERT INTO Students VALUES('John','Rambo');
INSERT INTO Students VALUES('Jane','Joe');

in Visual Studio 2012 create a WCF service project

Then create a new service called "DataProvider"  (You can use the IService which comes as default as well)

 Let go the coding Part

   [ServiceContract]
    public interface IDataProvider
    {
        [OperationContract]
        Student getStudent(int ID);

        [OperationContract]
        List getStudents();
    }

    [DataContract]
    public class Student
    {
        [DataMember]
        public int ID { get; set; }

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

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

The student class is going to be used as the entity class to represent the table. The actual coding for the Service will be in the DataProvider Class

    public class DataProvider : IDataProvider
    {
        //Get the information of a single student
        public Student getStudent(int ID)
        {
            DataAccess dataAccess = new DataAccess();
            return dataAccess.getStudent(ID);
        }

        //Get information of all students
        public List getStudents()
        {
            DataAccess dataAccess = new DataAccess();
            return dataAccess.getStudents();
        }
    }

The data provider uses the dataaccess class to communicate with the database

public class DataAccess
{
    //Connection to the Database
    SqlConnection DBconnection;

    public DataAccess()
    {
        //Get the connection from the Web config
        string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
        DBconnection = new SqlConnection(connectionString);
    }

    /// Get the information based on Student ID
    public Student getStudent(int ID)
    {
        if (DBconnection.State==ConnectionState.Closed)
        {
            DBconnection.Open();
        }

        Student student = null;

        SqlCommand command = new SqlCommand("select * from students where ID ="+ID,DBconnection);
        SqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            student = new Student()
            {
                ID = ID,
                FirstName = reader["FirstName"].ToString(),
                LastName = reader["LastName"].ToString()
            };
        }
        return student;
    }

    /// Get a list of all students
    public List getStudents()
    {
        if (DBconnection.State == ConnectionState.Closed)
        {
            DBconnection.Open();
        }

        List students = new List();

        SqlCommand command = new SqlCommand("select * from students", DBconnection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            students.Add(new Student()
            {
                ID = Convert.ToInt32(reader["ID"].ToString()),
                FirstName = reader["FirstName"].ToString(),
                LastName = reader["LastName"].ToString()
            });
        }
        return students;
    }
}

After creating the service you can test it using WCF Test Client.