Call SP in LINQ

scropio gurl

10y958LINQ

I try to call storerprocedure in LINQ but this show error

i try this sp

ALTER procedure [dbo].[grid_data]
@region varchar(50),
@fromdate datetime,
@todate datetime
as
Select tblRV.ID as ID, tblRV.Owner, tblRV.Regno,
(Select Count(*) as total from tblvv WHERE MID = tblRV.ID and Name <> '')
as total,tblRV.MA, tblRV.MS
from tblReg inner join tblRV On tblReg.RID = tblRV.RID
WHERE tblReg.StartDate >= @fromdate AND tblReg.EndDate <= @todate
and tblReg.Region = @region
order by tblRV.Owner

and code

[WebMethod]
public static string search_data(DateTime fromdate, DateTime todate, string region)
{
try
{
string result = "";
TrackDataEntities1 ts = new TrackDataEntities1();
var dq = ts.griddata(fromdate, todate, region);
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Owner", typeof(string));
dt.Columns.Add("RegNo", typeof(string));
dt.Columns.Add("total", typeof(string));
dt.Columns.Add("MA", typeof(string));
dt.Columns.Add("MS", typeof(string));
foreach (var c in dq)
{
dt.Rows.Add(c.ID, c.owner, c.RegNo, c.total, c.MA, c.MS);
}
result = DataSetToJSON(dt);
return result;
}
catch (Exception)
{
throw new Exception();
}
}
public static string DataSetToJSON(DataTable dt)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
object[] arr = new object[dt.Rows.Count + 1];
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
arr[i] = dt.Rows[i].ItemArray;
}
dict.Add("response", arr);
JavaScriptSerializer json = new JavaScriptSerializer();
return json.Serialize(dict);
}

but this show error on this line

foreach (var c in dq)

foreach statement cannot operate on variables of type 'int' because 'int' does not contain a public definition for 'GetEnumerator'

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

scropio gurl10y ago
@Bikesh Srivastava
now i try this
[WebMethod]
publicstaticstringsearch_data(DateTimefromdate,DateTimetodate,stringregion)
{
try
{
stringresult="";
TrackDataEntities1td=newTrackDataEntities1();
Listdq=new
List();
dq=td.griddataresult(fromdate,todate,region).ToList();
DataTabledt=newDataTable();
dt.Columns.Add("ID",typeof(int));
dt.Columns.Add("Owner",typeof(string));
foreach(varcindq)
{
dt.Rows.Add(c.ID,c.owner);
}
result=DataSetToJSON(dt);
returnresult;
}
catch(Exception)
{
thrownewException();
}
}
publicstaticstringDataSetToJSON(DataTabledt)
{
Dictionary<string,object>dict=newDictionary<string,object>();
object[]arr=newobject[dt.Rows.Count+1];
for(inti=0;i<=dt.Rows.Count-1;i++)
{
arr[i]=dt.Rows[i].ItemArray;
}
dict.Add("response",arr);
JavaScriptSerializerjson=newJavaScriptSerializer();
returnjson.Serialize(dict);
}
check image http://i.stack.imgur.com/w0gpP.png
my sp return return 42 rows but when i run this code show me 577 row in table.. whereas there is not any error
scropio gurl10y ago
@
Bikesh Srivastava
but all fields are in this classTrackDataEntities1
Manas Mohapatra10y ago
Your griddata() returns value as integer. And then it is trying to iterate over int which is not possible. To resolve the problem you need to check following step:

Make sure that you configured SP properly means returns type as complex. Follow below link that may help you to add SP in EF:

http://weblogs.asp.net/scottgu/linq-to-sql-part-6-retrieving-data-using-stored-procedures
Bikesh Srivastava10y ago
Hi gurl,
why you are dbcontext class "TrackDataEntities1"
you should create new class as model.
scropio gurl10y ago
@
Bikesh Srivastava
when i use this
List dq = ts.griddata(fromdate, todate, region).tolist();
this show so many error
Error 13 'int' does not contain a definition for 'tolist' and no extension method 'tolist' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)
Error 14 'chart_project.TrackDataEntities1' does not contain a definition for 'ID' and no extension method 'ID' accepting a first argument of type 'chart_project.TrackDataEntities1' could be found (are you missing a using directive or an assembly reference?)
Error 15 'chart_project.TrackDataEntities1' does not contain a definition for 'owner' and no extension method 'owner' accepting a first argument of type 'chart_project.TrackDataEntities1' could be found (are you missing a using directive or an assembly reference?)
Error 16 'chart_project.TrackDataEntities1' does not contain a definition for 'RegNo' and no extension method 'RegNo' accepting a first argument of type 'chart_project.TrackDataEntities1' could be found (are you missing a using directive or an assembly reference?)
Error 17 'chart_project.TrackDataEntities1' does not contain a definition for 'total' and no extension method 'total' accepting a first argument of type 'chart_project.TrackDataEntities1' could be found (are you missing a using directive or an assembly reference?)
Error 18 'chart_project.TrackDataEntities1' does not contain a definition for 'MA' and no extension method 'MA' accepting a first argument of type 'chart_project.TrackDataEntities1' could be found (are you missing a using directive or an assembly reference?)
Error 19 'chart_project.TrackDataEntities1' does not contain a definition for 'MS' and no extension method 'MS' accepting a first argument of type 'chart_project.TrackDataEntities1' could be found (are you missing a using directive or an assembly reference?)
Bikesh Srivastava10y ago
Hi,
actually you are getting data in var using foreach loop its not good.
create a class with properties which is defined you field name.
try
{
stringresult="";
TrackDataEntities1ts=newTrackDataEntities1();
List dq=ts.griddata(fromdate,todate,region).tolist();
DataTabledt=newDataTable();
dt.Columns.Add("ID",typeof(int));
dt.Columns.Add("Owner",typeof(string));
dt.Columns.Add("RegNo",typeof(string));
dt.Columns.Add("total",typeof(string));
dt.Columns.Add("MA",typeof(string));
dt.Columns.Add("MS",typeof(string));
foreach(varcindq)
{
dt.Rows.Add(c.ID,c.owner,c.RegNo,c.total,c.MA,c.MS);
}
result=DataSetToJSON(dt);
returnresult;
}