none
Call Stored Proceduras without using edmx file RRS feed

  • Question

  • I have following code. I have written that code manually, without using wizard.

     

     

    namespace EF001
    
    { 
    
        public class Client
    
        {
    
            public Client()
    
            {
    
                Orders = new List<Order>();
    
            }
    
            public int ID { get; set; }
    
            public string Name { get; set; }
    
            public List<Order> Orders { get; set;}
    
        }
    
    
    
        public class Order
    
        {
    
            public int ID { get; set; }
    
            public DateTime OperDate { get; set; }
    
            public int TotalSum { get; set; }
    
            
    
        }
    
        public class Context1 : DbContext
        {
            public Context1(string name)
                : base(name)
            {
            }
            public DbSet<Client> Clients { get; set; }
            public DbSet<Order> Orders { get; set; }
    
        
            public void RunStp(string Name)
            {
        
            
                 ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction(Name); //ERROR on this step            
    
            }
    
            
        }
    
    
    }

     

    And when I use RunStp function I  receive  Error. Is it possible to call stored procedure without using Wizard and write code manually as I do. Thank you.

     

     

    Thursday, December 15, 2011 11:58 AM

Answers

  • I already find some solution.

     

     public int RunStp3(string STPName, long id,out string str)
            {
                string strSql = String.Format(@"DECLARE	@rv int
                        EXEC	@rv = {0} @ID, @str OUTPUT
                        SELECT	@rv", STPName);
    
    
                SqlParameter p1 = new SqlParameter("@ID", id);
    
                SqlParameter p2 = new SqlParameter("@str", "");
                p2.Direction = ParameterDirection.Output;
                p2.Size = 4000;
    
    
                int retval = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<int>(strSql,p1, p2).FirstOrDefault();
                str = p2.Value.ToString();
    
                return retval;
    
            }


    But if there are some ideas please write....


    • Edited by progrish82 Thursday, December 15, 2011 9:58 PM
    • Marked as answer by progrish82 Thursday, December 15, 2011 9:58 PM
    Thursday, December 15, 2011 9:57 PM

All replies

  • Hi progrish82;

    You should be able to do this. Note EntityToFill is a class having all the properties that the SP will return.

     

    DbContexInstance.Database.SqlQuery<EntityToFill>("SQL Statement to Execute SP", Object[] parameters);
    

     

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Edited by Fernando Soto - MCSD Thursday, December 15, 2011 4:03 PM
    • Marked as answer by progrish82 Thursday, December 15, 2011 7:38 PM
    • Unmarked as answer by progrish82 Thursday, December 15, 2011 7:40 PM
    Thursday, December 15, 2011 4:03 PM
  • Thank you for answer.

    I can use SqlQuery function.

    I can also use ExecuteStoreCommand or ExecuteStoreQuery 

     ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreCommand(STPName);

    But I wont to receive the value that Stored Procedure returned. In all of this cases it equal to -1. Have any ideas?

     

    Thursday, December 15, 2011 7:37 PM
  • I already find some solution.

     

     public int RunStp3(string STPName, long id,out string str)
            {
                string strSql = String.Format(@"DECLARE	@rv int
                        EXEC	@rv = {0} @ID, @str OUTPUT
                        SELECT	@rv", STPName);
    
    
                SqlParameter p1 = new SqlParameter("@ID", id);
    
                SqlParameter p2 = new SqlParameter("@str", "");
                p2.Direction = ParameterDirection.Output;
                p2.Size = 4000;
    
    
                int retval = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<int>(strSql,p1, p2).FirstOrDefault();
                str = p2.Value.ToString();
    
                return retval;
    
            }


    But if there are some ideas please write....


    • Edited by progrish82 Thursday, December 15, 2011 9:58 PM
    • Marked as answer by progrish82 Thursday, December 15, 2011 9:58 PM
    Thursday, December 15, 2011 9:57 PM
  • Hi progrish82,

    Welcome to MSDN Forum!

    I'm glad to hear that you have solved the issue. If you have any problems, please feel free to let me know.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 16, 2011 2:15 AM
    Moderator