Why .EDMX file does not update a Stored procedure?

Question

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 tempIDProgramIdChannelId convert ( date Date_DL)asDate_DLStartTimeEndTimeViewers TxTVRCVRDVR Details_Id MastersIdBrandsIdItemsIdNotePaidForOrPropVerbalIndexesSeconds GradesIdChannelNameCountryNameWebSiteNameProductionNameBrandNameItemName Product_company_idCompanyNameProductGroupsId SectorsIdSubSectoresIdAgenciesIdGroupNameSectorNameSubsectoreName AgencyNameagencyIDQualityGradeCPH_DLCPH_UNCountry_CPHCountry_UN Web_CPHWeb_UN AdFactorDuration_FactorDaypartNameDaypartIdDaypartFactorCinemaFactorDigitalFactorMediaValueROW_NUMBER() over ( order by '+ @SortColumn +' ) AS RowNum from MonitorExposureFinal2;' execute sp_executeSQL @dynamicSQL print @dynamicSQL END

Ramesh Palaniappan · 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_Resultvalue in returnValue) { varstrTempID=value.tempID; }

Ramesh Palaniappan · Answer

http://www.c-sharpcorner.com/article/create-and-update-an-edmx-file-using-entity-framework-data-model-in-visual-stud/

Ramesh Palaniappan · Answer

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 ( int i=0;i { if (reader.GetName(i).ToLower()== tempID ) { vartempID=Convert.ToString(reader.GetValue(i)); } else if (reader.GetName(i).ToLower()== ProgramId ) { varProgramId=Convert.ToString(reader.GetValue(i)); } else if (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.

Esakki Muthu · Answer

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.

Mani Kandan · Answer

Hello Remesh I have writtern the code like this. But it shown error. How can I impliement Sqldatareader ? public List FinalMonitorList( string colname) { List monitorExposure= new List (); int query=db.SP_FinalExposureMonitor_Final(colname); SqlCommandcmd= new SqlCommand(query); SqlDataReaderreader=cmd.ExecuteReader(); while (reader.HasRows) { while (reader.Read()) { for ( int i=0;i { if (reader.GetName(i).ToLower()== tempID ) { vartempID=Convert.ToString(reader.GetValue(i)); } else if (reader.GetName(i).ToLower()== ProgramId ) { varProgramId=Convert.ToString(reader.GetValue(i)); } else if (reader.GetName(i).ToLower()== ChannelId ) { varChannelId=Convert.ToString(reader.GetValue(i)); } } } reader.NextResult(); } monitorExposure= /*HowcanIassignprocedureresulttomonitorExposure?*/ return monitorExposure; }

Ramesh Palaniappan · Answer

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( string sortColumn) { varsortColumnParameter=sortColumn!= null ? new ObjectParameter( SortColumn sortColumn): new ObjectParameter( SortColumn  typeof ( string )); return ((IObjectContextAdapter) this ).ObjectContext.ExecuteFunction ( GeExposureData sortColumnParameter); }

Mani Kandan · Answer

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= new SqlCommand(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); }

Manas Mohapatra · Answer

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 · Answer

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.

Mani Kandan · Answer

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 Srivastava · Answer

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