Loading and Inserting Data from Database in Silverlight Without Using LINQ to SQL Class

Introduction

In this Post I want to talk with Silverlight and it’s interaction with database .  Oh, I thing I want to tell you that I’m using SQL database .  Oracle also may be performed.  I don’t have any knowledge in that . So , basically I’m using SQL -2008 and Visual Studio 2010. These two software you need to make this application. And I’m using Linq Query to retrieved data from database.  I’ll describe this with step by step .

 Ok, if you search in internet or Google “How to connect Database with Silverlight”. I find lots of good and efficient learning resources in there .  Most of them are either using Linq to Sql Class or ADO.NET Entity Framework Class. Because using both of them make you end up writing lots of code and visual studio will do lots of heavy tasks for you.  And when you use WCF Services with them surprising thing is that tools automatically generate a set of proxy classes which are all be based on the design of your database . And also the relationship between one table to another will be automatically designed by the proxy classes. So, in without any doubt we can say that using bothLinq to Sql Class or ADO.NET Entity Data Model are best choice to do work with Database.

Fine, but In my this tiny application I do work with database without help of ADO.NET Entity Data Model or Linq to Sql Class. Yes , you do not need to go add extra item(Linq to SQL ,or etc …) in your server side  in Silverlight Application. To make application get rid of complexibility. I just do work with one table in SQL 2008, latter if I get more time I shall post another article with two or more tables.  But , I think if you can able to connect or make able to design your Silverlight application to connect the database as I explained here how to do that , then it is not very difficult to do work with multiple tables.

Ok, now  come into another thing . “How can Silverlight communicate with data ?”. Because we know that Silverlight is a client side technology(I’m not going details about it in here). There are some technologies through these you can perform this task . Like, Simple Web Services  for e.g. ASP.NET WebServices (.asmx extension), SOAP, JSON (it is java script object notation basically provides serialization services ) and WCF Secvices (Windows Communication Foundation is a vast use and most technology , which is a set of web services that will provide you security and reliability more easily than other web services).

I’m using in my application WCF Service. For more details about it See MSDN about WCF Services.  Oh, one excellent portion of WCF service is that it is use RESTful interface. REST in full form is that Representational State Transfer.  Actually it do work by a defining an address for your services and passing   a query to that service. By this technique, service itself figure out that what you want returned. Thus , this method give a great advanced to you that you don’t need to write any insert, update or delete operations in server side . you just write one query and get your result.

Theory is going very much . So, Start an application .

1. Open Visual Studio and create an Silverlight application name it Whatever you wish I give it Linq to SQLApplication. Select version 4  and Click ok .

2. In our MainPage.xaml page you need to create a simple designing as I have done with some textboxes, button , AutoComplateTextBox and Datagrid.

3. Now , Create a Folder in your project and name it DataProvier. Here we shall create a class name it StateProvider.cs . This Class will give the states information to our Autocompletetextbox so that when ever user fill one simple form and in there the state name will automatically comes after writing state name of user . See pic -1.

4. First look our Solution Explorer Image in the below .

5. Now , select the Solution add right click on it -> select add new project -> From right side Template click on the Visual C# item and then from menu select Class library . Give name it Entity . See in Pic – 2.

6. Simirarly , you have to create an another project in the name of DataContextSpecial. Do also this same as we done in Step – 5.

7. Now we go in to Sql Server Management Studio Express , and there we will create a Database in the name of Ram. And there will be one Table that name is Student. See pic-4. How create a database and table in SQl Server see in Google.
Pic-4

8. I make the StudentId Column as a Primary key and also makes its Identity true. Identity increment also gives 1. See the above Picture.

9. Fill the table with some data.

10. Before going to add service . We need to delve in some theory.  As I already explained that we shall never use any extra class like ADO.NET Entity Model Class or Linq to SQL Classes, So there will not be any proxy class generate by Visual Studio itself so that they can make relation with Classes and your data in database SQL 2008 .  So, by hand we need to do it .

11. Writing Entity Class by your hand you need to Solid Understanding of the Linq to Sql attributes or external mapping schema. Another thing of Entity Class is that it keeps Change Tracking . But Using the SQL Metal or Object Relational Designer you are completely relax, because if u use them you will able to see that everything has been ready for you. But We shall do everything by our own hand. So, let’s start create an Entity Class by own hand. We already made a separate project in Our Solution in Step -5 in the name of Entity . Now delete the default Class and a new Class in the name of StudentDetails.cs , see the Pic -2 of our Solution Explorer and you will get the idea. It is not necessary that your database table name Student and the Entity Class name should be same. My table name is Student and Entity Class name is StudentDetails.

12. Before going develop the Entity Class we need to know about of the Entity Class.

