Last post Jan 20, 2017 03:01 AM by Zhi Lv - MSFT
Jan 19, 2017 04:37 PM|bchernick|LINK
At this point I am not sure whether I have a stored proc problem or a LinqToSQL problem. I have a Web app with a LinqToSql dbml that is calling a stored proc. The purpose of the proc is to return a single integer value, a count of records.
The problem may be that the query needed is so complex that I see no way to implement it other than to set up a string and do an Exec. Unfortunately the LinqToSql is apparently receiving the 0 return value, not the actual value of the count I need. (FYI:
This is SQL Server 2008)
If for example I set up a string that has 'select count(*) as x...' and Exec it, a direct execution of the stored proc from SSMS returns both the return value and x. However if I add the stored proc to my DBML and call it in the usual manner (int x = DbContext.spName(parameter
list) etc) I get back 0. All my attempts to create and assign an output variable using the Exec string have failed so far. (Admittedly I have not tried something like this before.)
I am somewhat ashamed to admit that I came up with what appears to be a serviceable workaround after lunch. Declare a table with a single int field, use the exec string to populate the table with a single value, and then do a select * from the table.
Then, when doing the LinqToSQL call, do something like this:
int i = (int) DbContext.spCall(the usual list of parameters).FirstOrDefault().Count;
If there's a simpler way to do this, please chime in.
Jan 20, 2017 03:01 AM|Zhi Lv - MSFT|LINK
I suggest you could refer to the following steps to call store procedure using LINQ to SQL:
Code in SQL:
create procedure sp_GetTable3Count
select count(*) As [RowCount] from Table3
'Store procedure with output parameters
create procedure sp_GetTable3Count2
@RowCount int output
select @RowCount = count(*) from Table3
'you could execute the store procedure, and make sure the store procedure working.
declare @count int;
exec sp_GetTable3Count2 @RowCount = @count output;
Then, drag the store procedure into the dbml:
Then in the web page, you could use the following code to call the store procedure:
using (DataClasses1DataContext db = new DataClasses1DataContext())
var query = db.sp_GetTable3Count().FirstOrDefault();
int? count =0;
var query2 = db.sp_GetTable3Count2(ref count);