ADO.NET DataSets in a nutshell

Since few years, developer are more and more far from ADO.Net with the popularity of ORM (object relationnal mapper). Nevertheless, it’s important to take the time to remember what is used behind those ORM or simply to remember how that we can still use ADO.Net classes to achieve the desired behavior with a database. This article describe a disconnect object that contains informations that came from a connected persistence container or/and that will be pushed to a persistence container. This is the role of the DataSet.

A DataSet is a snapshot of tables of your database but once it’s filled, this one is not anymore connected to the database. A modification to the DataSet won’t update the database’s tables. It doesn’t mean that we could not send the information back to the desired tables but it will requires a new connection to the database. The DataSet is a layer of abstraction between your application data and the persistence storage. By using a DataSet, you do not know exaclty if you are saving into one database or several databases, or if you are saving into XML file.

The first property is the DataTableCollection. This property contains the set of DataTable which can be see as a SQL table columns. The definition of the DataTable ( metadata of the table) is defined by a collection of type DataColumnCollection. For data, these are located into DataRowCollection. This one is also a collection. So far, you can see a lot of similarity between DataSet properties and how SQL works. Both have tables, columns and rows.

In SQL, you can use what is called a view to manipulate the output of a table or many tables. With a DataSet, the DataView act the same way by selecting what is shown. This is perfect to map data between your application and data from your persistence storage.

The DataSet contains some properties and one of this one is the DataRelationCollection.

Usually, a DataSet is fill up with a DataAdapter which use a DbConnection with DbCommand. This fill up the DataSet and all connection can then be closed.

var con = new SqlConnection(/*Connexion String*/);
var dataset = new DataSet();
var adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(/*Your SELECT statement*/,con)
con.Open();
adapter.Fill(dataset); //The dataset is fill up with data, it's now possible to close the connection and use the data
con.Close();

The main advantage is that you are closing rapidly the connection to the database by releasing to the connection pool. You should always open the connection, do your query and close it. This will give the possibility to other request to connect to the database. It’s a good practice because every database has a limited amount of connection that can be accepted in the same time.

Once the connection is closed and the dataset filled up, you can manipulate the data inside DataTableCollection. This can be done by two different way. The first way is to use the DataSet as a Typed DataSet.

var dataset = new DataSet();
var prop = dataset.YourEntitiesSet[0].YourProperty;

The second way by using the UnTyped DataSet.

var dataset = new DataSet();
var prop = (string)dataset.Tables["YourEntitiesSet"].Row[0].["YourProperty"];

OData Query syntax

If you have setup WCF Data Service, you can now query your service with the OData syntax. If you are using Entity Framework, you will see a lot of similarity between Entity Framework and this string syntax. OData works with http request which is based on URI. Every queries statements need to be passed by this url. Here is a list of all possibles statement that can be used together as Entity Framework or a SQL statement.

OData ordering keyword : orderby

If you request one of your entity set, you may want to order the result. To be able to order, you will have to user orderby with = and the property of the entity. If you need multiply properties in your ordering, simply seperate them with a comma (,). Here is two examples.

http://yourwebsite/YourService.svc/YourEntitiesSet?$orderby=OneOfYourProperty

http://yourwebsite/YourService.svc/YourEntitiesSet?$orderby=OneOfYourProperty,ASecondProperty

OData getting a limited amount of data keyword : top

This is the same keyword as Sql Server or Entity Framework. This let the consumer to ask to the WCF Data Service how many result maximum the consumer receive. It can be the specific number or less, or none. Here is an example.

http://yourwebsite/YourService.svc/YourEntitiesSet?$top=10

OData paging result keyword : skip

You can do paging with OData. For example, if you want to have a list that display 10 elements per page, you could request the WCF Data Service to get the first 10 entities and then ask the next 10 entities when the user request the next page. This can be done with the previous keyword top to get the 10 entities but to get which group of 10, we need to new keyword skip. If you want to have the third page of 10 elements, we will need to use skip with the value 20 and the top at 10. This can be translated by : “I want the third page of 10 elements”. The value of skip is 20 because we have 10 elements by page. One page mean we display 10, two pages mean we display 20. The third page requires to skip those 20 pages and to display the next 10. This is specify with the top keyword.

http://yourwebsite/YourService.svc/YourEntitiesSet?$skip=20&$top=10

Condition with OData by the filter keyword

In SQL, we would use the keyword where and the keyword and if we have multiple conditions. It’s the samething with Entity Framework. But, with OData, the keyword is not where. The keyword is filter.

http://yourwebsite/YourService.svc/YourEntitiesSet?$filter=YourProperty equal('Test')

OData contains several keyword to compare : http://go.microsoft.com/fwlink/?LinkId=186972

Joining entity with the keyword expand

Entity Framework gives the keyword include which add a reference to an other entity or a list of entities. You need to specify the name of the property and this one will be loaded in the foreign key that define the relationship in the database. With OData, the keyword is expand.

http://yourwebsite/YourService.svc/YourEntitiesSet?$expand=YourPropertyThatLinkToAnOtherEntity

Selecting specify properties of an entity with the OData keyword select

