Lesson 8 : A little of ADO.net

Hi.  I hope you are finding the lessons easy to follow.  Now that we know how to access data from a web service,  we should take a look at accessing data from a database . I will be using ADO.net here to get data to the web service.  This is very important, because most of our applications will be storing their data in databases, and our application needs to access this data.

We will be covering the following topics in this lesson:

1) Connecting to database
2) Accessing a single value, and  range of data
3) Handling data into Custom classes.
4) Inserting into the database

Step 1:  Connecting to a database.

1) Create a new Silverlight Project, and name it Lesson8.
2) Add a WCF service as a new project to the solution and name it DatabaseService1
3) Open the Service1.svc.cs
   You can observe that some namespaces are added by default to the code.
   To access data from the database, specifically SQL Server, we need to add the following namespaces:

   System.Data;
   System.Data.SqlClient;

4a) The System.Data  namespace contains the DataTable and related classes, that are used to store the data retrieved from the database.
4b) The System.Data.SqlClient   namespace contains the classes specific for accessing data from Sql Server. 

   

5)  Now, we need to establish a connection to the Sql Server, and access data from a database.

6)  Now , there is a Tool for using Sql Server,  Microsoft Sql Server Management Studio, that comes with Microsoft Sql Server 2008 .  Go to Start Menu -> Microsoft Sql Server 2008 -> Management Studio.
  

           


7) To the left of the screen, you will find Object Explorer pane.  You can create a new Database in it by clicking on Databases- > New database
    

      

8) A prompt opens for the Database name.   Name it Sample Data , and press ok.

            
9)  In the object explorer pane, now expand the databases tab to find the newly created Sample Data   database.   Expand it to find the Tables group.  You can right click on it and Add a new Table.

10) Let us create a table called Users, with three fields,  Name, emailid  and Password . the data type for all the 3 fields is varchar(50) . Uncheck the Allow Nulls checkbox.

              
11) Save the table by pressing Ctrl+S . A prompt appears for the table name. Name it Users

12) Now in the Object Explorer, go to the Sampledata database, then to tables group and expand it.  You should see the dbo.Users  table present there.  Right click on that and select "Edit top 200 rows"

13)  Now you should be able to enter sample data there.
14) Note the name of your server. It is present in the tab above the sample data here.
 

        

 Thats all we want with sql Server Management studio. Save it  and we can go back to Visual Studio now.


Step 2a: Accessing a Single value 

1) In the Service1.svc ,  create a new method  public void getData()
   

2) Inside this, we first need to connect to the Sql Server database, so we use the SqlConnection class to create this. Add the following code in the getData()  method.  Change the YOUR_SERVER_NAME_HERE
to your Server name.


string connectionString="Initial Catalog=SampleData;"+"Data Source=YOUR_SERVER_NAME_HERE ;"+"Integrated Security=SSPI;";SqlConnection sCon = new SqlConnection(connectionString);  
Initial catalog refers to your Database name.
Data Source is your server name
The authentication is default windows authentication, so we use Integrated Security=SSPI 
If your database has a username and password, then you can use
Userid="user_name" ; Password="Password"

For now, we will use this  above connection string.

3)  We need to execute a Command to get data from the database.   So we will create a SqlCommand to get the data. Add the following code

stringsqlCmd = "Select emailid from Users where name='a'";SqlCommand scmd = new SqlCommand(sqlCmd, sCon);
4a)  We enclose our entire data access code within a try - catch- finally block, so that any exceptions can be handled. 

4b) Add the following code after the above statements
    try    {
    }

    {
    }    catch (Exception exc)    finally    {
    }
Your code should look something like this now

5) The sqlCmd query returns a single value, the email id  of the user with name 'a'.   Since it is a single value, we can use the ExecuteScalar method of the SqlCommand object.

6a)  Before you run the SqlCommand, you need to open the connection to the database.  So add the following code .
    string useremailid = scmd.ExecuteScalar().ToString();
6b) In the above lines, the connection to the database is opened, and a query for fetching a single value is executed.  that value is sent into the useremailid variable.

6c) We need to close the connection now, so we will close it in the finally block.
      Your code should now look like this

Step 2b: Accessing a range of Data

1) To store a set of values, we need to have a data table , what can hold the data.
     So instead of a string variable we have a DataTable object.

2) So first let us modify our query. We will select all the user names and passwords from the Users table.

3a) Now , inside the try block, we want to create a new DataTable object, and a SqlDataAdapter object.
    the SqlDataAdapter gets the range of data and stores it in the DataTable.
   Add the following code to the try block
   sda.Fill(dt);

   Add the following code to the try block

