Scenario:
We recommended Entity Framework 3.5 to our client but client clarified that only use Stored Procedures for all Database calls.
So we ended up looking for "Entity Framework 3.5 & Stored Procedure", while there were lot of posts on the google to do that, they were all focused towards using Stored Procedure for Insert / Update / Delete using Stored procedures but all the select calls were still directly going to tables.
Quick SQL Profiler analysis revealed that.
Solution:
Though it was not documented , after going through auto-generated designer code for the model, we were able to achieve this with code modification. Also It was painful when tables are related. i.e. Country / State tables
Few important things:
0. I am assuming that you already know little about EF and understand the basic process of using Stored procedures as explained here
1. We will need 5 Stored Procedures per Table
GetAllCountries, GetSpecificCountry, InsertCountry, UpdateCountry, DeleteCountry
GetAllStatesByCountry, GetSpecificState, InsertState, UpdateState, DeleteState
2. Connect to DB using an account with full database permissions ( tables / views / stored procedures ). You can switch the user once we are done with the plumbing.
3. I am assuming all the stored procedures are Function mapped with the same name in the code.
4. Go head and create a model with both the tables ( country and state ) , assuming they are in relationship on CountryID column.
Code ( Country Table ) :
/// <summary>Code ( State Table ) :
/// Gets all Countries.
/// </summary>
/// <returns>List Of all Countries.</returns>
public IList<Country> GetAllCountries()
{
MyEntities client = new MyEntities();
try
{
return client.GetAllCountry().ToList();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Gets the Country .
/// </summary>
/// <param name="CountryID">The Country ID.</param>
/// <returns>Country by CountryId.</returns>
public Country GetCountry(int CountryID)
{
MyEntities client = new MyEntities();
try
{
return client.GetCountry(CountryID).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Creates the Country .
/// </summary>
/// <param name="Country">The Country .</param>
/// <returns>Country with Id.</returns>
public int CreateCountry(Country Country)
{
MyEntities client = new MyEntities();
try
{
client.AddToCountry(Country);
client.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
return Country.ID;
}
/// <summary>
/// Updates the Country .
/// </summary>
/// <param name="Country">The Country .</param>
/// <returns>Updated Country .</returns>
public int UpdateCountry(Country Country)
{
int affected = 0;
MyEntities client = new MyEntities();
try
{
Country CountrySource = client.GetCountry(Country.ID).FirstOrDefault();
CountrySource.Description = Country.Description;
CountrySource.Status = Country.Status;
affected = client.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
return affected;
}
/// <summary>
/// Deletes the Country .
/// </summary>
/// <param name="CountryID">The Country ID.</param>
/// <returns>Deletion status.</returns>
public bool DeleteCountry(int CountryID)
{
bool status = false;
MyEntities client = new MyEntities();
try
{
Country Country = client.GetCountry(CountryID).FirstOrDefault();
if (Country != null)
{
client.DeleteObject(Country);
client.SaveChanges();
status = true;
}
}
catch (Exception ex)
{
throw ex;
}
return status;
}
/// <summary>
/// Gets all states by country.
/// </summary>
/// <param name="countryID">The country ID.</param>
/// <returns>List Of all states.</returns>
public IList<State> GetAllStatesByCountry(int countryID)
{
MyEntities client = new MyEntities();
try
{
return client.GetAllStatesByCountry(countryID).ToList();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Gets the state.
/// </summary>
/// <param name="StateID">The state ID.</param>
/// <returns>state by stateId.</returns>
public State GetState(int StateID)
{
MyEntities client = new MyEntities();
try
{
return client.GetState(StateID).FirstOrDefault();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Creates the state for country.
/// </summary>
/// <param name="State">The state.</param>
/// <param name="countryID">The country ID.</param>
/// <returns>
/// state with stateId.
/// </returns>
public int CreateStateForCountry(State State, int countryID)
{
MyEntities client = new MyEntities();
try
{
Country country = client.GetCountry(countryID).FirstOrDefault();
State.CountryReference.Value = country;
client.AddToStates(State);
client.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
return State.StateID;
}
/// <summary>
/// Updates the state.
/// </summary>
/// <param name="State">The state.</param>
/// <param name="countryID">The country ID.</param>
/// <returns>Updated state.</returns>
public int UpdateState(State State, int countryID)
{
int affected = 0;
MyEntities client = new MyEntities();
try
{
State StateSource = client.GetState(State.StateID).ToList().FirstOrDefault();
Country country = client.GetCountry(countryID).FirstOrDefault();
StateSource.Description = State.Description;
StateSource.CountryReference.Value = country;
affected = client.SaveChanges();
}
catch (Exception ex)
{
throw ex;
}
return affected;
}
/// <summary>
/// Deletes the state.
/// </summary>
/// <param name="StateID">The state ID.</param>
/// <param name="CountryID">The country ID.</param>
/// <returns>Deletion status.</returns>
public bool DeleteState(int StateID, int countryID)
{
bool status = false;
MyEntities client = new MyEntities();
try
{
Country country = client.GetCountry(countryID).ToList().FirstOrDefault();
State State = client.GetState(StateID).ToList().FirstOrDefault();
if (State != null)
{
State.CountryReference.Value = country;
client.DeleteObject(State);
client.SaveChanges();
status = true;
}
}
catch (Exception ex)
{
throw ex;
}
return status;
}
Discussion :
1. Take a closer look at the Country table method calls and then at State tables, you will notice that as State is a child table , it will need to have extra parameter carrying parent information to be able to successfully save it Database.
--> State.CountryReference.Value = country; <--
2. Now check this article for Mapping SPs
3. Function mapping for Insert / Update / Delete action on the Country table will be straight forward as shown article above.
4. Function mapping for Insert / Update / Delete action on the State table will require you to pass parent information also. i.e CountryID
5. This worked in .Net 3.5 SP1 , it may get better in 4.0
0 comments:
Post a Comment