This is exactly the same as SQL and Entity. As you can see since the beginning of this article, a lot of keyword in OData are the same with Entity and SQL. This is a strong positive point that give a low learning curl. The select keyword give you the posibility to not returning the whole entity information but only specific property of this one.

http://yourwebsite/YourService.svc/YourEntitiesSet?$select=Property1,Property2

Could not load file or assembly ‘WebMatrix.WebData’

If you are creating an Asp.Net MVC website or an Asp.Net MVC Api Web Service, you may see yourself blocking on the execution with a missing file with WebMatrix.WebData.

Could not load file or assembly ‘WebMatrix.WebData’ or one of its dependencies. The system cannot find the file specified.

This error can be fixed by being sure that you have the WebMatrix.WebData inside your references. This assembly should be located in Asp.Net Web Page folder of Microsoft Asp.Net (c:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v2.0\Assemblies\WebMatrix.Data.dll). The best way to get the reference is simply to click Add Reference and doing a search for WebMatrix.

webmatrixreference

Once you are sure your have the WebMatrix DLL, be sure that this one have the property “Copy Local” to true.

copylocal

This should solve the problem of having “Could not load file or assembly WebMatrix.WebData”.

WCF Data Services is the implementation of OData by Microsoft

Several time a week I hear confusion concerning WCF services, WCF Data Services, ADO Data Service and OData. First of all, ADO Data Service is out. In fact, it’s the old name for WCF Data Service which is the good sentence for OData by Microsoft. WCF service focus on contract when Data Service focus more on the data. OData is been invented by Microsoft but is not the implementation in .Net. It’s the protocol which is open.

WCF Data Service is a type of service that provide information through url. This is easily consumable because it requires only a web browser to execute a call which can have filter, join between entities and conditions. WCF Data Service uses the entity-relationship conventions of the Entity Data Model to expose resources as sets of entities that are related by associations. Not only can you get information the way you want but you can also update entity and insert.

WCF Data Service lets you use two well know structured semantic which is Atom (XML) and JSON. Both of these protocole are RestFul. That mean that when a call is executed, the life cycle end once the data is sent back to the consumer.

To create a WCF Data Service, simply create a class that inherit from DataService. It’s a generic class so you will have to specify a DataContext type. To use this class, you need to reference the dll : System.Data.Services.

public partial class MyService: DataService<MyDbContext>
{
   //...
}

You will have to also specify which entities is allowed to be accessed in InitializeService.

public static void InitializeService(DataServiceConfiguration config)
{
   //...
}

The simplicity lets you add this service in a later stage of your project. It’s even more true if your project is already using Entity Framework because OData is tightly coupled Entity Framework way to handle entity. This is why it’s very easy to create a WCF Data Service with Entity Framework because of the Data Model that both share.

OData is between the consumer and the source, the consumer could be a simple web browser and the source a .Net application or SQL Azure server. This is very powerful because you can make a mobile application that use the same service as a web site or a winform. Even more, you can create on different web browser (Internet Explorer, FireFox, Chrome) or different mobile platform (Android, IPhone, Windows Phone) with the same WCF Data Service.

The OData protocol lets you send more than only GET request. GET http request will only give you the possibility to receive data. In the case you want to delete data, a DELETE http request would be required. POST is used to insert, PUT to update. The protocol uses string and not a reference, this is why XML and JSON is used. It also respect the concept of not being tight to the server by being RestFul. So in short, a SQL table (rows) is converted into Entity Set (entity) which is converted to a collection of entry (entry).

WCF Data Services also supports data models that are based on common language runtime (CLR) objects that return an instance of the IQueryable interface.

Flipping a div (part 2)

In a previous post, we have discussed about how to flip a division. However, Javascript was involved with CSS3 while only CSS3 could have been enough. For the curious, here is the code/JsFiddle (most of the code is not from me, but I cannot find the source).

First of all, the Html structure changes a little with two divisions with the same class (“side”) within 2 divisions. One is the card container and the second is card. The card will be the one animated while the card container act as a placeholder.

<div class="card-container">
  <div class="card">
      <div class="side"><img src="thisisanimage.png"/></div>
      <div class="side back">This is some text</div>
  </div>
</div>

As for the CSS, the perspective is also set to have a 3d look of depth. This is what the card-container is for.

After, the card is assigned to have a transition of 1 second. Nothing is trigged until the card has a transformation applied, which is the case with the next CSS statement.

.card-container {
    -webkit-perspective: 600;
    margin-bottom: 20px;
    position: relative;
    width: 150px;
}

.card {
    position: absolute;
    -webkit-transform-style: preserve-3d;
    -webkit-transition: all 1s ease-in-out;
    width: 100%;
}
.card:hover {
    -webkit-transform: rotateY(180deg);
}
.card .side {
    -webkit-backface-visibility: hidden;
    position: absolute;
    width: 100%;
}
.card .back {
    color: #404853;
    font-size: 14px;
    font-weight: bold;
    line-height: 150px;
    text-align: center;
    -webkit-transform: rotateY(180deg);
    background-color:yellow;
}

