none
updating to reflect changes System.Data.Linq.DataContext

    Question

  • I am using Visual Studio 2012

    OK, I have made some changes to a stored procedure in my database, but those changes are not being reflected in the code.  The code where the missing parameter should be resides in an auto generated class inherited from System.Data.Linq.DataContext.  I found out how to show the various parts of this on the map, and then tried deleting my stored procedure from the map and then dragging from the database in 2012 (the interface in 2012 that is kind of a mini SQL server management studio built into 2012), but nothing appeared on the map.  So I quit without saving, and I'm back to where I was.

    How do I update this puppy to make the changes in the database reflect in my code?


    • Edited by jamesiw Wednesday, September 25, 2013 8:15 PM
    Wednesday, September 25, 2013 8:12 PM

Answers

  • Does anyone know what to do if the person who posted the question found the answer and no longer needs an answer?  Can you close the case?

    Anyways, thanks for the replies.  Forget about it, because this solution does not apply to me, because I found out from a co-worker that this code was generated with a 3rd party add-on package.  So this was auto-generated by a package not built into Visual Studio.  That is why everything seem weird.  Anyways, my co-worker found the solution.

    Thanks.


    Friday, September 27, 2013 12:08 PM

All replies

  • Hello,

    From your description, I did the steps the same with you using VS2012, however those changes are reflected in the code.

    Here is my steps:

    1. I created a procedure named ProTest1() as below:

    CREATE PROCEDURE [dbo].[ProTest1]
    
    AS
    
                    SELECT * from [Order]
    
    RETURN 0

    It is very simple. Just to return a collection of Order table.

    The mapped function code in program was:

    [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.ProTest1")]
                  public ISingleResult<ProTest1Result> ProTest1()
                  {
                         IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
                         return ((ISingleResult<ProTest1Result>)(result.ReturnValue));
                  }

    And the DataContext worked fine:

    using (DataClasses1DataContext dataContext = new DataClasses1DataContext())
                {
                    var result = dataContext.ProTest1();
                }

    2. I changed the procedure to be:

    CREATE PROCEDURE [dbo].[ProTest1]
    
                    @OrderID int
    
    AS
    
                    SELECT * from [Order] where OrderID = @OrderID
    
    RETURN 0

    It accepts an integer parameter.

    3. Then I deleted the mapped function in dbml designer:


    4. And then I dragged the changed procedure from database to the dbml designer:


    The mapped function was:

    [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.ProTest1")]
                  public ISingleResult<ProTest1Result> ProTest1([global::System.Data.Linq.Mapping.ParameterAttribute(Name="OrderID", DbType="Int")] System.Nullable<int> orderID)
                  {
                         IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), orderID);
                         return ((ISingleResult<ProTest1Result>)(result.ReturnValue));
                  }

    And the DataContext worked fine:

    using (DataClasses1DataContext dataContext = new DataClasses1DataContext())
                {
                    var result = dataContext.ProTest1(1);
                }

    If this does not work for you, please let me know.

    Thanks &Regards.

    Thursday, September 26, 2013 7:30 AM
  • Apparently my database code has been  generated in a different way.  There is no .dbml file.

    Here are screenshots from my solution explorer that hopefully will give you or someone who has seen it before a clue as to how this database is being accessed in Visual Studio.  I have greyed out some text, but it should give the idea.  I did not create this part of the code, this is a project that someone else started.  The .cs class is the one that inherits from System.Data.Linq.DataContext, and it is auto-generated code.

    BTW, the way I got the "map" to show up was right clicking on a subsection (on of the ones with a dark yellow icon, as you can see in the second image below) and then choosing show on code map.



    • Edited by jamesiw Thursday, September 26, 2013 12:33 PM
    Thursday, September 26, 2013 12:27 PM
  • Hi jamesiw,

    I am strange that why you do not have a dbml file. It is a designer file for LinqToSql item.

    If we create a LinqToSql item, it will create the dbml file automatically like below:


    I have a try without using the dbml designer.

    I change the procedure ProTest1 form accept one parameter to accept no parameter and correspondingly I change the function in the program by hand directly. It works fine.

    CREATE PROCEDURE [dbo].[ProTest1]
    
                    @OrderID int
    
    AS
    
                    SELECT * from [Order] where OrderID = @OrderID
    
    RETURN 0

    To:

    CREATE PROCEDURE [dbo].[ProTest1]
    
    AS
    
                    SELECT * from [Order]
    
    RETURN 0
    [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.ProTest1")]
                  public ISingleResult<ProTest1Result> ProTest1([global::System.Data.Linq.Mapping.ParameterAttribute(Name="OrderID", DbType="Int")] System.Nullable<int> orderID)
                  {
                         IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), orderID);
                         return ((ISingleResult<ProTest1Result>)(result.ReturnValue));
                  }


    To

    [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.ProTest1")]
                  public ISingleResult<ProTest1Result> ProTest1()
                  {
                         IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
                         return ((ISingleResult<ProTest1Result>)(result.ReturnValue));
                  }


    The designer has not been changed.


    And the function ProTest1() is in class DataClasses1DataContext which inherits the System.Data.Linq.DataContext.

    So it seems that without the dbml file we need to modify the mapped function by hand.

    You can have a try and hope it will help you.

    Regards.



    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 27, 2013 7:33 AM
  • I had the same problem, here is how did I manage it:

    1. Open Visual Studio Server Manager
    2. Open your procedure
    3. Save it (CTRL + S), it will recompile it in SQL
    4. Open your dbml
    5. Remove your procedure
    6. Drag and drop your procedure from Server Manager to DBML designer

    Please Mark as Answer and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Friday, September 27, 2013 9:27 AM
  • Does anyone know what to do if the person who posted the question found the answer and no longer needs an answer?  Can you close the case?

    Anyways, thanks for the replies.  Forget about it, because this solution does not apply to me, because I found out from a co-worker that this code was generated with a 3rd party add-on package.  So this was auto-generated by a package not built into Visual Studio.  That is why everything seem weird.  Anyways, my co-worker found the solution.

    Thanks.


    Friday, September 27, 2013 12:08 PM
  • Let me just write the solution in case other google the same issue.

    Adding parameters to a stored procedure sometimes doesn't show in the dbml file.

    Close dbml, and open it with notepad or something outside the studio.

    The procedures are normally down in the bottom of the file, and it should be fairly simple to add parameters yourself.

    Wednesday, October 02, 2013 2:35 PM