My Master's Thesis Problems and solutions encountered…

4Jul/102

Retrieving data from a MS SQL DB

This post is basically just a recap of the former, and aims to quickly show how to retrieve data form the MS SQL database through WCF Services all the way through my MVVM design pattern and to my Silverlight application. The steps in this posts will be repeated many times in my application, as I will go through them each time I have to retrieve a piece of data from the database.

This posts presupposes that the user has followed the steps in my recent post, explaining how to setup the MVVM design pattern: MVVM Structure. Also, it's preferable that the user has followed the steps in my last post, as it explains how to overcome some of the small problems that I encountered when deploying the WCF Service: This blog post just recaps the whole thing (but ads some smaller structure changes, in steps 1-3), and will not be as thorough as my last blog post.

I have made two changes:

First, I have decided to add two new folders in my Model project, called Model and Data_access, respectively. The Model folder contains all classes whose attributes we want to retrieve to the Silverlight application. The Data_access folder contains all classes that actually calls the database, runs the query, and returns the desired values. This has been done so as to ease the overview, as I otherwise would end up with way to much code in IService1.svc.cs

Also, I have made a DBconnection.cs class in the Data_access folder that all other classes in the folder inherit.

So, from the start:

Step 1: Create two folders in the Model project, called Model and Data_access, respectively.

Step 2: Add a DBconnection class in your Model folder containing the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace Model.Data_access
{
    public class DBConnection
    {
        public SqlConnection connectToDB()
        {
            string connstring = "Data Source=********;Initial Catalog=*******;User ID=********;Password=*********;Integrated Security=False;";
            SqlConnection dbConn = new SqlConnection(connstring);

            return dbConn;
        }

    }
}

Step 3: Create a new class in your Model folder named after the place in your application, where you want to insert the data you retrieve from the database. I have called mine Client_Home.cs. In this class you will merely define the attributes of the object, and create their properties. Notice the DataContract and DataMember.

*
using System.Runtime.Serialization;

namespace Model.Model
{
    [DataContract]
    public class Client_Home
    {

        string active_parentQuestionnaire_;

        [DataMember]
        public string Active_parentQustionnaire
        {
            get { return active_parentQuestionnaire_; }
            set { active_parentQuestionnaire_ = value; }
        }

    }
}

Step 4: Go to your Data_access folder, and create the class that will call the database and populate the attributes defined in the Client_Home class created in the last step. Depending on what you use your data for, it could be usefull to name the class after the tables you mainly retrieve the data from... but I have decided (for now) just to name it after the class it is related to from the Model folder. Hence, I have called mine Client_HomeDA.cs.

Step 5: Now, in the class that you have just created: For each attribute that you have declared in your Client_Home.cs, you will create a method that retrieves that data from the database. My first (and for now, only) attribute in my Client_Home is named ActiveQuestionnaire, as I just want to retrieve the name of the active questionnaire of the current user. So, in my Client_HomeDA.cs I will create a method that has a userID as a parameter:

using System.Data.SqlClient;
using Model.Model;

namespace Model.Data_access
{
    public class Client_Home_DA : DBConnection
    {

  public string GetActiveQuestionnare(int UID)  //In this case we just want to retrieve a string, and not a list of objects, like in the previous example.
        {

            string client =""; //Empty string
            SqlConnection dbConn = connectToDB();
            string _query = string.Format("SELECT parent_questionnaire.title FROM parent_questionnaire, accessible WHERE accessible.active = 1 AND accessible.pq_ID = parent_questionnaire.pq_ID AND u_ID = " + UID);

            try
            {
                dbConn.Open();
                SqlCommand cmd = new SqlCommand(_query, dbConn);
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {

                    client = (string)rdr[0];
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

            return client;
        }
  }

}

Notice that the class inherits the DBConnection class.

Step 6: Go to the IService1.cs, where you will make the GetActiveQuestionnaire available. This class is basically the link between the server and the client, and whatever methods you define in this class have to be also in the one of the classes you have created in the Data_access folder.


*
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using Model.Model;

namespace Model
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        string GetActiveQuestionnare(int UID);
    }
}