The “:hover” statement raise the transition to be executed (with the 1 second time). As you may have notice, the use of “backface-visibility” let the rendering to not display the back of the div but instead display the other division which rotate at the same time. This create the illusion that we are seeing the back of the division, but in fact, we are showing the other division. This is possible because of the position set to absolute. This put both division over each others.

How to flip a div with JQuery and CSS3 animation

If you are curious about what we will discuss in this blog post, you can visit JsFiddle for the code and an example of how to flip a div with CSS3 and some javascript.

First of all, here is the Html markup which define two divisions which are each side. Both side contain a text. When using the button, it goes from one div to the other with an animation that flip the division. The goal is to simulate that the user click some where and we go behind a div.

<section id="zone">
    <div class="recto">
        Patrick
    </div>
    <div class="verso" style="display:none">
        Desjardins
    </div>
    <button id="btnFlip">Flip</button>
</section>

The second step is to define a style to all those Html markup. It also defines the animation that we use to make it looks like if we were flipping the div to go behind it.

flipdiv

#zone div
{
    width:150px;
    height:150px;
    background-color:yellow;
    color:black;
    font-size:18px;
}
#zone
{
    -webkit-perspective: 1200px;
}
@-webkit-keyframes spinner {
    from { -webkit-transform: rotateY(0deg);    }
    to   { -webkit-transform: rotateY(-180deg); }
}

.flip
{
    -webkit-animation-name: spinner;
    -webkit-animation-timing-function: linear;
    -webkit-animation-iteration-count: 1;
    -webkit-animation-duration: 1s;
    -webkit-transform-style: preserve-3d;
    -webkit-transform-origin:50% 50%;
}

The first selector (#zone div) define the final state of both division. This is required because we want to be able to flip something similar. Also, we will be able to flip back to the original state. Nonetheless, we define a perspective for the zone which will create during the animation a sense of depth. Finally, we specify an animation key frames which do a 3d transformation by using the CSS3 features called rotateY. This will create a flip at the Y axis. Also, the class “flip” is defined which specify where the rotation occur (in the middle of the div) and also the duration which is 1 second in this example.

The last step is to define the action on the button. This action could have been placed directly onto the div but to not mix concept here, we will attach a click event to a button.


$(document).on("click", "#btnFlip", function flipDiv()
{
    $('#zone div:hidden').removeClass('flip')
    $('#zone div:visible').addClass('flip')
    .delay(600)
    .fadeOut(300, function(){$('#zone div:not(.flip)').fadeIn(300)});
});

What are we doing here is that we remove from the hidden div the “flip” class and add it to the other division. This let us having a continual movement of the class “flip” between the front and the back.

WCF The maximum message size quota for incoming messages (65536) has been exceeded.

WCF The maximum message size quota for incoming messages (65536) has been exceeded.

This is because the MaxReceivedMessageSize is set by default to 65536 and that you are returning more than that. Most WCF Client let you edit this property :

wshttpBinding

Even the WCFTestClient let you edit the size. You just need to right client on Config File, then you select Edit With SvcConfigEditor.

editConfigFile

From here, you need to open the Bindings folder (of SvcConfiEditor) and to change to MaxReceivedMessageSize to something more related to the kind of size you are expected to receive.

The difference between SQL Delete statement and SQL Truncate statement

Some time, task that we do often seem easy but when we stop to go deeper we realize that we do action by reflex and that we have forgotten the meaning of the detail underneat the action.

One action that we have to do quite often is to remove data from tables. This can be done with the keyword delete or truncate. Here is two examples.

--Delete
DELETE FROM Table123

--Truncate
TRUNCATE TABLE Table123

But behind those two statements, what really happenning? First of all, the delete statement is row oriented. This mean that it deletes one row after one. This mean that it has to lock the row, delete it, log it, etc… then unlock it. This whole process consume resources and times.

On the other side, truncate statement is won’t log anything, it won’t go row by row. All these advantages come with the disadvantage of not beeing able to delete specific rows. Neither be able to restore what you have deleted.

--This will work
DELETE FROM Table123 WHERE startDate <= 2013-06-14

--This won't work
TRUNCATE TABLE Table123 WHERE startDate <= 2013-06-14 -- Error!

It also doesn’t trig any trigger. OnDelete or OnUpdate won’t be trigged which could cause undesired behavior.

Howerver, an other advantage that truncate does have it’s that it will reset the seed for identity.

To conclude, using delete it the way to go if you are inside an application and you need to remove an entity of your model. The business defined behind trigger will be executed while you will be able to specify which entry to delete. On the other side, truncate is a must to use if you need to reset a table or move several rows to a temporary table, truncate and reinsert rows.

Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details.

With EF4.1, EF4.3 and EF5.0 you may have an Entity Validation Error and you may not found how to get the detail of the error. Entity Framework encapsulate the error into a class named DbEntityValidationException which is not casted in the debugger.

Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details.

To be able to get the information, you can add to your Watch Panel (inside Visual Studio) or into the Immediate Panel or Quick Watch the following line when the error occur:

((System.Data.Entity.Validation.DbEntityValidationException)$exception).EntityValidationErrors

From here, you will be able to get which entity is in error and what is the error. Data Annotation error or validation error will be clearly written inside the DbEntityValidationException.