Why .EDMX file does not update a Stored procedure?

Abhilash J A

Hello Everyone,

1) After updating .EDMX fill in mvc4 by the way Entity Framework, this procedure name not getting like Model.SP_FinalExposureMonitor_Final_Result, model class. Why this?

2) After executing this procedure I want list of items, but now db.SP_FinalExposureMonitor(); return type is int

How can I return this procedure with list items?

CREATE PROCEDURE SP_FinalExposureMonitor_Final
@SortColumn varchar(100)
AS
BEGIN
declare @dynamicSQL nvarchar(max) = '';
select @dynamicSQL = 'select tempID,ProgramId,ChannelId,convert(date,Date_DL)asDate_DL,StartTime,EndTime,Viewers,
Tx,TVR,CVR,DVR,
Details_Id,
MastersId,BrandsId,ItemsId,Note,PaidForOrProp,Verbal,Indexes,Seconds,
GradesId,ChannelName,CountryName,WebSiteName,ProductionName,BrandName,ItemName,
Product_company_id,CompanyName,ProductGroupsId,
SectorsId,SubSectoresId,AgenciesId,GroupName,SectorName,SubsectoreName,
AgencyName,agencyID,QualityGrade,CPH_DL,CPH_UN,Country_CPH,Country_UN,
Web_CPH,Web_UN,
AdFactor,Duration_Factor,DaypartName,DaypartId,DaypartFactor,CinemaFactor,DigitalFactor,MediaValue,ROW_NUMBER() over (order by '+ @SortColumn +')AS RowNum from MonitorExposureFinal2;'
execute sp_executeSQL @dynamicSQL
print @dynamicSQL
END

11 Replies

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

Ramesh Palaniappan10y ago
Accepted answer
Hi Abhilash,
Dont change the code which is generated by EDMX update. Call the stored procedure function from another function. Using the result returned by function, parse the value in foreach loop as below.
foreach(SP_FinalExposureMonitor_Final_ResultvalueinreturnValue)
{
varstrTempID=value.tempID;
}
Esakki Muthu10y ago
Hi Abhilash,
If you possible and your confident enough just remove your .edmx file and add again , it will bring all your tables, procedures and views (any how u only going to add them which and all you need while adding the new edmx file) , while doing this entity name might change in the .cs file of edmx (system generated code), so just manually change that code into your old entity name other wise you will get build errors.
Let me know is this possible for you.
Abhilash J A10y ago
Hello Remesh,
I have writtern the code like this. But it shown error. How can I impliement Sqldatareader ?
publicListFinalMonitorList(stringcolname)
{
ListmonitorExposure=newList();
intquery=db.SP_FinalExposureMonitor_Final(colname);
SqlCommandcmd=newSqlCommand(query);
SqlDataReaderreader=cmd.ExecuteReader();
while(reader.HasRows)
{
while(reader.Read())
{
for(inti=0;i
{
if(reader.GetName(i).ToLower()=="tempID")
{
vartempID=Convert.ToString(reader.GetValue(i));
}
elseif(reader.GetName(i).ToLower()=="ProgramId")
{
varProgramId=Convert.ToString(reader.GetValue(i));
}
elseif(reader.GetName(i).ToLower()=="ChannelId")
{
varChannelId=Convert.ToString(reader.GetValue(i));
}
}
}
reader.NextResult();
}
monitorExposure=/*HowcanIassignprocedureresulttomonitorExposure?*/
returnmonitorExposure;
}
Ramesh Palaniappan10y ago
Hi Abhilash,
Dont change int to string in the function manually. If you properly write the stored procedure, the edmx will generate like below with proper return object.
public virtual ObjectResult SP_FinalExposureMonitor_Final(stringsortColumn)
{
varsortColumnParameter=sortColumn!=null?
newObjectParameter("SortColumn",sortColumn):
newObjectParameter("SortColumn",typeof(string));
return((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("GeExposureData",sortColumnParameter);
}
Abhilash J A10y ago
Hello Ramesh,
Thank for replay me.
Here I am using entity framework with MVC4. After adding .edmx file I have tried with your code like this.
Model.EMG_DBEntities db=new Model.EMG_DBEntities();
string query=db.SP_FinalExposureMonitor_Final(colname).ToString();
SqlCommand cmd=newSqlCommand(query);
SqlDataReader reader=cmd.ExecuteReader();
But, it is not working.
Here colname ='agencyname asc'
colname variable data passing like this. How can I ?

And db.SP_FinalExposureMonitor_Final is int type in dbcontext class. So I have convert the db.SP_FinalExposureMonitor_Final to string in above code. Here is dbcontext
public virtual int SP_FinalExposureMonitor_Final(string sortColumn)
       {
           var sortColumnParameter = sortColumn != null ?
               new ObjectParameter("SortColumn", sortColumn) :
               new ObjectParameter("SortColumn", typeof(string));

           return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("GeExposureData", sortColumnParameter);
       }
Ramesh Palaniappan10y ago
http://www.c-sharpcorner.com/article/create-and-update-an-edmx-file-using-entity-framework-data-model-in-visual-stud/
Ramesh Palaniappan10y ago
Hi Abhilash,
Just selected values in stored procedure. Get the values in C# as below.
SqlDataReaderreader=command.ExecuteReader();
while(reader.HasRows)
{
while(reader.Read())
{
for(inti=0;i
{
if(reader.GetName(i).ToLower()=="tempID")
{
vartempID=Convert.ToString(reader.GetValue(i));
}
elseif(reader.GetName(i).ToLower()=="ProgramId")
{
varProgramId=Convert.ToString(reader.GetValue(i));
}
elseif(reader.GetName(i).ToLower()=="ChannelId")
{
varChannelId=Convert.ToString(reader.GetValue(i));
}
}
}
reader.NextResult();
}
Above code will return all the result items. I this this will helps you.
Manas Mohapatra10y ago
If you are returning multiple records follow below steps:

Go to EntityFramework designer -> Right click on the StoredProcedure
-> Function Import -> In the window you see return type of SP
-> Choose Entities or Complex(if you have entities then choose entity otherwise choose complex)

Follow below link:

http://www.entityframeworktutorial.net/data-read-using-stored-procedure.aspx

http://www.codeproject.com/Articles/675933/Returning-Multiple-Result-Sets-from-an-Entity-Fram
Salman Beg10y ago
Hi.
You are not returning any kind of int value from Stored Procedure. But rather in @dynamicSQL you are returning lots of code. So in your code behind create a list and loop through each code to add in it and atlast you can get a list of items.
Thanks.
Abhilash J A10y ago
HelloBikesh Srivastava,
Very very thank you for replay me.
That procedure, want to return list item. But now its return type is Int. How can I get list of details?
Bikesh Srivastava10y ago
Follow these steps.
1=>Open ORM page .edmx page
2=>right click any where
3=>update or refresh
Ctrl+S
It will update your all entity.
Mark as answer if find your solution