3b)  Notice that we dont need to open the sql connection.  The SqlDataAdapter automatically opens the connection and closes it.  That means you can remove the sCon object as well as  sCon.Close();   also.
The final code should look like this.   DataTable dt = new DataTable(); 
   SqlDataAdapter sda = new SqlDataAdapter(sqlCmd, connectionString);  
   string sqlCmd = "Select name,password from Users";    

We wont be using the  SqlCommand object ,  scmd  , to fetch the range of data , so you can remove it.

 
 

4) The data table now contains all the names and passwords of the users. 

Step 3: Handling data into custom classes

1) Now we have to  handle this data temporarily .We will create a custom class classed MyUsers, and store this data into it  . Add the following code to the IService1.cs.

    [DataContract]
    public class MyUsers
    {
       string username;
       string password;
    }

   
2)  Now right click on the variables and select Refactor->Encapsulate Field.
     string username;     string password;

3) Now a prompt appears for the name. Press Ok.

   
4)  Repeat the procedure for the email id variable also.

5)  Your custom class is ready.

6) Now we need to assign the data into objects of this class. Go to the try block in Service1.svc.cs , where you have filled data into the data table.  We can create a list of type MyUsers, which will store the data.


    List<MyUsers> myUsers = new List<MyUsers>();
    foreach (DataRow dr in dt.Rows)

7)  In the above code, we are looping through each row of the data, using the foreach loop . Create a new object of type myusers, and add the data from the data row into it.


    MyUsers mTemp = new MyUsers();
    mTemp.Username = dr["name"].ToString();
    mTemp.Password = dr["password"].ToString();   
    myUsers.Add(mTemp);   

    

8)   This ensures that all the data from the data table is converted into the class objects, and stored in that list.

9)  If you have to pass this data to silverlight, you need to change the return type of the getData() method to List<MyUsers>  and return the myUsers  object. Your final code should look like this

10)  Also, you need to add the following code in the iService1.cs  under the interface iService1

 
This is how we get data from the Database.

Step 4: Inserting into the database

1)  We make use of the SqlConection and SqlCommand classes to perform the insertion. 
     Since this insert statement is not a query, i.e, we are not fetching data from the database, 
     we can use the ExecuteNonQuery() method of the SqlCommand.

2)  Add a new function insertData() and  the  following code to it.
      public void insertData()    string connectionString = "Initial Catalog=SampleData;" +    "Data Source=YOUR_SERVER_NAME_HERE ;" +    "Integrated Security=SSPI;";    

   {

     string sqlCmd = "insert into Users values('h','h','h')";     SqlConnection sCon = new SqlConnection(connectionString);    



     try 
     {

       sCon.Open();
       int status= sCmd.ExecuteNonQuery();
     }

     {
     }       SqlCommand sCmd = new SqlCommand(sqlCmd, sCon);    catch (Exception exc)     finally     {
       sCon.Close();
     }
  }
As you can notice, the only difference between this insert and fetching the single value data  is the ExecuteNonQuery() and the ExecuteScalar().

3)  The number of rows changed will be stored in the variable status. If this insert is successful, then status=1.
    

This is how we access data from the SqlServer database into our web service, and from this web service into our Silverlight application.  We will see more of this in the video.

Lesson 7: Accessing data from the WCF service.

Now you know how to create a Silverlight application , and create a Wcf Service.
The WCF service runs independently of the Silverlight application.
All we need to do is just send the data from the Web service in to the Silverlight application. 
To do this, we need to follow the below steps:

1. Create a Service Reference in the Silverlight application.
2. Make asynchronous calls to the WCF service methods.
3. Retrieve the data from the WCF service.


Step 1:  Creating a Service Reference

1.  Create a new Silverlight Application, name it lesson 7 .
2.  In the Solution Explorer, Select the Solution, and Right click -> Add New Project

3. Select WCF -> WCF service application, and name it TestWCF.
4.  Now your solution will have 2 projects , the Silverlight Application and the Wcf Service.

5. Build the solution by pressing F6.
6. Right click on the Silverlight application , and select "Add Service Reference"

6. In the new window that pops up, press Discover.
    The WCF service Service1 will appear. Select it and press OK.

7. When you press OK, the Service Reference will be added to the Solution explorer.
   

 8. Now you can user the namespace ServiceReference1  in your Silverlight application.
     It has the ServiceClient Class, which contains all the methods declared as [OperationContract]

Step 2:  To make asynchronous calls to the WCF service  