What is Entity Class?

Classes that are mapped to the SQL Server Database using Linq to Sql are known as Entity Class. And an instantiated object of an entity classes is an entity of that type , this is called Entity objects. The Entity Classes are same as like normal C# Classes as we do generate in our application. But it contain additional Linq to Sql attributes . That may be one difference from Normal C# classes. If we don’t like to use attributes then there is other option to create entity classes that is using XML mapping file whenever we are going to initiated Datacontext Objects. So , using Entity Classes we can query and update our records in database .
     
13. One thing I want to tell you that if you have more than one table in your database than you have to make every separate Entity Classes for each and every table. Lets’ built our StudentDetails Entity Class.  We mark it as a Partial Class , so that it will be easier to spilt our class definition over  other source files. Only knowing up to this is not sufficient to you to make Complete Entity Classes, because Entity Classes also have the responsibility of Change Tracking.  For that Our Entity Class should also develop with PropertyChange Notification , so that it can participate in value updating. Use the namespace Using System.ComponentModel;

14. When you inherit the class from INotifyPropertyChanged and INotifyProperty then you need to add two Public Event. Ok don’t need to write any code .  Just follow the Pic- 5 . Click on the “Implement interface ‘INotifyPropertyChanged ” .  You will see that  a public propertychanged  eventhandler

Is ready for you. Same thing do for the  INotifyPropertyChanging interface .

Pic-5

15.  Add a Private Static variable which type of PropertyChangingEventArgs and also need to give String.Empty parameter in its Constructor.

private static PropertyChangingEventArgs emptychanging = new PropertyChangingEventArgs(string.Empty);

16. This emptyChangingeventargs will pass the value to one of the above mentioned eventhandler when the appropriate event will be arise.

17. Now we need to implement our previous two event handler .   Because every time our mapped entity class property is Changed. So, we need to raise the PropertyChanging Event and PropertyChanged Event before and after the property changed. Just need to write two Simple  method .

protected virtual void SendPropertyChanging()
{
if ((this.PropertyChanging != null))
{
this.PropertyChanging(this, emptychanging);
}
}
protected virtual void SendPropertyChanged(string propertyname)
{
if (this.PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyname));
}
}

18. Look the code at time of raising  Property Changed event, a new PropertyChangedEventArgs objects passes the name of the specific property that has been changed .

19. I don’t make store procedure and don’t use any functions in my database, the reason is  to keep my Entity Class Simple.

20. Now Come to Attribute that we mark on the top of our Class Name and another attribute on  the Top of the Properties , this is called Column Attribute . Ok , First see the Code .

[Column(Storage = "_StudId", IsDbGenerated = true, IsPrimaryKey = true, DbType = "int NOT NULL IDENTITY")]
public int StudId
{
get
{
return this._StudId;
}
set
{
if ((this._StudId != value))
{
this.SendPropertyChanging();
this._StudId = value;
this.SendPropertyChanged("StudId");
};
}
}

21. So, in the  Table Attribute we define the table Name= “dbo.Studendetails” and you also see in the Column Attribute we mentioned Storage , sets the private Storage field which to hold the value from Column. IsDbGenerated = “true” means that it will autogenerate the values that database contains. IsprimaryKey = true defines that this column value will represent as a primary key in datatable and DbType means you generally know to create a table in database you need to select the data type of the column value.

22. Similarly all properties should be make corresponding to the table’s column. Yes , One thing forget to tell that you need to give the namespace using  System.Data.Linq.Mapping , be sure that you have add assembly System.Data.Linq in your project reference .

23. So , you can say that our Entity Class is already . Now the question is how Your Project become to know about the connection of the database . For that Microsoft gives you a special Class that is DataContext Class. This Class contain the connection information of the Database.

What is DataContext Class?

Now this is our main point. Though we are not using .dbml /ADO.NET Entity Framework file . So, in project you have to create your own DataContext Class. If you would use .dbml file then you would be able to see that the proper DataContext Class has been created for you automatically by SQLMetal tool. Ok, Now Come in to main topic . What is DataContext Class?

DataContext Class will handle our connection to database . This class also handle database CRUD operations , change tracking , change processing , transactional integrity and also database creation . This Class is reposible to translate our query in to SQL statement that are performed on the connected database . So, it is obvious that how important this Class is . 

24. Now the Question is How can we create our own DataContext Class . I tell you to see the Pic – 3  , when we create an Entity Project . This is same just need to give the name SpecialDataContext. Don’t worry we don’t write much more code in here , because we shall inherit our from DataContext , So all members of the DataContext Class will contain in this Class. See the Code . Before that we should give the namespace Using System.Data.Linq; You look that the constructor of DataContext Class is containing the Connection Strings.
 

