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

This post is all about creating the application

First we need to add the service reference

Give the Namespace as DataProvider

Now we'll go to the coding Here i have added the coding for the buttons Both button click events will have to be async.

   private async void btnGetStudent_Click(object sender, RoutedEventArgs e)
    {
        //Create a client object to access the service
        DataProvider.DataProviderClient serviceClient = new DataProvider.DataProviderClient();

        //Get the student id and convert it to integer
        int ID = Convert.ToInt32(txtStudentID.Text);

        //Get the student via the service
        DataProvider.Student student = await serviceClient.getStudentAsync(ID);

        //The service will return null if the student is not available
        //If student is not found show a message
        if (student==null)
        {
            Windows.UI.Popups.MessageDialog message = new Windows.UI.Popups.MessageDialog("Student not found");
            message.ShowAsync();
            return;
        }

        //Set the values to the appropriate labels
        lblFirstName.Text = student.FirstName;
        lblLastName.Text = student.LastName;
    }

    private async void btnGetAllStudents_Click(object sender, RoutedEventArgs e)
    {
        //Create a client object to access the service
        DataProvider.DataProviderClient serviceClient = new DataProvider.DataProviderClient();

        //Get the student objects from the service
        ObservableCollection students = await serviceClient.getStudentsAsync();

        //Add the objects to the list view
        foreach (DataProvider.Student student in students)
        {
            lstStudents.Items.Add(student.FirstName+" "+student.LastName);
        }
    }

Download Code

Please Leave a comment about the post.

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.