C# – Adding stored procedure to DBML doesn’t generate *.designer.cs code

asp.netclinq-to-sqlvisual-studio-2008

I'm trying to add a new stored procedure to my Asp.Net website with Visual Studio 2008, the way I did some time ago, but this doesn't generate any code in the *.designer.cs and when I try to call the SP, I just get a undefined/not-found error.

Here is what I have done so far :

make sure I have read/write access to MyApp.dbml, MyApp.dbml.layout and MyApp.designer.css
Open the DBML (in "graphic" mode)
Refresh the list of SPs in the server explorer
Drag and Drop the targeted SP onto the DBML
Save it
Build the website

The XML inside the DBML is created with the correct types for each column of the result, but absolutely nothing is created in the designer.cs.

I checked, and no "new" designer.cs is created like it did for this person.
I tried using "Run custom tool" (as suggested here) with and without deleting the MyApp.designer.cs, and it never did anything

Does anybody know were the problem might come from? Am I missing something?

Best Answer

As nothing else seemed to work, I used Pleun's idea, which worked like a charm:

create a new empty Asp.Net Web site with a whole new DBML
import my SPs into this new DBML
copy generated code from the new empty project to my old one (theses portions of code are easy to spot as they are the only ones mention my SPs)
1. XML in the MyApp.dbml
2. Csharp in the MyApp.designer.cs (the method that calls the SP + the partial class with accessors for the result of the SP)
build my "old" project

In fact, I had to close and reopen VS for that to work completely.

I mentioned in my original post that I suspected the temp table. The temp table was created by "SELECT INTO #PeopleCameMissing" command therefore its definition is dynamic so when the stored procedure is added to "dbml" designer, the generator has no clue about the table's definition. What if I let the table definition known to the generator, maybe I'd be able to get the desired return type. Based on this conjecture, I ran a test. I declared a table as follows:


    DECLARE @PeopleCameMissing TABLE(
        MemberID int,
        FirstName nchar(20),
        MiddleName nchar(20),
        LastName nchar(20),
        Suffix nchar(10),
        PhoneHome nchar(20),
        PhoneCell nchar(20),
        Email  nchar(80)
    );

And changed the original statement as follows:


    INSERT INTO @PeopleCameMissing (
        MemberID,
        FirstName,
        MiddleName,
        LastName,
        Suffix,
        PhoneHome,
        PhoneCell,
        Email)
    SELECT 
        c.MemberID,
        c.FirstName,
        c.MiddleName,
        c.LastName,
        c.Suffix,
        c.PhoneHome,
        c.PhoneCell,
        c.Email
        FROM [dbo].[Members] c
        WHERE c.MemberID IN (SELECT a.MemberID FROM #PeopleCameMissing a)
        ORDER BY c.LastName,c.FirstName;

    DROP TABLE #PeopleCameMissing;

    SELECT * 
        FROM @PeopleCameMissing 
        ORDER BY LastName,FirstName;

And then I added the stored procedure to "dbml" designer again. This time the generator created the desired return type: "ISingleResult". It seems to prove my conjecture.

Best Answer

Related Solutions

Autogeneration of a DataContext designer file when using SqlMetal and Visual Studio

C# – How does MSLinqToSqlGenerator determine the return type of a stored procedure

Related Topic