I am making a number of distinct controllers, one relating to each stored procedure in a database. These are only used to read data and making them available in JSON format for javascripts.
My code so far looks like this, and I'm wondering if I have missed any opportunities to re-use code, maybe make some help classes. I have way too little experience doing OOP, so any help and suggestions here would be really appreciated.
Here is my generalized code so far (tested and works);
using System;
using System.Configuration;
using System.Web.Mvc;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Prototype.Models;
namespace Prototype.Controllers
{
public class NameOfStoredProcedureController : Controller
{
char[] lastComma = { ',' };
String oldChar = "\"";
String newChar = """;
StringBuilder json = new StringBuilder();
private String strCon = ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString;
private SqlConnection con;
public StoredProcedureController()
{
con = new SqlConnection(strCon);
}
public string do_NameOfStoredProcedure(int parameter)
{
con.Open();
using (SqlCommand cmd = new SqlCommand("NameOfStoredProcedure", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@parameter", parameter);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
json.AppendFormat("[{0},\"{1}\"],", reader["column1"], reader["column2"]);
}
}
con.Close();
}
if (json.Length.ToString().Equals("0"))
{
return "[]";
}
else
{
return "[" + json.ToString().TrimEnd(lastComma) + "]";
}
}
//http://host.com/NameOfStoredProcedure?parameter=value
public ActionResult Index(int parameter)
{
return new ContentResult
{
ContentType = "application/json",
Content = do_NameOfStoredProcedure(parameter)
};
}
}
}
