SQL Server 2008 New Data types

Yesterday I was trying to create my first database on SQL 2008 and thought let me check out if there are any new data types that I can take advantage of and yes in fact there are. The best I liked is Date and Time data type, how many times we needed a field just to store either the date part or just the time part and we ended up using DateTime data type which stores both date and time when you don't need both. Besides saving space in storage it saves the hurdle of reformatting or massaging the code while displaying, comparing or manipulating. I use Oracle more often then SQL sever (not by my choice) and each time we have to compare the date part we have to remember to do the TRUNC to remove the time part, same applies while displaying the fields on screen. Some time we just want to store the time part like Shift start and end time, we still end up storing both date and time and then remove date before displaying. It's just annoying. Also on storage side the date field just takes up 3 bytes instead of 8, one may say that in current time when we are dealing with Tera bytes of data how much does 5 bytes matter; I would say there is no alternative to responsive design.

When I designed my tables first time I added the fields with SQL 2005 in mind, good that I checked the new types and even though I had to redesign some of my tables I think it's worth the efforts :)

Check out TechNet for nice article explaining the types in detail.

That's the fun being in IT field you can never say you know the it, every time you cross check you will learn something new. Happy learning.

Virtual Earth development with silverlight

Yesterday Telmo Sampaio of Blue Dasher Technologies presented Virtual Earth development with silverlight at the West Palm Beach .net user group and it was more than amazing. During his presentation he demonstrated how one can take the mapping applications to next level with the help of silverlight, now the maps on your application need not be just plain maps with zoom in and zoom out, you can plot any custom silverlight objects on the map and play with them. Enough said take a look at this cool application they have developed at Blue Dasher

http://www.bluedashertech.com/

You can learn more about SDK download and updates from Bing Maps team blog.

SSWUG virtual conference discount code

Recently I received a discount code for the virtual conference by SSWUG group, the organizers call it VIP code :) I am going to present 3 sessions under the .net  conference sessions and really liked the whole concept; for less than 5% cost of what one would pay at VS live or Teched one can get same quality of session at the comfort of home or office and you can pause the session or replay them when you think it’s getting way over your head to understand the accent of guys like me.

Check out the sessions at SSWUG vConference

And if you plan to register you can save $10 on registration by using SPVVSHSP09

code. They have over 140 session in 4 different tracks.

SF Code camp 2009, Links to code and presentation for ADO .net Sync Services

It was another great code camp event at Miami with so many great speakers and .net enthusiasts. The only difficult thing is to choose between tracks that you want to go.

In today's session of Sync services I saw more interest than in past which is always good and fun. For a presenter there is nothing is more satisfying than having a presentation more like a group discussion.

Below are the link for presentation and code example

ADO .Net Sync Services Presentation

2 Tier Example

N Tier Example using WCF Service

:)

Why smart client still not picking up main stream?

ADO.NET Sync Services in N Tier Architecture

Finally after 2 weeks I did get chance to publish the second part of my article on Sync Services.

The article is a follow up article to Introduction to ADO .Net Sync and takes a look at how easily we can convert a two Tier Sync application to N tier using WCF services. The ability to sync up your data without connecting to the main database is a great value and opens the door to many complex architecture implementations. There are many examples on sync services online but most of them are in C#, hence here I am just trying to have very basic examples for the VB community.

If you need slides for your own presentation you can get it from my previous blog. In the coming weeks I'll try to post more examples on sync services, and my next target is to convert the 101 sample library from MSDN to VB .net if someone has not already done that.

SQL pass meeting in Miramar

Next Thursday I'll be presenting ADO .net Sync Services at the SQL pass meeting in Miramar, this will be my 5th presentation on sync services in 4 months. It's an amazing technology and I just love how simple still powefull it is. If you are in South Florida region and are planning to register go to

South Flordia SQL User Group

I have already uploaded my presentation and code sample on previous blog. Plus you can also check out article on code project. Hopefully in couple of dats I'll try to add another article on Sync services in N tier architecture.

Introduction to ADO .Net Sync Services

Introduction

This blog entry which is more like an article, will demonstrate how easily you can design occasionally connected applications using ADO .Net Sync services. It’s going to be a very basic example in VB .net showing how easily you can use Sync services within your application. I have taken the template from code project and will try to upload it there as well. I am also uploading my presentation in case anyone needs it.

