How can I create a WCF RIA service that uses one Stored Procedure that connects to multiple databases RRS feed

  • Question

  • Hello All,

    I am trying to create a Visual Studio LightSwitch application that accesses data via a stored procedure.  The stored procedure has no inputs, you call it and it returns all records in the last 24 hours.  I have found many write ups on creating the connection using WCF RIA services.  

    The issue I run into is that these are all based off of single database connections.  Most of my data is located on a remote SQL database, but some of the data and the stored procedure are located on another SQL database.

    According to the below MSDN guide, I have to map my stored procedure to the tables.  But I don't have a way to select/map the remote tables to the stored procedure when creating the ADO.NET connection because its only connected to 1 database.  Do I have to create 2 connections in the same ADO.NET Entity Data Model and then map to the proper Stored Procedure?  Maybe I don't have to map anything, since I am not doing anything other than just trying to call the stored procedure.

    Walkthrough: Updating Records Using Stored Procedures

    Thanks for any advice in advance.


    Friday, January 18, 2013 4:39 PM

All replies

  • How are you using the data in LightSwitch? Is it scalar data, as in a single value? Or are you displaying it in rows in a data grid?

    I don't believe that you'd need to model the data (but depends on what you're dong with it). I would imagine that you just use two ADO Connection/Command objects, just like you normally would in ADO.NET.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
    By doing this you'll help others to find answers faster.

    Monday, January 21, 2013 3:07 AM
  • Hi Yann,

    Thank you for the reply.  Ok, a little more detail on what I am trying to do.  I'm not an expert, I have only created one LightSwitch app under the previous version of LightSwitch so hopefully this makes some sense.  

    I'm trying to get the data into one of the LightSwitch datagrid screens like Search for example.  Our SQL guy put together this super complicated stored procedure that pulls 24 hours of data from all of these different tables.  Some of them are local tables and others are running on a remote SQL server.  

    The reason for all that is because there are basically a series of factory floors that run production and each line has its own SQL server with machines reporting to it.  He combines all of the data via the stored procedure for easy access in a web report.  Because their is so much data the stored procedure was the way to go.  

    So back to my issue, when I try to create an app in LightSwitch I go to add data source and select SQL and put in the credentials for the server that has the stored procedure.  Of course when it connects in the wizard there is no way to select the stored procedures on the server, only tables and views.  So this is where I was lead to believe that I have to create a WCF RIA service.  

    When I do that I found that I need to create a ADO.NET connection for the RIA service to use.  All of the examples I have read show connecting to and selecting the stored procedure and the tables it uses.  Then you have to use the Map stored procedure command and that's where I get stuck.  Because I can't map anything, but maybe I don't have to map anything?  I'm starting to think I am over analyzing it and the examples of 'mapping' are for when people want to pass parameters to their stored procedure.  I don't have any parameters for the stored procedure.

    Sorry for the long explanation :) 



    Wednesday, January 23, 2013 2:50 AM
  • If what I have read is correct the dba has created a stored proc that when run collects data from the source server as well as multiple databases around the floor.

    If it was me I would be using linked servers and have the one stored proc do the call, in this case you would only need the one connection and the stored procedure would do the running around for you.

    Wednesday, January 23, 2013 8:25 PM