asp.net mvc - retrieving data from a stored procedure

grahamor

Hi,

I am experienced with asp.net but not so much with the MVC side of things.

I am trying to display the results of a stored procedure on a webpage.

Would prefer to use ado.net as opposed to the entity framework as i dont need CRUD operations. I've heard ado.net is much faster for simple data retrieval. Every tutorial i check out uses the entity framework. Does anyone have any tips on how i could call the sproc in my controller and pass it to the view ?

Also, on the front end. Should i serialize the object into json to output it there ?

Thank you in advance

Giblet

Here's a basic connection to an sproc, without getting into why you shouldn't call an sproc directly from a controller.

SqlConnection conn = null;
SqlDataReader reader  = null;		
try
{	
	conn = new SqlConnection("ENTER-CONNECTION-STRING");
	conn.Open();

	SqlCommand cmd  = new SqlCommand("mySProcName", conn);
	cmd.CommandType = CommandType.StoredProcedure;
	cmd.Parameters.Add(new SqlParameter("@MyParam", someValue));
	reader = cmd.ExecuteReader();
	
	SomeObject myObj = new SomeObject();
	
	while (reader.Read())
	{
		myObj.MyProp = reader["columnname"];
	}
}
finally
{
	if (conn != null)
	{
		conn.Close();
	}
	if (reader != null)
	{
		reader.Close();
	}
}

The "SomeObject" is just whatever container class you want to write to hold the data. This can be passed to the view and you can output the data contained within.

JSON serialisation is only useful if you need it, forget about it for the moment.

Your action inside your controller

 public ActionResult Index()
 {
      //Code for SPROC here
      return View("Index", myObj);
 }

Your View, Index.cshtml, inside the Views/<ControllerName> folder

@model SomeObject

<h1>@Model.MyProp</h1>

The data layer should be encapsulated in some way, so you aren't opening and closing connections and calling procs from the front end, but for a simple exercise this should work. (The datalayer shouldn't care what consumes it, MVC, or a WebForms / WinForms app)