locked
Simple N-Tier Code Sample RRS feed

  • Question

  • Does anybody have a simple N-Tier example for SQL Server / SQL Server Express?

    The demo "WebSharingAppDemo-SqlProviderEndToEnd" is incredibly convoluted.  To whomever put that together, two thumbs down!!!  I've got what I think is a standard and typical setup -- a server running SQL Server and a bunch of clients running SQL Server Express.  How about a simple console-based app with ALL the server-side code, and a simple client with all the client-side code?

    This convoluted example is extremely hard to follow.  I can't even get the darn thing to run because it seems to be intended to demonstrate a client and server on the same machine/database??  And the client connects to the server DB directly before it tries to synchronize through the WCF service?!?  What is the point of that?  If a proxy/service is being used shouldn't ALL communication with the server go through the WCF service?  Otherwise what's the point of the service to begin with?  I find this example extremely frustrating. 

    If anybody has a simple example it would be greatly appreciated.

     

     

     

    Monday, September 20, 2010 9:26 AM

Answers

  • I was never able to find a simple example, so I created my own ;)

    I took the demo "WebSharingAppDemo-SqlProviderEndToEnd" and simplified it, and then immediately complicated things even more to suit my own needs.  I'm thinking about perhaps taking the results and creating a article on CodeProject or some other site.

    I ended up with three projects in the solution, Client, Server, and ServerInterface.  I'll try to give a brief explanation for each.

    1. ServerInterface - This is a DLL that holds all the interface files, along with other common code such as exceptions that WCF might throw and classes that I use for chunking batches into small pieces for streaming.  The interface is the link that tells the client proxy how to communicate with the server.

    2. Server - This is just a console app that exposes the WCF service, which derives from the interface(s) in ServerInterface DLL.  The server maintains all the connection info to the SQL server, which is hidden from the client.

    3. Client - Just a simple console client.  I added code to create MDF files using SQL Server SMO, as well as provision the blank database for synchronization.  The sync function is then simple.  The local provider is just a regular SqlSyncProvider that connects to the local MDF.  The server provider is the SqlSyncProviderProxy which creates a WCF channel to the server using interfaces defined in ServerInterface.  The client knows nothing about the database server it is syncing with.

    ** For additional goodies I also added a couple things. 

    1) I added custom user validation to the WCF service.  The proxy server on the client imports a SSL certificate that is saved into ServerInterface as an embedded resources.  The WCF service uses that same SSL certificate server-side.  The client then passes a userid/password that the server WCF service validates through a custom validation class. 

    2) I also added code to break batches up into smaller chunks, which I then stream one at a time.  This allows me to monitor the entire sync process byte-by-byte and provide progress bars showing how far along the sync progress is.  It's awesome.

    If anybody is interested in this example being published let me know and I'll see if I can find the time to put an article on CodeProject or some other site.

     

    • Marked as answer by AJ8829 Sunday, October 3, 2010 5:08 PM
    Sunday, October 3, 2010 5:08 PM

All replies

  • Hi AJ8829

    I am lookfing for a simple example as well, i download that sample and couldnot figure out what's happening.

    Let me know if you get any easy to follow sample

    Thanks

    Tuesday, September 28, 2010 1:02 PM
  • try this

    http://technet.microsoft.com/en-us/sqlserver/dd407782.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+Microsoft/TechnetHow-toVideos+(Microsoft+TechNet+-+How+to+Videos)&utm_content=Google+Feedfetcher

     

    or 

    i use this method

    http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/sync-services-for-ado-net-with-sql-server-2008-change-tracking.aspx

    code is very simple

    that way do is little different i use .Net Remoting to get to Clients running SQLExpress

    Server app connects to main SQL Server and retrieves the latest inserts/updates and delete changes which it receives as a dataset and sends that data to all client which updates its local SQLExpress.

    hope this helps.

     

     

     

    Tuesday, September 28, 2010 2:59 PM
  • I was never able to find a simple example, so I created my own ;)

    I took the demo "WebSharingAppDemo-SqlProviderEndToEnd" and simplified it, and then immediately complicated things even more to suit my own needs.  I'm thinking about perhaps taking the results and creating a article on CodeProject or some other site.

    I ended up with three projects in the solution, Client, Server, and ServerInterface.  I'll try to give a brief explanation for each.

    1. ServerInterface - This is a DLL that holds all the interface files, along with other common code such as exceptions that WCF might throw and classes that I use for chunking batches into small pieces for streaming.  The interface is the link that tells the client proxy how to communicate with the server.

    2. Server - This is just a console app that exposes the WCF service, which derives from the interface(s) in ServerInterface DLL.  The server maintains all the connection info to the SQL server, which is hidden from the client.

    3. Client - Just a simple console client.  I added code to create MDF files using SQL Server SMO, as well as provision the blank database for synchronization.  The sync function is then simple.  The local provider is just a regular SqlSyncProvider that connects to the local MDF.  The server provider is the SqlSyncProviderProxy which creates a WCF channel to the server using interfaces defined in ServerInterface.  The client knows nothing about the database server it is syncing with.

    ** For additional goodies I also added a couple things. 

    1) I added custom user validation to the WCF service.  The proxy server on the client imports a SSL certificate that is saved into ServerInterface as an embedded resources.  The WCF service uses that same SSL certificate server-side.  The client then passes a userid/password that the server WCF service validates through a custom validation class. 

    2) I also added code to break batches up into smaller chunks, which I then stream one at a time.  This allows me to monitor the entire sync process byte-by-byte and provide progress bars showing how far along the sync progress is.  It's awesome.

    If anybody is interested in this example being published let me know and I'll see if I can find the time to put an article on CodeProject or some other site.

     

    • Marked as answer by AJ8829 Sunday, October 3, 2010 5:08 PM
    Sunday, October 3, 2010 5:08 PM
  • Yes, I am interested in seeing your example.  I have the following situation:

    - Hub & spoke

    - Spokes are running SQL Server 2008 R2 Express with change tracking on

    - Hub is running SQL Server 2008 R2 Enterprise with change tracking on

    - Spokes can make changes to their local copies of the database

    - WCF will be used

    - I would like to use the latest features of Sync Framework 2.1, to wit, use the SyncOrchestrator class, not the SyncAgent class.

    If you can point me at any further places that might help me, please let me know.  Thanks.

    Wednesday, October 6, 2010 2:53 PM
  • I am very interested in seeing you sample.  Could put it up someplace where we can download it?  Perhaps on a Sky Drive?

    Thanks

    Saturday, October 9, 2010 9:09 PM
  • One note on the client connecting directly to the server DB.  You are correct that this should never happen in the real world and this is clearly commented in the code.  The only operations that are happening directly on the server DB via the client are provisioning operations.  Normally you would put those operations in some kind of administrator application (where you could connect directly, or go through a WCF service).  The sample chose to wrap up all those operations and just comment this fact.  Perhaps this wasn't the best way to go.  I agree that various part of the sample are confusing (especially the client code), your feedback is welcome.

     

    I would also be interested in checking out your simplified sample, the additional authentication features are a good idea.

     

    -Jesse

    Monday, October 11, 2010 8:04 PM
  • Can I get the Sample you worked on. The sample provided  WebSharingAppDemo is confusing alot.
    Thursday, October 14, 2010 6:25 PM
  • I would be interesting in seeing AJ8829 example as well. Hopefully he will come back and take a look at this. 
    Thursday, October 21, 2010 6:31 PM