1. Inside the MainPage.xaml.cs, we can access the WCf service using the namespace ServiceReference1.
    Create a new class variable    ServiceReference1.Service1Client sClient;
 
2. The method calls to the WCF service will be asynchronous, which means that when you call the method, it does not return the result immediately.

3. The normal execution of the program goes on, and independent of the web service execution. When the web service sends the result ,it raises the completed event.

4. We need to handle the completed event of the methods.
So inside the constructor , i.e  MainPage() , we need to add the following code
    sClient = new ServiceReference1.ServiceClient();
sClient.GetDataCompleted +=
 new EventHandler<ServiceReference1.GetDataCompletedEventArgs> (sClient_GetDataCompleted);

5. You will be prompted to pres TAB , so that the event handler gets created automatically
    

6.  Remove the     " throw new NotImplementedException(); "  from the above event handler.

7.  Before we start, we will drag a button in the MainPage.xaml  in the Silverlight application .
     Double click the button to go to its event handler.
 
8.  Inside the  button click event handler, just call the GetDataAsync method of the web service.

       
    

9. This calls the getData method of the WCF service asynchronously.

Step 3 : Retrieve the data from the WCF service.

1.  In the Completed event handler ( sClient_getDataCompleted ) , we have the variable e as a parameter.
     To obtain the result, we use the property e.Result.
     So display the result in a message box .

2.  Since the return type of the getData was string, we displayed it directly.  If it is some other data type, we need to typecat it accordingly.

3. Now you need to add two XML files to your WCF project folder.
    Go to your  WCF service, Right click -> Add new Item - > XML File -> Name it clientaccesspolicy.xml
    Copy this to your clientaccesspolicy.xaml and save it.
 
<?xml version="1.0" encoding="utf-8"?>
<access-policy>
  <cross-domain-access>
    <policy>
      <allow-from http-request-headers="*">
        <domain uri="*"/>
      </allow-from>
      <grant-to>
        <resource path="/" include-subpaths="true"/>
      </grant-to>
    </policy>
  </cross-domain-access>
</access-policy>

4. Similarly add another xml file and name it crossdomain.xml and add the following code to it.

<?xml version="1.0"  ?>
<!DOCTYPE cross-domain-policy SYSTEM "http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd">
<cross-domain-policy>
  <allow-http-request-headers-from domain="*" headers="*"/>
</cross-domain-policy>
5.  Now press F5 and Run the project .  Click on Button , to see the result.



Thats how we access data from a WCF service.  To see more on how to access data from databases, watch this video.

Lesson 6: Introduction to WCF services.

In Silverlight, you cannot connect to a database directly. You can create visually appealing interfaces, handle events, make user controls , use all logic and .net framework , but you cannot connect directly to a database.

To access data, the Silverlight application needs to call a Web service, which supplies it with data.  The Windows Communication Foundation (WCF) provides you a way to access all your databases by writing all the data access logic within it, and your silverlight application can request data from this WCF service.

Before we create a WCF service, you need to keep in mind that the Web service and the Silverlight application are different applications , that merely exchange data. I am going to show you how to create your first WCF service .

There are only two topics which will be covered in this lesson.
1. Creating the WCF service.
2. Modifying the WCF service.
3. Publishing the WCF service
Step 1:  Creating the WCF service

1. Open Visual Studio -> New Project -> WCF -> WCF Service Application.
    Name it as MyDataService , and click OK.

 

2.  Open Solution explorer, present to the right of the screen and double click on IService1.cs
     You will find an Interface and a class there.  

3.  The methods in the interface have an attribute  [OperationContract] , which is used to specify that this method can be called from the Silverlight application.

4.  The class has an attribute [DataContract] , which indicates that this class object can be sent from the WCF service to the Silverlight application and vice versa. 

5. Only the members of the class which have [DataMember] attribute will be accessible in the silverlight application.

6.  The methods defined in the interface are implemented in the Service1.cs  file , which is obtained by expanding the Service1.svc  in the Solution Explorer.

7.  If you want to create your own class, with its data members, you need to give the [DataContract] attribute to that class also , and its members should be given [DataMember] attribute.


Step 2 : Modifying the WCF service

1. We will create a new method in the interface IService1.  Add the following code in it

     [OperationContract]
     int getStatus(string name);

2.  Now save this, and go to Service1.svc.cs .  We need to implement the new method defined in the interface. So add the following code there

3.  That all  :)   You have created your web service.  You can call this getStatus method, which takes a string as a parameter, and returns you the status as 1 if length is greater than 10 , and 0 if lesser.


How to actually use these WCF services will be covered in the next lesson. For a more detailed explanation and examples, check this video.