Presentation and Source code

Background

At our organization we have designed an application for the Fire Rescue chiefs who are usually on unit and want the ability to access the application using their laptops and even PDAs. They use Verizon cards for broadband access and it works well for most part, but once in a while they lose the connection and the application dies. The ability to support mobile and remote workers is becoming more and more important for organizations. The Microsoft Sync framework has been designed to address these issues and gives a great framework to easily design your applications on occasionally connected architecture. For this article I will use the VB .net as sample as there are very few VB .net examples on the net.

Using the Code

I have uploaded the complete source code for the project, at the same time I will go through setting up on your front step by step. Hopefully in a process I’ll be able to explain the process in detail.

First you’ll have to download the Sync framework from Microsoft site. The V1 is available for download at

http://msdn.microsoft.com/en-us/sync/default.aspx

Once the framework has been installed, start visual studio 2008 and create new Windows Form application.

Now let’s add new item to the project “Local Data Cache”

What this is going to do is create local SQL CE database that will be used by the client application, and we will eventually write the code to sync up this database with our master database for selected tables. The best part of the Sync framework is it’s support for studio 2008. It provides nice wizard for the whole set up making our life lot easier.

As soon as you click “Add” button the next screen pops up, which will be used to set up your local cached database. On the screen select “Server Connection”. Now if you have previously used the windows form application you may already have SQL connection setup like in my case, otherwise click the new button and create the connection string for your master SQL server. For this example I will use our favorite “Adventure Works” database. This is how the second screen looks ;

Now that you have given your server connection information, the wizard creates client connection. This is nothing but the new SQL CE database that you just created. Also now that the wizard is aware of the server database it will allow you to add the tables to be cache. Click the “Add” button on the left bottom of the screen. Once you hit the “Add” button following screen appears.

Select the tables that you would like to cache on the client side. Now remember you do not want to select all the tables as it’s not practical to cache the whole master database on the client machine, as usually the client machines in such cases laptops, tablets, PDA may not have enough storage. In the above scenario I have selected 3 of the tables. On the right side of screen you will get some more setting options. In most cases you would like to keep them to default settings.

The first option asks you what data you want to download; New and incremental changes only after first synchronization or Entire table each time. Also the sync framework is going to add 2 new columns to your tables to keep track of last update and new inserts. You can choose existing columns if you are already tracking it. Also it will create a history table named as TABLENAME_Tombstone. This is used to keep track of deleted rows. With SQL server 2008 you would not need either of them as SQL server has standard change tracking. So with SQL server 2008 it will not add any additional columns or tombstone table.

Select “OK”, and “OK” on the first wizard screen this will create the local cached database with the tables you selected. On the first wizard screen you also have options to select the server and client project location. In this scenario both will be our current project. In my next article I will try to cover a 3 tier example using WCF services. And in N tier application you can select your server and client application to be different. You can optionally select to create synchronization components for client only or server only. By default it’s client and server.

After hitting ok on this screen it the wizard will create local database (.sdf) in our case it created AdventureWorks.sdf. It will then prompt you with following screen allowing you to select tables to be added to your dataset. This will allow easy creation of grid on the form with typed dataset.

As you can notice I have selected all the 3 tables. After clicking Finish it will create dataset for the project. You can open the dataset and add some more tables from the server explorer, but those tables will not be cached on client machine. They will be available to use in the application but the application will go back to server each time those tables are accessed. For this demo we’ll keep it simple and not add any more tables, as the main purpose of demo is to see the sync framework in action. Now go back to your form and open your data sources (show data sources under data menu). Select Employee table and select Data grid view, and drag the table on form. This should add the gridview to the form with all the navigation controls. I really like this part of design, just drag and drop and you are ready to go.

Now comes the main part, in order to activate the sync process we will add the button to grid’s tab strip. Usually you will have a service running in the background that will check for network availability and if its available it will initiate the sync process. Again for this demo will try to keep it simple and will call the sync process on click of a button.

Double click the button and add the code to sync the databases. Now it’s just 3 lines of code and out of those 2 lines have been provided right in the wizard. Click the LocalDataCache1.sync and you will notice that on the right bottom corner is “Show Code Example”, click that copy the code and cut paste it in the event handler for button click.

