CODEWITHSUNDEEP
c#asp.net-mvc-5entity-framework-6
user4864716
user4864716

Reputation:

How to clear parameters after use of `db.Database.SqlQuery<model>`

In an MVC 5 web app using Entity Framework, I learned how to populate an Index view by using db.Database.SqlQuery<model> to execute a stored procedure and show the results in the Index View.

This is the relevant code in my Index View (and it works).

// supply parameter values required by the stored procedure
object[] parameters = {
  new SqlParameter("@campus",SqlDbType.NVarChar,3) {Value=vm.SelectedCampus},
  new SqlParameter("@date1",SqlDbType.DateTime) {Value=Convert.ToDateTime(vm.SelectedStartDate)},
  new SqlParameter("@date2",SqlDbType.DateTime) {Value=Convert.ToDateTime(vm.SelectedEndDate)}
};

// populate the list by calling the stored procedure and supplying parameters
IEnumerable<PerfOdomoeterDate> query =
  db.Database.SqlQuery<PerfOdomoeterDate>("PerfOdomoeterDate @campus, @date1, @date2",
      parameters).OrderBy(m => m.StudentName).ToList();

And to put that code into better context, here is the entire Index ActionResult.

private PerformanceContext db = new PerformanceContext();
private static readonly string d1 = DateTime.Now.ToShortDateString();
private static readonly string d2 = DateTime.Now.ToShortDateString();

[HttpGet]
public ActionResult Index(int? page, string SelectedCampus = "CRA", string SelectedStartDate=null, string SelectedEndDate=null)
{
  int PageNumber = (page ?? 1);

  PerfOdomoeterDateViewModel vm = new PerfOdomoeterDateViewModel();
  vm.SelectedCampus = SelectedCampus;

  vm.SelectedStartDate = string.IsNullOrEmpty(SelectedStartDate) ? d1 : SelectedStartDate;
  vm.SelectedEndDate = string.IsNullOrEmpty(SelectedEndDate) ? d2 :SelectedEndDate;
  vm.CampusList = StaticClasses.ListBank.CampusList();        

  // supply parameter values required by the stored procedure
  object[] parameters = {
      new SqlParameter("@campus",SqlDbType.NVarChar,3) {Value=vm.SelectedCampus},
      new SqlParameter("@date1",SqlDbType.DateTime) {Value=Convert.ToDateTime(vm.SelectedStartDate)},
      new SqlParameter("@date2",SqlDbType.DateTime) {Value=Convert.ToDateTime(vm.SelectedEndDate)}
  };

  // populate the list by calling the stored procedure and supplying parameters
  IEnumerable<PerfOdomoeterDate> query =
      db.Database.SqlQuery<PerfOdomoeterDate>("PerfOdomoeterDate @campus, @date1, @date2",
          parameters).OrderBy(m => m.StudentName).ToList();            

  vm.CreditTable = query.ToPagedList(PageNumber, 25);                        
  return View(vm);
}

As I stated, this code works perfectly in the Index View. However, in a separate ActionResult, where the user has an option to export the data set to an Excel file, I use the same code, and I get this runtime error:

The SqlParameter is already contained by another SqlParameterCollection.

I was under the impression that each ActionResult is in its own scope, so how is it that I am getting this error when I am calling up a new query from a separate ActionResult?

Intellisense did not give me any clues as to how I could explicitly empty the parameters after executing the stored procedure.

This is the code in the ActionResult that is producing the error.

public ActionResult ExportToExcel(string SelectedCampus, string SelectedStartDate, string SelectedEndDate)
{            

  object[] parameters2 = {
      new SqlParameter("@campus",SqlDbType.NVarChar,3) {Value=SelectedCampus},
      new SqlParameter("@date1",SqlDbType.DateTime) {Value=Convert.ToDateTime(SelectedStartDate)},
      new SqlParameter("@date2",SqlDbType.DateTime) {Value=Convert.ToDateTime(SelectedEndDate)}
  };

  IEnumerable<PerfOdomoeterDate> query =
      db.Database.SqlQuery<PerfOdomoeterDate>("PerfOdomoeterDate @campus, @date1, @date2",
          parameters2).OrderBy(m => m.StudentName).AsEnumerable();

...

Upvotes: 3

Views: 3647

Answers (2)

Amal
Amal

Reputation: 1

I would say, This is how as per the design.
You need to extract the data right from there .ToArray() or .ToList() etc...
Do not try to re execute the query for further data operations.

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109205

The ADO.Net objects (like SqlParameter, SqlCommand etc.) presented to us by the .Net framework are a mere layer on top of the real stuff under the hood that is managed by the .Net connection pool. If we create a new SqlConnection —which is implicitly done by db.Database.SqlQuery— we don't really establish a new connection to the database. That would be far too expensive. In reality, our connection object "plugs" in to an available connection in the connection pool.

Normally, this mechanism is pretty transparent, but it is unveiled in issues like the one you see here. I remember having experienced similar issues (exceptions that persisted longer than met the eye).

The message is: you can't beat it, so join it. The quick solution seems to be renaming the parameters in one of the methods. A better solution, of course, is to factor out the repetitive part of your code into a method that contains the identical parts.

Upvotes: 3

Related Questions