Problem in StoredProcedure for LINQ
-
Hi all, I wrote a StoredProcedure which returns a dataset. But when i drag that SP into .dbml file, the return type of SP is becomes int, not ISingleResultType<>. I dont know reason for this. When i call that SP i got Error / Return vale 0 Plz Help me. Here my SP: ALTER PROCEDURE [dbo].[USP_GetAdNames]( @user varchar(50), @specialization smallint = null, @country smallint = null, @state tinyint = null, @city varchar(50)= null, @from datetime = null, @to datetime = null ) AS CREATE TABLE #TEMPADS ( int_ID int, str_Name varchar(50) ) DECLARE @query nvarchar(1000) BEGIN SET @query = N'INSERT INTO #TEMPADS SELECT transAds.int_AdID,transAds.str_Name FROM TRANS_ADS AS transAds WHERE transAds.str_UserID = '''+@user+'''' if (@specialization != null OR @specialization != 0) SET @query = @query + ' AND tansAds.smallint_Specialization = '+cast(@specialization as varchar(10)) if (@country != null OR @country != 0) SET @query = @query + ' AND transAds.smallint_CountryCode = '+cast(@country as varchar(10)) if(@state != null OR @state != 0) SET @query = @query + ' AND transAds.tinyint_FocusState = '+cast(@state as varchar(10)) if(Ltrim(Rtrim(@city)) != '' OR Ltrim(Rtrim(@city)) != null) SET @query = @query + ' AND transAds.str_FocusCity = '''+@city +'''' if(@from IS NOT NULL) SET @query = @query + ' AND transAds.dt_Modifiedon >='''+convert(varchar(25),@from,101)+'''' if(@to IS NOT NULL) SET @query = @query + ' AND transAds.dt_Modifiedon <= '''+convert(varchar(25),@to,101)+'''' print @query EXECUTE sp_sqlexec @query SELECT int_ID,str_Name FROM #TEMPADS END ///// When i execute in SQl Management Studio iam getting records as dataset. But not in my program. I am calling that sp like this: var query = from temp in DataContext1.USP_GetAdNames("kvs",null,null,null,null,null,null) select temp; ..................................................... I also tried creating a partial class in DataContextMapping.Designer.cs file but no use. Please suggest me, what went wrong in my SP.
-
Hi all, I wrote a StoredProcedure which returns a dataset. But when i drag that SP into .dbml file, the return type of SP is becomes int, not ISingleResultType<>. I dont know reason for this. When i call that SP i got Error / Return vale 0 Plz Help me. Here my SP: ALTER PROCEDURE [dbo].[USP_GetAdNames]( @user varchar(50), @specialization smallint = null, @country smallint = null, @state tinyint = null, @city varchar(50)= null, @from datetime = null, @to datetime = null ) AS CREATE TABLE #TEMPADS ( int_ID int, str_Name varchar(50) ) DECLARE @query nvarchar(1000) BEGIN SET @query = N'INSERT INTO #TEMPADS SELECT transAds.int_AdID,transAds.str_Name FROM TRANS_ADS AS transAds WHERE transAds.str_UserID = '''+@user+'''' if (@specialization != null OR @specialization != 0) SET @query = @query + ' AND tansAds.smallint_Specialization = '+cast(@specialization as varchar(10)) if (@country != null OR @country != 0) SET @query = @query + ' AND transAds.smallint_CountryCode = '+cast(@country as varchar(10)) if(@state != null OR @state != 0) SET @query = @query + ' AND transAds.tinyint_FocusState = '+cast(@state as varchar(10)) if(Ltrim(Rtrim(@city)) != '' OR Ltrim(Rtrim(@city)) != null) SET @query = @query + ' AND transAds.str_FocusCity = '''+@city +'''' if(@from IS NOT NULL) SET @query = @query + ' AND transAds.dt_Modifiedon >='''+convert(varchar(25),@from,101)+'''' if(@to IS NOT NULL) SET @query = @query + ' AND transAds.dt_Modifiedon <= '''+convert(varchar(25),@to,101)+'''' print @query EXECUTE sp_sqlexec @query SELECT int_ID,str_Name FROM #TEMPADS END ///// When i execute in SQl Management Studio iam getting records as dataset. But not in my program. I am calling that sp like this: var query = from temp in DataContext1.USP_GetAdNames("kvs",null,null,null,null,null,null) select temp; ..................................................... I also tried creating a partial class in DataContextMapping.Designer.cs file but no use. Please suggest me, what went wrong in my SP.
The problem here is that you are doing a SELECT from a temporary table. LINQ to SQL's sqlmetal tool can made a good stab at determining the resultset if it represents a select from existing database objects - otherwise it really cannot determine the result. In this instance you probably need to define your own result type and modify the proc in your dbml file to state the "Return Type" of the stored procedure manually.
'Howard
-
The problem here is that you are doing a SELECT from a temporary table. LINQ to SQL's sqlmetal tool can made a good stab at determining the resultset if it represents a select from existing database objects - otherwise it really cannot determine the result. In this instance you probably need to define your own result type and modify the proc in your dbml file to state the "Return Type" of the stored procedure manually.
'Howard
Thanks Howard, I tried that also by modifying .dbml file. But i am to get only int_ID but not str_Name (it is showing null). Here is My hardcoded .dbml file: [Function(Name="dbo.USP_GetAdNames")] public ISingleResult<USP_GetAdNamesResult> USP_GetAdNames([Parameter(DbType="VarChar(50)")] string user, [Parameter(DbType="SmallInt")] System.Nullable<short> specialization, [Parameter(DbType="SmallInt")] System.Nullable<short> country, [Parameter(DbType="TinyInt")] System.Nullable<byte> state, [Parameter(DbType="VarChar(50)")] string city, [Parameter(DbType="DateTime")] System.Nullable<System.DateTime> from, [Parameter(DbType="DateTime")] System.Nullable<System.DateTime> to) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), user, specialization, country, state, city, from, to); return ((ISingleResult<USP_GetAdNamesResult>)(result.ReturnValue)); } //////////////////////// public partial class USP_GetAdNamesResult { private int _int_ID; private string _str_Name; public USP_GetAdNamesResult() { } [Column(Storage="_int_ID", DbType="Int NOT NULL")] public int int_ID { get { return this._int_ID; } set { if ((this._int_ID != value)) { this._int_ID = value; } } } [Column(Storage="_str_Name", DbType="VarChar(255)")] public string str_Name { get { return this._str_Name; } set { if ((this._str_Name != value)) { this._str_Name = value; } } } } /////////////////////// Ads.aspx.cs: ========== PageLoad() { if(!Page.IsPostBack) { var query = from temp in datacontext1.USP_GetNames("kvs",null,null,null,null,null,null) select temp; // Here I am Getting Records Gridview1.DataSource = query; // Nothing getting here, Message : The query result can't be enumerated morethan once Gridvie1.DataBind(); } } Plz suggest where i went wrong in above code.
modified on Monday, August 18, 2008 6:53 AM
-
Thanks Howard, I tried that also by modifying .dbml file. But i am to get only int_ID but not str_Name (it is showing null). Here is My hardcoded .dbml file: [Function(Name="dbo.USP_GetAdNames")] public ISingleResult<USP_GetAdNamesResult> USP_GetAdNames([Parameter(DbType="VarChar(50)")] string user, [Parameter(DbType="SmallInt")] System.Nullable<short> specialization, [Parameter(DbType="SmallInt")] System.Nullable<short> country, [Parameter(DbType="TinyInt")] System.Nullable<byte> state, [Parameter(DbType="VarChar(50)")] string city, [Parameter(DbType="DateTime")] System.Nullable<System.DateTime> from, [Parameter(DbType="DateTime")] System.Nullable<System.DateTime> to) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), user, specialization, country, state, city, from, to); return ((ISingleResult<USP_GetAdNamesResult>)(result.ReturnValue)); } //////////////////////// public partial class USP_GetAdNamesResult { private int _int_ID; private string _str_Name; public USP_GetAdNamesResult() { } [Column(Storage="_int_ID", DbType="Int NOT NULL")] public int int_ID { get { return this._int_ID; } set { if ((this._int_ID != value)) { this._int_ID = value; } } } [Column(Storage="_str_Name", DbType="VarChar(255)")] public string str_Name { get { return this._str_Name; } set { if ((this._str_Name != value)) { this._str_Name = value; } } } } /////////////////////// Ads.aspx.cs: ========== PageLoad() { if(!Page.IsPostBack) { var query = from temp in datacontext1.USP_GetNames("kvs",null,null,null,null,null,null) select temp; // Here I am Getting Records Gridview1.DataSource = query; // Nothing getting here, Message : The query result can't be enumerated morethan once Gridvie1.DataBind(); } } Plz suggest where i went wrong in above code.
modified on Monday, August 18, 2008 6:53 AM
You might also want to set NOCOUNT so that SQL is not reporting the rows updated - that could the other cause.
'Howard
-
Hi all, I wrote a StoredProcedure which returns a dataset. But when i drag that SP into .dbml file, the return type of SP is becomes int, not ISingleResultType<>. I dont know reason for this. When i call that SP i got Error / Return vale 0 Plz Help me. Here my SP: ALTER PROCEDURE [dbo].[USP_GetAdNames]( @user varchar(50), @specialization smallint = null, @country smallint = null, @state tinyint = null, @city varchar(50)= null, @from datetime = null, @to datetime = null ) AS CREATE TABLE #TEMPADS ( int_ID int, str_Name varchar(50) ) DECLARE @query nvarchar(1000) BEGIN SET @query = N'INSERT INTO #TEMPADS SELECT transAds.int_AdID,transAds.str_Name FROM TRANS_ADS AS transAds WHERE transAds.str_UserID = '''+@user+'''' if (@specialization != null OR @specialization != 0) SET @query = @query + ' AND tansAds.smallint_Specialization = '+cast(@specialization as varchar(10)) if (@country != null OR @country != 0) SET @query = @query + ' AND transAds.smallint_CountryCode = '+cast(@country as varchar(10)) if(@state != null OR @state != 0) SET @query = @query + ' AND transAds.tinyint_FocusState = '+cast(@state as varchar(10)) if(Ltrim(Rtrim(@city)) != '' OR Ltrim(Rtrim(@city)) != null) SET @query = @query + ' AND transAds.str_FocusCity = '''+@city +'''' if(@from IS NOT NULL) SET @query = @query + ' AND transAds.dt_Modifiedon >='''+convert(varchar(25),@from,101)+'''' if(@to IS NOT NULL) SET @query = @query + ' AND transAds.dt_Modifiedon <= '''+convert(varchar(25),@to,101)+'''' print @query EXECUTE sp_sqlexec @query SELECT int_ID,str_Name FROM #TEMPADS END ///// When i execute in SQl Management Studio iam getting records as dataset. But not in my program. I am calling that sp like this: var query = from temp in DataContext1.USP_GetAdNames("kvs",null,null,null,null,null,null) select temp; ..................................................... I also tried creating a partial class in DataContextMapping.Designer.cs file but no use. Please suggest me, what went wrong in my SP.
Just had a look at this as someone else is having problems with SPs. Can you post the XML contents of the DBML (the datacontext file) for USP_GetAdNames - that might help determine if SQLmetal has created the right template.
'Howard
-
You might also want to set NOCOUNT so that SQL is not reporting the rows updated - that could the other cause.
'Howard
Howard Richards wrote:
You might also want to set NOCOUNT so that SQL is not reporting the rows updated - that could the other cause.
I'd just add that this does not seem to make any difference when I just tested it, so NOCOUNT ON is not the cause of your problem.
'Howard
-
Thanks Howard, I tried that also by modifying .dbml file. But i am to get only int_ID but not str_Name (it is showing null). Here is My hardcoded .dbml file: [Function(Name="dbo.USP_GetAdNames")] public ISingleResult<USP_GetAdNamesResult> USP_GetAdNames([Parameter(DbType="VarChar(50)")] string user, [Parameter(DbType="SmallInt")] System.Nullable<short> specialization, [Parameter(DbType="SmallInt")] System.Nullable<short> country, [Parameter(DbType="TinyInt")] System.Nullable<byte> state, [Parameter(DbType="VarChar(50)")] string city, [Parameter(DbType="DateTime")] System.Nullable<System.DateTime> from, [Parameter(DbType="DateTime")] System.Nullable<System.DateTime> to) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), user, specialization, country, state, city, from, to); return ((ISingleResult<USP_GetAdNamesResult>)(result.ReturnValue)); } //////////////////////// public partial class USP_GetAdNamesResult { private int _int_ID; private string _str_Name; public USP_GetAdNamesResult() { } [Column(Storage="_int_ID", DbType="Int NOT NULL")] public int int_ID { get { return this._int_ID; } set { if ((this._int_ID != value)) { this._int_ID = value; } } } [Column(Storage="_str_Name", DbType="VarChar(255)")] public string str_Name { get { return this._str_Name; } set { if ((this._str_Name != value)) { this._str_Name = value; } } } } /////////////////////// Ads.aspx.cs: ========== PageLoad() { if(!Page.IsPostBack) { var query = from temp in datacontext1.USP_GetNames("kvs",null,null,null,null,null,null) select temp; // Here I am Getting Records Gridview1.DataSource = query; // Nothing getting here, Message : The query result can't be enumerated morethan once Gridvie1.DataBind(); } } Plz suggest where i went wrong in above code.
modified on Monday, August 18, 2008 6:53 AM
OK the problem is pretty simple: LINQ Stored Procedures can return single values, a query or multiple queries. When resultsets are retured LINQ to SQL uses a System.Data.Linq.ISingleResult to hold the outcome, not just a collection of your elements. Try this instead
var resultset = DataContext1.USP_GetAdNames("kvs",null,null,null,null,null,null);
var query = resultset.GetResults<USP_GetAdNamesResult>();LINQ has to cope with situations where multiple results could be returned and so it does not just create a list of objects. See the next post in this forum by francoisdotnet which is very similar.
'Howard
-
Just had a look at this as someone else is having problems with SPs. Can you post the XML contents of the DBML (the datacontext file) for USP_GetAdNames - that might help determine if SQLmetal has created the right template.
'Howard
Hi Howards, Thanks for your reply. I posted .dbml file of USP_GetNames in above post as reply to your post. I did a changes in Ads.aspx.cs file. Now its working fine here is that code: Ads.aspx.cs: ========== PageLoad() { if(!Page.IsPostBack) { var query = datacontext1.USP_GetNames("kvs",null,null,null,null,null,null); //I changed here. (Not taken any temp var type variable.like 'var query = from temp in datacontext1.SP(..) select temp; Gridview1.DataSource = query.ToList(); // I changed here. (.ToList()) Gridvie1.DataBind(); } } //////////////// But if i try like this below, i am getting the same error: if(!Page.IsPostBack) { var query = datacontext1.USP_GetNames("kvs",null,null,null,null,null,null); if(query.Count()>0) //Here i am geeting the same error: The enumerated result can't be displayed morethan once { Gridview1.DataSource = query.ToList(); Gridvie1.DataBind(); } } Thanks for your help.
-
Hi Howards, Thanks for your reply. I posted .dbml file of USP_GetNames in above post as reply to your post. I did a changes in Ads.aspx.cs file. Now its working fine here is that code: Ads.aspx.cs: ========== PageLoad() { if(!Page.IsPostBack) { var query = datacontext1.USP_GetNames("kvs",null,null,null,null,null,null); //I changed here. (Not taken any temp var type variable.like 'var query = from temp in datacontext1.SP(..) select temp; Gridview1.DataSource = query.ToList(); // I changed here. (.ToList()) Gridvie1.DataBind(); } } //////////////// But if i try like this below, i am getting the same error: if(!Page.IsPostBack) { var query = datacontext1.USP_GetNames("kvs",null,null,null,null,null,null); if(query.Count()>0) //Here i am geeting the same error: The enumerated result can't be displayed morethan once { Gridview1.DataSource = query.ToList(); Gridvie1.DataBind(); } } Thanks for your help.