Step 7: Go to your Service1.svc.cs, where you will create a new instance of the Client_Home, and call the Client_Home_DA, that will populate the attributes with data from the database, and return it. Notice that we have added the Using Model.Model and Model.Data_access.

*
using System.Data.SqlClient;
using Model.Model;
using Model.Data_access;

namespace Model
{

    public class Service1 : IService1
    {
          public string GetActiveQuestionnare(int UID)
        {
            string cli;
            Client_Home_DA cliDA = new Client_Home_DA();
            cli = cliDA.GetActiveQuestionnare(UID);
            return cli; 

        }
    }
}

Step 8: Publish by right-clicking on you Model project >> Publish.

Step 9: Right-click on your Service Reference that you created in step 11 in my last post, and click 'Update Service Reference'.

Step 10: The next step is to create a class in your ViewModel, that will be connected to the .xaml page, where you want to insert the data. I want to insert the data in a page called Client_Home, so I create a class called Client_Home_ViewModel.cs, that will contain the necessary properties and eventhandlers. Remember that you have to have published and updated your Service References before you can execute the code in the constructor.

Also notice that you have to inherit the INotifyPropertyChanged .

*
using ViewModel.QMServiceReference;
using System.ComponentModel; 

namespace ViewModel
{
    public class Client_Home_ViewModel : INotifyPropertyChanged //Important!!!!
    {
        private string activeQuestionnaire_;
        public string ActiveQuestionnaire
        {//Property
            get { return activeQuestionnaire_;  }
            set { activeQuestionnaire_ = value;
            RaisePropertyChanged("ActiveQuestionnaire");
            }
        }
         public Client_Home_ViewModel()
        {//Constructor, calls WebService
            int UID = 2; //Temporary hardcoding.
            QMServiceReference.Service1Client WebService = new Service1Client();
            WebService.GetActiveQuestionnareCompleted += new EventHandler<GetActiveQuestionnareCompletedEventArgs>(WebService_GetActiveQuestionnareCompleted);
            WebService.GetActiveQuestionnareAsync(UID); 

        }

         void WebService_GetActiveQuestionnareCompleted(object sender, GetActiveQuestionnareCompletedEventArgs e)
         {
             ActiveQuestionnaire = e.Result; //Return value, sets property

         }
        public event PropertyChangedEventHandler PropertyChanged;
        private void RaisePropertyChanged(string propertyname)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyname));
            }
        }

    }
}

Step 11: The next step is of course to define the textblock in your .xaml code (mine is Client_Home.xaml), where you want to display the result of your query. Notice that we bind to ActiveQuestionnaire, which is the property that we defined in the code in the previous step:

 <TextBlock Grid.Row="1" Grid.ColumnSpan="4" Grid.Column="1" Foreground="#FF696969" Text="{Binding ActiveQuestionnaire}"/>

Step 12: The very last thing to do is to connect your Client_Home.xaml to the Client_Home_ViewModel, which is done by opening your Client_Home.xaml.cs and instantiating the Client_Home_ViewModel():

*
using ViewModel;

namespace View
{
    public partial class Client_Home : Page
    {
        public Client_Home()
        {
            InitializeComponent();
            this.DataContext = new Client_Home_ViewModel();
        }

       protected void Page_Loaded(object sender, RoutedEventArgs e)
        {
         }

    }
}

And so, these are the steps that must be repeated again and again and again, everytime data is retrieved from the database...

Enjoy!

Ps. In this post I have hardcoded the UserID that I needed, but I will soon blog about how to parse the data from one page to another, and how you can save data locally... but for now, I will just work on how figuring out what data I need, how the application will look like, which queries are necessary, and how to insert all this into my project.

Comments (2) Trackbacks (0)
  1. Hi,
    I really enjoyed your articles.. just wondering.. why are you not using RIA services.. WCF services for Silverlight was in Silverlight 3 era.
    Happy coding
    Pooran

  2. Hey, I can’t view your site properly within Opera, I actually hope you look into fixing this.


Leave a comment



8 × six =

No trackbacks yet.