.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Stored Procs: Map Entity to Functions

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :ASP.Net


I'm using VS2010, .net 4.0, EF 4.0

Using the ADO.Net Entity Data Model (edmx)

I have a view that spans > 1 table.

i can still Map my Entity to Functions?


View Complete Post

More Related Resource Links

Test Run: Automate Testing of Your Stored Procs


Many Windows®-based applications have a SQL ServerT back-end component that contains stored procedures. Although techniques to automatically test functions in the front-end code are well known, the techniques to write test automation for stored procedures are not.

James McCaffrey

MSDN Magazine September 2004

Entity Framework 4.0 Dynamic Data - stored procedure


I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.

Extended stored procs on 64 bit MSSQL 2008

We need to develop some extended stored procedures for 64-bit SQL Server 2008. We have written the DLL in Visual C++ but when we try to register it with the database, we see the following error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not load the DLL c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xp_test.dll, or one of the DLLs it references. Reason: 193(failed to retrieve text for this error. Reason: 15105).(42000,17750) Procedure(xp_test) Can anyone please tell me if you have come across such an issue and how it was successfully resolved? 

using stored procedures in ado.net entity framework model

Hi, I have a issue, i am calling stored procedure through entity model like result=ctx.spname(parmas), i am expecting 0 or 1 as a result after the execution of sp, but its returning unknown values like 178 for number records. Please suggest what went wrong Thanks

Entity Framework and stored procedures with OUTPUT parameters (suggest API)

EF does handle stored procedures with OUTPUT parameters, but the generated methods are not the easiest to work with or safest you could ask for. The ObjectParameter objects you have to create, pass to the method and extract the value from are not type checked and reallyObjectParameter nameParam = new ObjectParameter( "Name", typeof(string)); ObjectParameter ageParam = new ObjectParameter( "Age", typeof(int)); context.GetPersonsNameAndAge( personId, nameParam, ageParam); string name = Convert.IsDBNull(nameParam.Value) ? null : (string)nameParam.Value; int? age = Convert.IsDBNull(ageParam.Value) ? null : (int?)ageParam.Value; is not what I would want to have to write.The ObjectParameter objects do make some sense for stored procedures that return both a resultset and some OUTPUT parameters, because in that case the OUTPUT parameters are only available after the resultset has been consumed and therefore you need some objects that will be populated after the ObjectResult<...> gets processed but for the stored procedures that do not return any resultset, there is no reason not to create the ObjectParameter objects within the method and extract the values there as well. So that the procedure above may be called asstring name; int? age; context.GetPersonsNameAndAge( personId, out name, out age); Things are not so simple for procedures returning both. I'm

problems running stored procs on AS400 iSeries DB2

Hi, Im working on SSIS packages to populate a SQLServer Data warehouse from an I Series AS400 DB2. Downoading data is no problem, using the ISeries IBMDA400 provider. But I need to upload a couple od tables to the iSeries and then run a stored procedure to copy them into the prodution tables. To do this I had to switch to the iSeries provider IBMDASQL. this runs fine when running in BIDS debug mode but when I save the package on the server and run it from a job, it dails with an invalid password on the connection. I have tried this with, Protection level Server Storage, encrypt with password, and dont save sensitive, using a configuration table, in all cases it works fine running in BIDS, on the dev server, but runing as a SQLSERVE job on the same server it fails. when I saved as Server storage i got the following from the job but it works from BIDS Message Executed as user: Domain\XXXXXXXX. ...lity  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  7:38:53 AM  Error: 2010-09-08 07:38:55.30     Code: 0xC0202009     Source: PJCMTest001 Connection manager "GroupQAC.RunProc"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.&

Enterprise t-sql Stored Procs against tables -vs- Views

I work for a very large enterprise and I am wanting to know what is the best way to design t-sql for the stored proces to be used from an application? Option 1 - Be sure to optimize your Database design so your application will only require at most 6 joins and used stored procs for queries using proper indexs to gain the most performance Option 2 - Create Views and use stored procs against the views My questions:  1. What is the perfromance hit in using views.   My thought process is that views should only be used when you know the data you want to return "hence the name 'VIEW', should not be used as a query".   2. Are stored procs against the schema tables the best for performance. 3. What is the best way to tune and ensure your stored proc is optimal with the correct indexes, so you build a lighting fast query? http://www.mmwebs.com

Stored Procs tutorial/learning resources?

I've always used some kind of ORM to fetch data,  LinqToSQL or NHibernate so i guess im spoiled - but now i have a prospective job where they primarily use stored procs.  Not my first choice as i'm not all too comfortable with DBs in general - but i like to brush up on stored procs - almost a Stored Procs for Dummies kind of thingWhats a good resource you guys use for this? I just wanna show that i some initiative and more or less hit the ground running when it comes to stored procs - assuming im selected.Thanks!

Entity Model- Add Function Import - selected stored procedure returns no columns

