Clash Royale CLAN TAG#URR8PPP

Where should I relocate my direct SQL Queries (MVC 5) to avoid a fat HomeController?

I need to add ~30+ SQL commands with different stored procedures, and want to avoid having a “fat” home controller.

I would appreciate recommendations as to where I relocate the SQL commands (away from the home controller) and how the controller accesses them.

I am concurrently building and testing this with an EF to assess which query techniques are most efficient and easy to secure. Efficiency is not currently a problem, as the db will only be about 5 columns and 5-10k rows and the “where” statements limit the transfer of data.

However, in time the db may get a lot bigger, and increasing the number of queries may impact performance.

At present, I feel more comfortable with the direct method, but remain open to using an EF if there is good reason to do so.

public ActionResult Test4()

string markers = "";
string conString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

SqlCommand cmd = new SqlCommand("Sp_GeoLoc_10 ");

using (SqlConnection con = new SqlConnection(conString))

cmd.Connection = con;
con.Open();

using (SqlDataReader sdr = cmd.ExecuteReader())

while (sdr.Read())

markers += "[";
markers += string.Format("0,", sdr["Latitude"]);
markers += string.Format("0", sdr["Longitude"]);
markers += "],";



con.Close();


markers += "";
ViewBag.Markers = markers;



By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.