Now just add code to merge the changes to client table using;

And that’s it, your first occasionally connected win form application is ready to run. Browse through the data on client side, now make some changes to the data on server. The data on client is still old as we have not initiated sync process, click the sync button and your client gets updated with new data. Now try changing data on client and see if it is reflected on server, it wont as by default the sync works unidirectional. But again they have made it real easy to change it to bidirectional. All you have to do is right click the LocalDataCache1.sync and say view code. You will see SyncAgent class, just add following code;

Partial Public Class LocalDataCache1SyncAgent
    Private Sub OnInitialized()
         Me.Employee.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional
    End Sub
End Class

And now your application should sync up in both directions. Try changing some data on client side, click the “save” button on tool bar and then click the sync button. Data on server should reflect the changes done on client side, but wait have we got us in trouble by allowing bidirectional sync? What happens if both client and server update the same record, how will it work? Again the framework at your rescue, all such conflicts raise ApplyChangesFailed event for server sync provider which you can implement by implementing partial class for your server sync provider. Below is the sample on how to do it.

By default the changes from server are overwritten on client, you can change that logic to say that in case of ApplyChageFailed force changes from client to be over written to server.

Partial Class LocalDataCache1ServerSyncProvider
    Private Sub LocalDataCache1ServerSyncProvider_ApplyChangeFailed _
                (ByVal sender As Object, _
                 ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) _
                 Handles Me.ApplyChangeFailed

         e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite

    End Sub
End Class

In this case the changes from client are always written to the server. Again this may not be a practical solution as in most case you may want to do some kind of validations before accepting either client or server changes. And the best part is that even that’s easy to implement. All you do is take the client changes and server changes and apply your business rule.

Partial Class LocalDataCache1ServerSyncProvider
    Private Sub LocalDataCache1ServerSyncProvider_ApplyChangeFailed _
                (ByVal sender As Object, _
                 ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) _
                 Handles Me.ApplyChangeFailed

       Dim clientChanges As DataTable = e.Conflict.ClientChange
       Dim serverChanges As DataTable = e.Conflict.ServerChange

       If (clientChanges.Rows(0)("ModifiedDate") > serverChanges.Rows(0)("ModifiedDate") Then
           e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite

       End If
    End Sub
End Class

Here we are checking the date modified on server and date modified on client, and if the date modified on client is later we are forcefully updating server with client changes otherwise do nothing. You can apply any business logic here and in fact you can use ApplyingChanges event and modified the data before it is updated, this could be useful in cases where you want to update the values based on some calculation e. g. in case of inventory you may want to update both server and client with addition of values from server and client updates.

Points of Interest

This is a real simple example and in real world you may have lot more complex scenario’s to implement, but the purpose of the article is to show how easy it is to sync framework to design occasionally connected smart client applications.

Southwest Florida Code camp

Yesterday I presented ADO .Net Sync Services at the first ever Southwest Florida Code Camp. It turned out to be a good event considering that it was the first year. There were some really good speakers discussing some of the best in technolgoy. Due to my daughter's soccer game I could not attend the morning sessions, but did attend ASP .net dynamic data by Amit Trivedi. It was a great topic.

As promissed I will try to upload the presentation and project from my presentation to the site. I am in the process of composing a detail article on what we saw at the presentation.

Auto Save/load Template Server Control

Recently my team had a requirement where they wanted to store the search criteria on various screen under user's profile. So that once the user selects some search criteria on a particular screen, next time when he comes back to the same screen it should re load those search criteria automatically. Now this could have been accomplished by changing the screens to store the search criteria during post back in the database and load them when the screen is loaded for the first time from the database for the user. But we had around 20-30 such screens, which means lot of code changes.

This is when we got the idea of designing the user control, and the outcome was great. Thanks to the user control all we had to do in those screens is just add the reference to the control and put all the asp .net/html controls within our user control. Total 2 lines of code change :)

The same control can be modified to achieve lot of other requirements like doing common validations, applying some common code to all the controls etc.

Read the article in detail at CodeProject, I have also uploaded complete source code for the control as well demo project.

http://www.codeproject.com/KB/webforms/vishalshukla.aspx