   [.net] WCF and Database access
  Hi all, I'm currently redesigning a .net Remoting application in order to use WCF. Thanks to proxies, Remoting made me able to access databases through Datareader, and this was the best option, as this was fast and I had not to wait until all records were loaded to get back control to my program.With WCF things are not as easy: I can't access data through proxies, and I can't serialize a DataReader. We have more than 100 tables, each one with its object representation, and each one has its own methods to handle it. I sure can't create a service with 500 methods! So I though to create BO on the client, and query the db through a few simple methods (like ExecuteQuery) made available by the service, but how can I transfer data? They say that DataTables would slow it down quite a lot.Wich are the alternatives?Thank you!   
  The basic problem here is that you're not abstracting your service-contract level objects and methods from the data-storage objects and methods. You don't want to query a database directly through a remoting service, nor do you want to expose all your database access routines through your service interface.Typically, you'd create a contract, consisting of plain objects that your service operates on and an interface describing the kind of actions you can do with those objects.  These should be separate from the objects and methods you use to store this data in the database.  You don't make a 1-to-1 mapping of service-level functions to database-functions;  your service level code is what does the validation and mapping of your service level functions into multiple database calls.  That is, one contract-level method results in potentially multiple different database calls.For example, if you wanted to save and load player data, you might have something like:class Item {   private string type;   ...}  class Player {  private string name;  private int level;  ...  private List<Item> items;}interface PlayerService {    Save(Player player);    ...}In your database, you might have a Player table and an Item table.  Saving the player would consist of inserting/updating the player object, then iterating through the items and saving each of the player's items.   When you save the player's data, you'd probably do a SELECT on the table to see if the player is in the database, then if it is you'd update the existing data object and do an UPDATE, otherwise an INSERT.  So that one Save method can result in a myriad of different database calls.PlayerDataAccess playerDAO = dataAccessLayer.getPlayer();if(playerDAO != null) {   playerDAO.Name = player.Name;   ...   dataAccessLayer.updatePlayer(playerDAO);} else {   playerDAO = new PlayerDataAccess(player);   dataAccessLayer.insertPlayer(playerDAO);}saveItems(player.Items);To put it another way:  You're not trying to expose the database through your service.  You're trying to provide a service that persists data.  The service just uses the database to persist.  However, the client programs shouldn't care how the data is being persisted.  If they have to, then your abstraction is leaky.[Edited by - Rycross on September 2, 2009 10:06:31 AM];  Thank you, this is exactly what we do now: we have classes that map our tables, and each object has methods to save, update, delete, select and so on. But since we have close to 100 tables (that is classes), I can't really create 100 services. And I'm not all that free to choose the architecture either, as I'm porting an existing application based on remoting.In addition, I must be able to make arbitrary queries, so even if I solved the ORM issue, I still should be able to pass arbitrary data...   ;  His point is that you're trying to expose too much of your data layer through your service layer. Your service layer should not be directly forwarding data objects and procedure calls. It should operate at a higher level, where it exposes more generalized tasks and uses data objects and procedure calls to make those generalized tasks happen.For example, say you have these tables in your database:FileTypes[TypeName, MIMEType, FileExtension, FormatProvider, (etc)] PK:TypeNameCategories[CategoryName, Description] PK:CategoryNameItems[ItemName, FileTypeName, DateCreated] PK:ItemNameItemRevisions[ItemName, RevisionNumber, Author, RevisionDate, ItemContent] PK:(ItemName, RevisionNumber), FK:ItemName->Items.ItemNameItemRevisionsInCategories[ItemName, RevisionNumber, CategoryName] PK(ItemName, RevisionNumber, CategoryName), FK:(ItemName, RevisionNumber)->ItemRevisions.(ItemName, RevisionNumber)CurrentItemRevisions[ItemName, RevisionNumber] PK:ItemName, FK:(ItemName, RevisionNumber)->ItemRevisions.(ItemName, RevisionNumber)And various Save/Update/Delete procedures associated with them. You don't want to expose these tables and procedures directly through your service. You want to expose things that you can do with them as a whole:GetForumPosts (@ForumTitle)beginselect Items.ItemName, ItemRevisions.Author, ItemRevisions.RevisionDate, ItemRevisions.ItemContent, FileType.MIMEType, FileType.FormatProviderfrom Items inner join ItemRevisions on Items.ItemName = ItemRevisions.ItemNameinner join ItemRevisionsInCategories on ItemRevisions.ItemName = ItemRevisionsInCategories.ItemName and ItemRevisions.RevisionNumber = ItemRevisionsInCategories.RevisionNumberinner join CurrentItemRevisions on ItemRevisions.ItemName = CurrentItemRevisions.ItemName and ItemRevisions.RevisionNumber = CurrentItemRevisions.RevisionNumberwhere ItemName in (select distinct ItemName from ItemRevisionsInCategory where CategoryName = 'ForumPosts')and ItemName in (select distinct ItemNamefrom ItemRevisionsInCategorywhere CategoryName = @ForumTitle)endSaveNewsArticle(@ArticleTitle, @Author, @NewsCategory, @ArticleText)beginif not exists(select ItemName from Items where ItemName = @ArticleTitle)insert into Items(ItemName, FileTypeName, DateCreated) values(@ArticleTitle, 'plain text', GETDATE())declare @NextRevNumber intset @NextRevNumber = 0if exists(select ItemName from ItemRevisions where ItemName = @ArticleTitle)select @NextRevNumber = MAX(RevisionNumber) + 1 from ItemRevisions where ItemName = @ArticleTitleinsert into ItemRevisions(ItemName, RevisionNumber, Author, RevisionDate, ItemContent) values(@ArticleTitle, @NextRevNumber, @Author, GETDATE(), @ArticleText)insert into ItemRevisionsInCategories(ItemName, RevisionNumber, CategoryName) values(@ArticleTitle, @NextRevNumber, 'News')insert into ItemRevisionsInCategories(ItemName, RevisionNumber, CategoryName) values(@ArticleTitle, @NextRevNumber, @NewsCategory)delete from CurrentItemRevisions where ItemName = @ArticleTitleinsert into CurrentItemRevisions(ItemName, RevisionNumber) values(@ArticleTitle, @NextRevisionNumber)exec LogActivity @UserActivity = @Author, @ActivityType = 'New Content', @Description = 'Created article: ' + @ArticleTitle + ', RevNumber: ' + convert(nvarchar, @NextRevNumber)endIn your service layer, you only expose the "GetForumPosts" and "SaveNewsArticle" methods. Those two methods will access much more functionallity in the database, but will only provide the consuming application limited access.   ;Quote:His point is that you're trying to expose too much of your data layer through your service layer. Your service layer should not be directly forwarding data objects and procedure calls. It should operate at a higher level, where it exposes more generalized tasks and uses data objects and procedure calls to make those generalized tasks happen.Unlukily, while WCF was build with SOA in mind, remoting and our project were not. This means that there is not a specific 'cut' where we can separate the client from the server, as the interaction is really complex and involves hundreds of operations. Fully redesigning the whole system (100k+ loc) to be SOA in a few months is not an option, so I must 'force' WCF and our app to work toghether :-(Quote:It should operate at a higher level, where it exposes more generalized tasksThen there would be thousands of them. WCF is simply not the right tools give our current architecture, but Remoting gave us several problems that we hope WCF will solve, and they decided to give it a try.The fact is that currently, with Remoting, mappers are created directly on the server and have near full access to the database. Each class has its own set of instructions, so everything is well encapsulated. Clients ask the server to create an instance of the object, and access it through a remoting proxy.Now this is no longer possible (everything is serialized), so I can either have all those methods in the interface of the service, or have several services. Or I can simply expose the db so that changes to the code on the client can be reduced. It is not good, but it should work well enough with our current architecture (note that these services will never ever be made accessible from others).In addition, add-in mus be able to issue arbitrary queries to the database, so there must be a way to return the results. This is not an implementation detail, but a requirement.My current idea is to use reflection to 'generalize' the main methods (add, update, select, delete) wich directly return an object or accept objects as parameters. I'm still not sure what to do with other specific methods: directly in the service definition?Thank you   ;  What are the problems you were having with remoting that you're trying to solve with WCF? It seems like your application is not going to be well-suited to WCF (at all!) and you might be better of sticking with remoting and trying to fix whatever problems you were having.   ;Quote:Original post by CodekaWhat are the problems you were having with remoting that you're trying to solve with WCF? It seems like your application is not going to be well-suited to WCF (at all!) and you might be better of sticking with remoting and trying to fix whatever problems you were having.I completely agree, but those decisions do not depend upon me :-(Anyway, they have found that remoting does not play very well with NAT and firewalls, so they asked me to port part of the code to WCF so that they can test some of the functionalities in a few different scenarios.I pointed to them the differences between the two aproaches, but I get money to code, not to take decisions :-)   ;  Unfortunately, I think you've run into a situation where there are no silver bullets for your problem. Think about what the Web service actually *is*. It's HTTP request/responses passing XML documents back and forth. With the size of database you're dealing with, nothing is going to be able to automatically handle that kind of mapping. They want the benefits of N-Tier architecture without actually having an N-Tier architecture, meaning what you're doing is fundamentally "rearchitecturing", not at all an easy, quick process.   ;  Another thing you might consider/investigate is using WCF to create REST(Full) services.;Quote:Original post by capn_midnightUnfortunately, I think you've run into a situation where there are no silver bullets for your problem. I supposed that :-(Quote:Another thing you might consider/investigate is using WCF to create REST(Full) services.I've considered REST, but AFAIK it still suffers from the very same problems: I cannot pass references (proxies) and many types are just not serializable (DataReaders). In addition, I still should define hundreds of functions.I will read that document thought, in case I'm wrong...Thank you!   