New Posts New Posts RSS Feed: Calling Stored procedure using Cocktail WPF Temphire implementation
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Calling Stored procedure using Cocktail WPF Temphire implementation
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
joelozina View Drop Down
Newbie
Newbie


Joined: 19-Nov-2012
Location: Sydney
Posts: 3 		Post Options Post Options   Quote joelozina Quote  Post ReplyReply Direct Link To This Post Topic: Calling Stored procedure using Cocktail WPF Temphire implementation
    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
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583 		Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post 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.
 
 
Back to Top
joelozina View Drop Down
Newbie
Newbie


Joined: 19-Nov-2012
Location: Sydney
Posts: 3 		Post Options Post Options   Quote joelozina Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583 		Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post 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.
 
Back to Top
joelozina View Drop Down
Newbie
Newbie


Joined: 19-Nov-2012
Location: Sydney
Posts: 3 		Post Options Post Options   Quote joelozina Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583 		Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post 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.
 
 
TempHire WPF can also be deployed n-tier, by deploying an actual application server tier just like in case of TempHire Silverlight.
 
 
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.
Back to Top
jbiddle61 View Drop Down
Newbie
Newbie


Joined: 18-Dec-2012
Location: Arizona
Posts: 24 		Post Options Post Options   Quote jbiddle61 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583 		Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post 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.


and here is another example for how to deal with stored procedures that have output parameters.
Back to Top
jbiddle61 View Drop Down
Newbie
Newbie


Joined: 18-Dec-2012
Location: Arizona
Posts: 24 		Post Options Post Options   Quote jbiddle61 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583 		Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post Posted: 13-Sep-2013 at 2:30pm
You don't get it from the EM, you simply create a new instance of your DbContext.
Back to Top
jbiddle61 View Drop Down
Newbie
Newbie


Joined: 18-Dec-2012
Location: Arizona
Posts: 24 		Post Options Post Options   Quote jbiddle61 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
jbiddle61 View Drop Down
Newbie
Newbie


Joined: 18-Dec-2012
Location: Arizona
Posts: 24 		Post Options Post Options   Quote jbiddle61 Quote  Post ReplyReply Direct Link To This Post 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()}
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583 		Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
jbiddle61 View Drop Down
Newbie
Newbie


Joined: 18-Dec-2012
Location: Arizona
Posts: 24 		Post Options Post Options   Quote jbiddle61 Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down