I am using stored procedures returns columns from pivot table generated dynamically "Execute (@PivotTableSQL)".So the resultset columns cannot be identified. Please let us know how to add function import and Get Column information(create complex Type). Thanks in advance. My stored procedure is alter PROCEDURE spGetQuestGrid( @QID as int)ASBEGIN SET NOCOUNT OFF SET FMTONLY OFF  Declare @optId varchar(10) set @optId = (select DISTINCT Optid from  QuestOptions where  QID = @QID)    DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders =  COALESCE(@PivotColumnHeaders + ', [' + cast(Caption as varchar) + ']' ,  '[' + cast(Caption as varchar)+ ']' ) FROM OptionsDetail where OptId = @optId  DECLARE @PivotTableSQL NVARCHAR(MAX)  SET @PivotTableSQL = 'select * from  ( select  Caption,Optid  from OptionsDetail ) as dt PIVOT  ( min(Optid) FOR Caption IN ( '+ @PivotColumnHeaders + '))as pt'     Execute (@PivotTableSQL)  END 

Report using Stored Proc calling multiple Stored Procs


Hi guys,

I am new to SSRS. I have to create a report from a Stored Proc. Thi Stored Proc excepts just one parameter 'ErecNo'. This parameter is passed to fetch the columns from table1. This result set contains OrderID. The OrderId is further passes to 5 SP's in to fetch 5 more result sets. Output of the main stored proc contains 6 result sets.

Now I have to create a report using this SP to show all the Result sets in a Single report. But When I pass the Parameter in SP in SSRS. It ends up showing just one result set with Order ID. 

Can you please guide me how can I show all the result set in a Single report? 



How to Bind Selective Columns from Stored Proc to Gridview using the entity framework


Hi Guys,

I have this entity model>> http://img840.imageshack.us/img840/306/schemaj.jpg and I would like to bind  a list  which consists of: employee's

  • emloyee_firstname(employees table) + employee_lastname(employees table)
  • employee's extension(employee_extension table)
  • deparment name(departments table)

to my gridview. I already created my stored procedure

    Concat(employees.employee_firstname," ",employees.employee_lastname) as name
    , extensions.extension_number
    , departments.department_description
    INNER JOIN employees 
        ON (employee_extensions.employee_id = employees.employee_id)
    INNER JOIN extensions 
        ON (employee_extensions.extension_id = extensions.extension_id)
    INNER JOIN departments 
        ON (employees.department_id = departments.department_id)

But my problem is, i don't know how to bind it since i don't have an entity that contains the employee's name, extension# and departments name. Do I need to create a custom class that consists of said columns? or is their a proper way to do this? I'

Entity Framework - stored procedure mapping, excecuting SP with float parameters separated by commas



Im using Entity framework with POCO entity generator template + SQL Server. I have mapping to stored procedure that takes two floats as input.

After executing function from code with two doubles as parameters: 1.23 and 4.56, SQL Profiler shows:

exec storedProcedureName @arg1 = 1,23, @arg2= 4,56

This is causing an error because it looks like that stored procedure takes 4 arguments, but it should take only two and there should be dots insted of commas in these floats.

My SQL Server collation is SQL_Latin1_General_CP1_CI_AS. I tried to set:

           Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
           Thread.CurrentThread.CurrentUICulture = CultureInfo.InvariantCulture;

in DataContext Constructor, and di

Find all stored procedures/functions granted to a specific user


SQL 2005.

How can I find all routines (sp, fn) exec granted to a specific user in a given database ?

Thanks !!

Issue while creating a BCS entity in visual studio for SQL stored procedures


Hi Everyone,

                  I have an issue while creating an entity in Visual studio for SQL stored procedures.I have two input parameters startdate and enddate as datetime datatypes in the stored procedures.I have added two input parameters startdate and enddate in the Readlist and ReadItem methods and created two filter descriptors startdate and enddate of "comparison" type and related these to their respective input parameters.

                 In my readlist and readitem methods I have used LINQ TO SQL query to query the SP.When I debug the program,I am receiving an error message-->remote procedure cannot be debugged.Also my parameters are not being passed to the SP.Any help is appreciated..also if any one has step by step approach,that helps me a lot.

Creating BCS entity for a SQL stored procedure that has parameters using visual studio


Hi Everyone,

                 can anyone help me in creating  BCS entity using BCDM model in visual studio using Linq to sql query for a SQL stored procedure.I am receiving remote debugging error and few other errors.Your step by step approach helps me a lot,I have been playing with this for long,but still no answer.


Thanks ,

entity framwork with stored prcedure vs entity framwork without stored procedures


I am new to entity framwork , it is really very good , but I want to know what is the differance between using entity framwork with stored procedure or without stored procedure , which one the faster and what is the benifits for using stored procedures with entity framwork

Trying to use stored procedure in Entity Framework


I have a aspx page that only has a button and a button click event.

My code behind looks like this

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CommerceModel;

public partial class test : System.Web.UI.Page
    protected void Page_Load(object sender, EventArgs e)

    protected void Button1_Click(object sender, EventArgs e)
        using (CommerceEntities context = new CommerceEntities())
                UBOtest myRecord = new UBOtest
                    dataA = "Tina",
                    dataB = "Feeney"
            catch (Exception ex)

This code works fine if I have no insert stored procedure mapped to Table UBOtest in the EntityDataModel.However, when I map the stored procedure to the EntityDataModel and add UBOtestinsert I get an error that reads "An error occured while updating the entries. See the inner exception for details.">/P>

Then the innerException message is "Invalid object name 'UBOtes

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend