我可能不会直接从控制器访问数据库，但会相当抽象的这种访问。不是一个真正的性能优化而设计的改进。因此，通过定义一个模型，将持有的存储过程的结果启动：

 公共类为MyModel
{
    公共字符串列1 {获得;组; }
    公共字符串列2 {获得;组; }
}
 

然后定义库的界面将包含在这个模型中不同的操作：

 公共接口IRepository
{
    IEnumerable的＆LT;为MyModel＆GT; GetModel（中间体ID）;
}
 

下一步实施库：

 公共类RepositorySql：IRepository
{
    公开的IEnumerable＆LT;为MyModel＆GT; GetModel（INT ID）
    {
        使用（VAR康恩=新的SqlConnection（ConfigurationManager.ConnectionStrings [SomeConnectionString。的ConnectionString））
        使用（VAR CMD = conn.CreateCommand（））
        {
            conn.Open（）;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText =NameOfStoredProcedure;
            cmd.Parameters.AddWithValue（@参数，身份证）;
            使用（SqlDataReader的读卡器= cmd.ExecuteReader（））
            {
                而（reader.Read（））
                {
                    收益回报新为MyModel
                    {
                        列1 =读卡器[列1]的ToString（）
                        列2 =读卡器[列2]的ToString（）
                    };
                }
            }
        }
    }
}
 

最后你的控制器将使用存储库：

 公共类NameOfStoredProcedureController：控制器
{
    私人只读IRepository _repository;
    公共NameOfStoredProcedureController（IRepository库）
    {
        _repository =库;
    }

    //警告不加此构造函数。使用DI框架来代替。
    //这种构造被称为穷人的DI（见http://www.lostechies.com/blogs/jimmy_bogard/archive/2009/07/03/how-not-to-do-dependency-injection-in-nerddinner.aspx)
    //关于为什么这是不好的更多信息。
    公共NameOfStoredProcedureController（）：这个（新RepositorySql（））
    {}

    公众的ActionResult指数（INT参数）
    {
        VAR模型= _repository.GetModel（参数）;
        //使用直接的Json，无需手动操作序列化
        返回JSON（模型）;
    }
}
 

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)
            };
        }
    }
}

I probably wouldn't directly access the database from the controller but would rather abstract this access. Not really a performance optimization but design improvement. So start by defining a model that will hold the result of the stored procedure:

public class MyModel
{
    public string Column1 { get; set; }
    public string Column2 { get; set; }
}

Then define a repository interface that will contain the different operations on this model:

public interface IRepository
{
    IEnumerable<MyModel> GetModel(int id);
}

Next implement the repository:

public class RepositorySql : IRepository
{
    public IEnumerable<MyModel> GetModel(int id)
    {
        using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "NameOfStoredProcedure";
            cmd.Parameters.AddWithValue("@parameter", id);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return new MyModel
                    {
                        Column1 = reader["column1"].ToString(),
                        Column2 = reader["column2"].ToString()
                    };
                }
            }
        }
    }
}

Finally your controller will use the repository:

public class NameOfStoredProcedureController : Controller
{
    private readonly IRepository _repository;
    public NameOfStoredProcedureController(IRepository repository)
    {
        _repository = repository;
    }

    // Warning don't add this constructor. Use a DI framework instead.
    // This kind of constructors are called Poor Man DI (see http://www.lostechies.com/blogs/jimmy_bogard/archive/2009/07/03/how-not-to-do-dependency-injection-in-nerddinner.aspx)
    // for more info on why this is bad.
    public NameOfStoredProcedureController() : this(new RepositorySql())
    { }

    public ActionResult Index(int parameter)
    {
        var model = _repository.GetModel(parameter);
        // Use directly Json, no need to do the serialization manually
        return Json(model);
    }
}