public class MainDataContext : DataContext
{
public Table<StudentDetails> studentdetail;
public MainDataContext()
: base(@"Data Source=.;Initial Catalog=Ram;Integrated Security=True")
{
 
}
}

25. Up to now we complete one part of our Application.  Now just need to add Services in our Application. I already told about the WCF Services at the top of the article. So , no need to say extra here . Select LinqtoSqlApplication.Web (See the Pic -2 of Solution Explorer) . Right Click on it -> Add new Item -> Form right sidw template select Silverlight -> From the menu select Silverlight Enabled WCF Services . look the below image pic -6.

Pic-6

26. Now you need to write your query in Service1.svc class. But Wait . We need to give reference of Entity Class in Server Side of our Application . See the Pic – 7.

27. click the Add reference . Select the Projects from right side of the template and choose your Both DataContext project and Entity Project assembly. See the below pic – 8.

28. Now we shall write our query first to load data from database . Before that using two namespace in the class. Using Entity and Using DataContextSpecial. Write First method . look the  code

[OperationContract]
public IEnumerable<StudentDetails> GetDataFromDatabase()
{
using (MainDataContext dc = new MainDataContext())
{
Table<StudentDetails> student = dc.studentdetail;
IEnumerable<StudentDetails> query = from c in student
select c;
return query.ToList();
}
}
 

29. In the Code we make the datacontext class object . And then we use the query .

30. Right click on the LinqtoSqlApplication.Web and Select Built . If any error is there fix that .

31. If everything is fine then right click your service1.svc. Select view in Browser . You will able to see like below Image Pic -9.

32. We need to add service reference in our main Silverlight project How? Ok see the Below pic – 10.

pic-10

33. So, when you click the Add Service Reference. Then You need to give the above copy address and click Discover. See Image Pic -11.

Pic-11

34. Click Ok , you will see that your ServiceReference1 has been successfully add in your project. Built Complete Solution of press F6.

35. After Completing Built We shall go in our MainXamlPage and in datagrid we bind all the data. So, how to do that please download the Source Code from the download link. And see it is very easy .But we need make servicereference class object in our Codebehind. I’m not going in details assume that you know WCF Services .

36.  I’m going to show you the Output in the Pic – 12.
Pic-12

37. So you have successfully loaded your data from database .  Download application and run the code in your machine.

38. Next task is to insert the data in database and show the records in the data grid. To do that again you need to go back Service1.svc class. Write another method in here . See the code below

[OperationContract]
public IList<StudentDetails> InsertData(string name, int studentclass, int studentage, string studentsex, string Address, string city, string state)
{
StudentDetails st = new StudentDetails
{
StudName = name,
StudAge = studentage,
Class = studentclass,
Sex = studentsex,
Address = Address,
City = city,
State = state
};
using (MainDataContext dc = new MainDataContext())
{
dc.studentdetail.InsertOnSubmit(st);
dc.SubmitChanges();
Table<StudentDetails> stud = dc.studentdetail;
var query = from c in stud
select c;
return query.ToList();
}
}

39. Again built your LinqtoSqlApplication.Web . After successfully do this go to the Project Service reference and update this.

40. You will see the below image .

Pic-13

41. Need to write some code in your codebehind page . Under button click event you need to write the below code. 

private void Submit_Click(object sender, RoutedEventArgs e)
{
stud.StudName = this.Studentnametextbox.Text;
stud.StudAge = Convert.ToInt32(this.AgetextBox.Text);
stud.Class = Convert.ToInt32(this.ClasstextBox.Text);
stud.Sex = this.SextextBox.Text;
stud.Address = this.AddressTextBox.Text;
stud.City = this.CityTextBox.Text;
stud.State = this.autoCompleteBox1.Text;
busy.Show();
client.InsertDataCompleted += new EventHandler<InsertDataCompletedEventArgs>(client_InsertDataCompleted);
client.InsertDataAsync(stud.StudName, stud.Class, stud.StudAge, stud.Sex, stud.Address, stud.City, stud.State);
}

42. I make one CustomBusyIndicator in this application.  When you fill the form with name , address, sex, age, state name …..etc and after that you click the Submitt button you will see the busyindicator is rotating  and when the data is inserted in the data grid successfully it will  vanish . You can get the complete code when you run the application in your machine. See the Image below , Pic – 14 after putting the details in textboxes .

Pic-14

43. See the name You enter , in the datagrid .

Hope, this can give you the idea how to create your own datacontext and Entity Classes and insert , and loading data from database without using LinqtoSql Classes I mean .dbml file and also without using ADO.NET Entity framework .

Give me your response to improve my article . Thank you .