Try to return different numbers om columns from stored procedure

Question

Hi.
I have a problem, I've been searching information about, and even if it should be an solution, I do something wrong here.
The case is: I got a stored procedure which return 3 or 11 columns, depending on one of the in-parameters.
So, I drag my SP into the designer, modify it from returning an Int to IMultipleResults (yes, I know, I am gonna put this in a separate partial class, to awoid it from being owerwritten).
But when I call the method I got a runtime error "SqlException: Incorrect syntax near ')'.."
It builds ok, but clearly I misunderstood something. Here is some example:

The sp:s name is SP_myMutiResultStoredProc, and it got 16 inparameters (auto-generated by the dbml).
Obviously, there is something I misunderstood here.

The linq-call looks like:

[Function(Name = "dbo.SP_myMutiResultStoredProc")]
[ResultType(typeof(type_P))]
[ResultType(typeof(type_F))]
public IMultipleResults SP_myMutiResultStoredProc(
                    [Parameter(DbType = "VarChar(5)")] string mainID,
                    [Parameter(DbType = "Int")] System.Nullable<int> myField1_ID,
                    [Parameter(DbType = "Int")] System.Nullable<int> myField2_ID,
                    [Parameter(DbType = "Int")] System.Nullable<int> myField3_ID,
                    [Parameter(DbType = "Int")] System.Nullable<int> myField4_ID,
                    [Parameter(DbType = "VarChar(30)")] string paramField1,
                    [Parameter(DbType = "VarChar(30)")] string paramField2,
                    [Parameter(DbType = "VarChar(1)")] System.Nullable<char> paramField3,
                    [Parameter(DbType = "Decimal")] System.Nullable<decimal> paramField4,
                    [Parameter(DbType = "VarChar(254)")] string paramField5,
                    [Parameter(DbType = "DateTime")] System.Nullable<System.DateTime> paramField6,
                    [Parameter(DbType = "VarChar(20)")] string paramField7,
                    [Parameter(DbType = "VarChar(20)")] string paramField8,
                    [Parameter(DbType = "VarChar(254)")] string paramField9,
                    [Parameter(DbType = "Decimal")] System.Nullable<decimal> paramField10,
                    [Parameter(DbType = "Decimal")] System.Nullable<decimal> paramField11)
        {
   IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), mainID, myField1_ID, myField2_ID, myField3_ID, myField4_ID, paramField1, paramField2, paramField3, paramField4, paramField5, paramField6, paramField7, paramField8, paramField9, paramField10, paramField11);
   
   //(It's on this line " IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), mainID, myField1_ID, myField2_ID, myField3_ID, myField4_ID, paramField1, paramField2, paramField3, paramField4, paramField5, paramField6, paramField7, paramField8, paramField9, paramField10, paramField11);"
//I got the error message in runtime)
   
   return ((IMultipleResults)(result.ReturnValue));
        }
  
  
The function call is:
    string main_id = "XX";
    char @paramField3 = 'P';

 //(If I change @paramField3 to 'F', I got 3 columns as result, and 'P' is the one that gives me 11 colums)
 
    var ctx = new DataClasses1DataContext();
    using (IMultipleResults sprocresults = ctx.SP_myMutiResultStoredProc(main_id, null, null, null, null, "", "", paramField3, null, "", null, "", "", "", null, null))
    {
  List<type_F> typF = sprocresults.GetResult<type_F>().ToList();
  List<type_P> typP = sprocresults.GetResult<type_P>().ToList();
 }

//And I got 2 classes for the different result:
    public class type_F
    {
        public int Field1 { get; set; }
        public string Field2 { get; set; }
        public string Field3 { get; set; }
    }

    public class type_P
    {
        public int Field1 { get; set; }
        public string Field2 { get; set; }
        public int Field3 { get; set; }
        public string Field4 { get; set; }
        public string Field5 { get; set; }
        public string Field6 { get; set; }
        public string Field7 { get; set; }
        public string Field8 { get; set; }
        public int Field9 { get; set; }
        public int Field10 { get; set; }
        public int Field11 { get; set; }
       
    }

 

---

ejo

Answer 1

Hi Erik,

 

Welcome to LINQ to SQL forum!

 

If I understand your scenario correctly, such a stored procedure to return different number of columns is supported in LINQ to SQL.  The exception should be thrown at the database side instead of at the LINQ to SQL client side.  Could you please provide us with the detailed information about the stored procedure and the SqlException message? 

 

I have perform a simple test to verify such a scenario is supported:

 

Stored procedure at the database side:

===========================================================================
PROCEDURE [dbo].[GetDiffNumOfColumns]

      -- Add the parameters for the stored procedure here

      @isType int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

 

    -- Insert statements for procedure here

      IF (@isType = 1)

      SELECT [T].ID, [T].Contract FROM [MYTABLE] AS [T]

      ELSE

      SELECT [T].GUID FROM GUIDTest AS [T]

END
===========================================================================

 

The stored procedure GetDiffNumOfColumns returns columns ID (int), Contract(nvarchar) if the passed in parameter isType equals 1, and returns column GUID (uniqueindentifier) if the parameter isType is other values. 

 

 

At the LINQ to SQL client side, I have such a custom stored procedure call:

