none
How to use one stored procedure among multiple DataContexts RRS feed

  • Question

  • Hello,

    I have about 400 tables in DB , so i use multiple datacontext by module , one for invoices , one for purchase , one for HR , etc
    I have some stored procedures that i want to call from several datacontext , those procedures are working like common "services" to my application ,
    I discovered after dragging the procedure into the second datacontext , gives error that it is already defined as Public in another location , even when i changed the name of the procedure in the second context it still gives me the error , is there any work arounds ?

    some details:
    procedure name is "psp_Services_SNo_Update" , a partial class is generated into first datacontext named:

    Partial Public Class psp_Services_SNo_UpdateResult

    even if i change the name property of the procedure in second datacontext it STILL generating same partial class name

    Partial Public Class psp_Services_SNo_UpdateResult

    and so genrates error preventing complie , any solution ?

    Thanks
    Bassam
    Sunday, August 16, 2009 7:04 AM

Answers

  • Hello Bassam,

    Dont drag it in at all.

    You need to manually define the procedure in the partial class of each context if you like. Here is an example manual implementation of a SP inside a datacontext (but stored in the partial class).

            /// <summary>
            /// Calls the stored procedure   
    /// </summary> [Function(Name = "dbo.[data_Insert]")] public int Data_Insert([Parameter(Name = "id", DbType = "BigInt")] System.Nullable<long> id, [Parameter(Name = "time", DbType = "DateTime")] DateTime time, [Parameter(Name = "value", DbType = "Float")] System.Nullable<float> value, [Parameter(Name = "svalue", DbType = "nvarchar(511)")] string svalue ) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), id, time, value, svalue); return ((int)(result.ReturnValue)); }
    Monday, August 17, 2009 1:11 AM
  • You could resolve this issue by changing the namespace on your context for each instance. However, this may cause multiple result types be created as I believe the result type has to be included in the context in order to map it using the designer. Creating the code for the procedure manually isn't that hard and offers a more flexible solution where you can map the result type explicitly regardless of where the procedure and classes are declared.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Thursday, August 20, 2009 9:37 PM
    Moderator

All replies

  • Hello Bassam,

    Dont drag it in at all.

    You need to manually define the procedure in the partial class of each context if you like. Here is an example manual implementation of a SP inside a datacontext (but stored in the partial class).

            /// <summary>
            /// Calls the stored procedure   
    /// </summary> [Function(Name = "dbo.[data_Insert]")] public int Data_Insert([Parameter(Name = "id", DbType = "BigInt")] System.Nullable<long> id, [Parameter(Name = "time", DbType = "DateTime")] DateTime time, [Parameter(Name = "value", DbType = "Float")] System.Nullable<float> value, [Parameter(Name = "svalue", DbType = "nvarchar(511)")] string svalue ) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), id, time, value, svalue); return ((int)(result.ReturnValue)); }
    Monday, August 17, 2009 1:11 AM
  • You could resolve this issue by changing the namespace on your context for each instance. However, this may cause multiple result types be created as I believe the result type has to be included in the context in order to map it using the designer. Creating the code for the procedure manually isn't that hard and offers a more flexible solution where you can map the result type explicitly regardless of where the procedure and classes are declared.

    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Thursday, August 20, 2009 9:37 PM
    Moderator