Print Page | Close Window

Calling Stored procedure using Cocktail WPF Temphire implementation

Printed From: IdeaBlade
Category: Cocktail
Forum Name: Community Forum
Forum Discription: A professional application framework using Caliburn.Micro and DevForce
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3815
Printed Date: 16-Apr-2024 at 1:00am


Topic: Calling Stored procedure using Cocktail WPF Temphire implementation
Posted By: joelozina
Subject: Calling Stored procedure using Cocktail WPF Temphire implementation
Date Posted: 22-Nov-2012 at 12:03am
Hi

I've based my WPF application on the Temphire implementation. How would I call a stored procedure and where would the call be made? 

Would you put it in a service or in the TempHireDbContext : DbContext?


    internal class TempHireDbContext : DbContext
    {
................

        public class Initializer : IDatabaseInitializer<MembershipDbContext>
        {
            public void InitializeDatabase(MembershipDbContext context) {
    
            context.Database.ExecuteSqlCommand("Call Proc...");
            }
        }

Or is there somewhere else I can call context.Database.ExecuteSqlCommand

Thanks,
Joe


Replies:
Posted By: mgood
Date Posted: 22-Nov-2012 at 2:02am
If the sproc returns data, then you would call it from a method in the appropriate repository. If the sproc is closer to a process, I would call it from a domain service. Once you map your stored procedure, there will be a method on the EntityManager to call it.
http://drc.ideablade.com/devforce-2012/bin/view/Documentation/stored-procedure-queries - http://drc.ideablade.com/devforce-2012/bin/view/Documentation/stored-procedure-queries
 
 

Posted By: joelozina
Date Posted: 22-Nov-2012 at 3:32am
I've tried to follow the Stored procedure queries page as much as possible. 

How do I map the stored procedure? The example uses the EDM Designer.


Here is my entity manager

    public class MembershipEntities : EntityManager
    {
        static MembershipEntities()
        {
            TransactionSettings.Default = new TransactionSettings(TransactionSettings.Default.IsolationLevel,
                                                                  TransactionSettings.Default.Timeout, false);
        }

        public MembershipEntities(EntityManagerContext context)
            : base(context)
        {
        }

        public int GetOrderTotal() {}

        public StoredProcQuery GetOrderTotalQuery() {}

    }


Here is my repository 

        public OrderRepository(IEntityManagerProvider<MembershipEntities> entityManagerProvider)
            : base(entityManagerProvider)
        {
        }

        public new MembershipEntities EntityManager
        {
            get { return (MembershipEntities)base.EntityManager; }
        }

        public int GetOrderTotal()
        {
            var results = EntityManager.GetOrderTotal();

            return results;
        }

    }


Thanks in advance.

Posted By: mgood
Date Posted: 22-Nov-2012 at 9:14am
My bad, I didn't pick up on the fact that you are using Code-First of course. Entity Framework Code-First does currently not support stored procedures. If I remember correctly from this year's //Build/ conference, sproc support is planned for Entity Framework 6.
 
You'll have to use an EDM or call your stored procedure directly from a server-side method and return the data to the client.
 
http://drc.ideablade.com/devforce-2012/bin/view/Documentation/rsmc-query - http://drc.ideablade.com/devforce-2012/bin/view/Documentation/rsmc-query

Posted By: joelozina
Date Posted: 22-Nov-2012 at 4:46pm
I'm using the WPF Temphire application as a reference implementation, So I've got a WPF Client and a SQL Server.

Is the Server on a Remote Server Method the database Server(SQL Server) or am I missing something?

Posted By: mgood
Date Posted: 22-Nov-2012 at 5:09pm
The EntityServer. The TempHire WPF example as-is is a 2-tier deployment. In this case the EntityServer is deployed locally as part of the AppDomain.
 
http://drc.ideablade.com/devforce-2012/bin/view/Documentation/deploy-two-tier - http://drc.ideablade.com/devforce-2012/bin/view/Documentation/deploy-two-tier
 
TempHire WPF can also be deployed n-tier, by deploying an actual application server tier just like in case of TempHire Silverlight.
 
http://drc.ideablade.com/devforce-2012/bin/view/Documentation/deploy-n-tier - http://drc.ideablade.com/devforce-2012/bin/view/Documentation/deploy-n-tier
 
In either case it's always the EntityServer that talks to the database. Whether you deploy 2-tier or n-tier doesn't matter, the EntityManager always talks to an EntityServer.

Posted By: jbiddle61
Date Posted: 13-Sep-2013 at 1:45pm
Marcel, how exactly do you 'call your stored procedure directly from a server-side method'?

Every time I try this using a StoredProcQuery with a code first model I get the following exception thrown:
       The FunctionImport 'StoredProcName' could not be found in the container 'DbContextName'

Thanks,

           John

Posted By: mgood
Date Posted: 13-Sep-2013 at 2:08pm
John,
StoreProcQuery doesn't work with Code-First. As stated above, EF Code-First does not currently support stored procedures. You can't call them directly through EF. If you google you'll find several StackOverflow posts and other posts that show how you can currently execute a stored procedure. You would implement any of those approaches that work for your particular case inside of the server-side method. Here's an example for how to call a stored procedure to insert data.

http://blogs.msdn.com/b/wriju/archive/2011/05/14/code-first-4-1-using-stored-procedure-to-insert-data.aspx - http://blogs.msdn.com/b/wriju/ar http://blogs.msdn.com/b/wriju/archive/2011/05/14/code-first-4-1-using-stored-procedure-to-insert-data.aspx - chive/2011/05/14/code-first-4-1-using-stored-procedure-to-insert-data.aspx

and here is another example for how to deal with stored procedures that have output parameters.

http://weblogs.asp.net/dwahlin/archive/2011/09/23/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters.aspx - http://weblogs.asp.net/dwahlin/archive/2011/09/23/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters.aspx

Posted By: jbiddle61
Date Posted: 13-Sep-2013 at 2:15pm
I saw those and other similar examples of how to do this with an EntityFramework DBContext, but I have no idea how to get that from the EntityManager that is passed to the Remote Server Call Method.

If this is covered in your docs somewhere, please point me in the right direction.

      John

Posted By: mgood
Date Posted: 13-Sep-2013 at 2:30pm
You don't get it from the EM, you simply create a new instance of your DbContext.

Posted By: jbiddle61
Date Posted: 13-Sep-2013 at 2:41pm
OK, next question:

How do I get the connection string to pass into the DBContext constructor?

Sorry if this is documented somewhere, but I can't find it.

        John

Posted By: jbiddle61
Date Posted: 13-Sep-2013 at 3:07pm
I got the connection string from the ConfigurationManager, but I was hoping there was a way to get it from the passed in EntityManager.

Now I am getting the following exception thrown:

{System.ServiceModel.CommunicationException: An error occurred while receiving the HTTP response to http://localhost:54557/EntityServer.svc. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details. ---> System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
   at System.Net.Sockets.Socket.EndReceive(IAsyncResult asyncResult)
   at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
   --- End of inner exception stack trace ---
   at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)
   at System.Net.PooledStream.EndRead(IAsyncResult asyncResult)
   at System.Net.Connection.ReadCallback(IAsyncResult asyncResult)
   --- End of inner exception stack trace ---
   at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
   at System.ServiceModel.Channels.HttpChannelFactory`1.HttpRequestChannel.HttpChannelAsyncRequest.CompleteGetResponse(IAsyncResult result)
   --- End of inner exception stack trace ---
   at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannelProxy.TaskCreator.<>c__DisplayClass5`1.<CreateGenericTask>b__4(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at IdeaBlade.EntityModel.RemoteEntityServerProxyBase.<ExecuteOnServerAsync>d__0`1.MoveNext()}

Posted By: mgood
Date Posted: 13-Sep-2013 at 3:24pm
This could be a number of this. I would debug the server-side method first and make sure that it actually executes without errors.

Posted By: jbiddle61
Date Posted: 13-Sep-2013 at 5:07pm
I finally figured out what I was doing 'wrong'.
I was trying to return the raw results of SqlQuery instead of adding a .ToList()!

Thanks for pointing me in the right direction Marcel!

      John


Print Page | Close Window