===========================================================================
    public partial class DataClasses1DataContext

    {

        [Function(Name = "dbo.GetDiffNumOfColumns")]

        [ResultType(typeof(Type1))]

        [ResultType(typeof(Type2))]

        public IMultipleResults GetDiffNumOfColumns([Parameter(DbType = "Int")] System.Nullable<int> isType)

        {

            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), isType);

            return ((IMultipleResults)(result.ReturnValue));

        }

    }

 

    public class Type1

    {

        public int ID { get; set; }

        public string Contract { get; set; }

    }

 

    public class Type2

    {

        public Guid GUID { get; set; }

}
===========================================================================

Testing Code Snippet ONE:

===========================================================================
            using (DataClasses1DataContext db = new DataClasses1DataContext())

            {

                var list1 = db.GetDiffNumOfColumns(1).GetResult<Type1>().ToList();
                var list2 = db.GetDiffNumOfColumns(1).GetResult<Type2>().ToList();

            }
===========================================================================
The variable list1 contains the data columns ID and Contract, while the list2 contains the same number of list1 data, but the GUID property is the default value {00000000-0000-0000-0000-000000000000}

 

Testing Code Snippet TWO:

===========================================================================
            using (DataClasses1DataContext db = new DataClasses1DataContext())

            {

                var list1 = db.GetDiffNumOfColumns(2).GetResult<Type1>().ToList();
                var list2 = db.GetDiffNumOfColumns(2).GetResult<Type2>().ToList();

            }
===========================================================================
The variable list1 contains the data columns ID (default value 0) and Contract (default value null), while the list2 contains the GUID values returned from the database side. 

 

 

If you have any questions, please feel free to let me know.

 

Have a great day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi Lingzhi, and thanks for your time.
Yes, of course you are right, this specific problem was due to some error in the stored Proc. BUT, now when this is fixed, I got some other strange results. Let me explain: If I provide a parameter 'F' in the field 'paramField3', then I expext an answer with 3 columns that fits in the class type_F, and I do get them! The result is the same numbers of rows as I got in the query analyzer. The problem is that 2 of the fields don't have any value. The field 'Field2' got the right value, but Field1 is zero, and Field3 is null. All the 3 column are named.

Yet another problem is when I try the other call, with an 'P' in 'paramField3', I expect 11 colums in return, but in the "List<type_P> typP = sprocresults.GetResult<type_P>().ToList();"-command I got a: "System.InvalidCastException: Specified cast is not valid..." I guess this problem nr 2 is quite difficult for someone that don't have my tables to figure out where the problem is. But I have typed every variable in the type_P class exacltly as they are in the database, with nullable and everything.... frustrating. If someone has any time to look at it, I could try to mail something more to reproduce the problem.

Btw, is there any good way to examine the actual sql-command here?

---

ejo

Answer 3

Hi Erik,

 

If it is convenient for you, you can send me the demo or detailed information by mail.  My mail address: v-micsun @ microsoft.com.  

 

Have a nice day!

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Hi Erik,

 

Thank you for sending me the demo project and db script.  I added some data into the three data table and I do repro the problem as you described in the mail. 

================================================================
FT01_Foretag

 

FT01Foretag_ID	FT01Namn	FT01Adress
1	FN1	FA1

 

PE01_Person

 

PE01Person_ID	PE01Personnr	PE01Fornamn	PE01Efternamn
1	NULL	PF1	PE1

 

PE02_Anstallning

 

PE02Anst_ID	FT01Foretag_ID
1	1

================================================================

 

After debugging the program, I found the cause of the problem is the incorrect property names among the LINQ to SQL entities.  LINQ to SQL maps the return columns to the entities’ property names.   If no property with the same name of the return columns, the mapping fails and no data is returned.  I modified the entities property names to fit the return column names and the problem is resolved.  

================================================================
    public class SP_XBEHL002Result_sBegreppstyp_F

    {

        //public int FT01Foretag_ID_field { get; set; }

        //public string FT01Namn_field { get; set; }

        //public string FT01ForetagsAdress { get; set; }

 

        public int FT01Foretag_ID { get; set; }

        public string FT01Namn { get; set; }

        public string FT01Adress { get; set; }

    }

 

    public class SP_XBEHL002Result_sBegreppstyp_P

    {

        //public int? PE02Anst_ID_field { get; set; }

        //public string FT01Namn_field { get; set; }

        //public int? FT01Foretag_ID_field { get; set; }

        //public string PE01Fornamn_field { get; set; }

        //public string PE01EfternamnFornamn_field { get; set; }

 

        public int? PE02Anst_ID { get; set; }

        public string FT01Namn { get; set; }

        public int? FT01Foretag_ID { get; set; }

        public string PE01Fornamn { get; set; }

        public string PE01Efternamn { get; set; }

    }
================================================================   

 

Or we can define the column name of the property, if we want to rename the property. 

================================================================
[Column(Name="FT01Foretag_ID")]

public int FT01Foretag_ID_field { get; set; }
================================================================

 

Please have a test.  If you have any questions, please feel free to let me know.

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 5

Aha.... So the name in the class must fit exactly to the column-name. That was quite unexpexted! Thanks for you spending this time, you saved me a lots of hours here. Now it works like a charm.

/Erik

---

ejo

Answer 6

It's my please!  :)

Have a nice weekend!
 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.