Lesson 5: Working with user controls

You have already seen and used some of the available controls like TextBlock, Button etc in Silverlight . Well, you can create your own controls also, making use of the existing controls. Such controls are also referrred to as User controls.

There may be many reasons to use our own controls, like extending functionality of the control. In this lesson, we will see the following.

1. Creating a simple user control.
2. Adding additional functionality to the user control.
3. Using the user control in our application.

Step 1.  Creating a simple user control.

1. Create a new Silverlight application in Expression Blend and Go to the Project pane on the right.
    Name it Lesson5.
2. Right click on the Lesson5  ->  Add a new Item

3. Now select  User Control  and name it MyButton.xaml

4.  In the toolbar to the left, select the Grid control and double click it.
 

5. Stretch the grid to fit the screen, and change the color of the grid .  To change the color, click the second tab highlighted below and drag your mouse on the colors .   

6. To view the xaml, click on this icon present beside the properties pane.

  
Press V to get the selection tool.
Now Resize the d.DesignWidth  and d.DesignHeight to 450 and 50 respectively

6.  Drag a button and a textbox (right click the textblock icon to get the options)

Press V to get the selection tool.
and arrange the control as shown below

7.  Select the textbox and edit the name of the textbox in the top right corner, as shown in  the picture.
     similarly , chage the name of button to btnSubmit.

8. Remove the content of the textbox, and change the content of button to Submit.

Step 2: Adding functionality to the user control

1.  Now select the button, and click on the events icon in the properties pane and type an event handler name beside Click event.

2. Double click on event handler name to generate the event handler stub.

3.         Now add a public variable to the class.     public string name;
              We will take the text from the textbox and  assign it to the variable name.
          
              So inside the button click event handlet, add the following code.
              name = txtbox1.Text;   

4. Build the project once by pressing Ctrl + Shift + B . and go to MainPage.xaml.
       The user control will now be available in the toolbox to the left.
      ( Click the expand link , and go to the project category inside it. )
     

5.  You can now drag the control onto your grid .  In fact, you can drag multiple copies of the control.
  Add a name to the controls in the top right corner, as mentioned earlier in step 7.  i am giving b1, b2,b3 respectively.

6.  In the Mainpage.Xaml.Cs , you can access the property name  from these controls if you want.
 

      

Tha advantage, as you can see is reusability.  Instead of draggin 3 text boxes, and 3 buttons, and assigning event handlers to each of them , we did it only once, and reused the component.    :)   Users controls are very useful , and we'll see more of them in the lessons to come.   For  more detailed examples, please watch the video.

Lesson 4. Event Handling

This lesson will deal with events and how to handle them. You will be using  event handling in every step of application development as you start developing bigger applications. So best get familiar with it right now :)

We will be going through the following topics.

1. Accessing Events for a control
2. Event handling through code.

Step 1: Accessing Events for a control.

1. Create a new Silverlight Application and drag a Button control on to the Grid.
    Right Click ->   Properties  ->   The property pane opens to the right side of the screen.
    Click on the lightning symbol , which represents events.

 
Different controls have different events associated with them. As you can notice, there are many events associated with this Button control. 

2. To add an event handler for an event, just type a new event handler name beside the event.

3. If you double click the event handler name , you will be taken to the code file and the event handler stub  will be automatically generated.

 4.  You can write your functionality inside this event handler.  For now, i am going to display a message that reads HI.   Add             MessageBox.Show("HI"); inside buttonclick

5. When you  press F5 to Run the application , a message box pops up when you click the button.

You can change the properties of other controls using the event handlers. For example, you could change the background color of the application when you click a button. 


Step 2 :  Event handling through code.

You can add an event handler directly from code instead of doing it from the Properties pane.  In the previous lesson (Lesson 3), we added an event handler for the completed event. 


There are 2 steps to handle an event ,  First step is to associate the event with a handler  and the second step is to write the functionality in the handler.

1.  We add an event handler to a control using the  += operator.   In reality, it uses the concept of delegates {but that concept is not really needed right now}
  
2.  Add a new button to the Grid, and to add a click event to our button, we simply write
     button2.click += .      inside the MainPage()  constructor.
    Visual studio provides auto completion for the event handler by pressing the TAB key twice.

3. Write the MessageBox.Show("Hi")  code inside this method, and press F5 to Run

4. The output will be the same as earlier. 

5. If for some reason you want to remove the event handler at a later stage, then you can use the  -= operator. 

This way , you can handle all the events for any control.  For a more detailed explanation and demo, watch the video.