我提出了许多不同的控制器,一个涉及在数据库中的每个存储的过程。这些仅是用来将读出的数据并使其JSON格式为Javascript角提供。
我的code到目前为止是这样的,我想知道如果我错过任何机会,以重新使用code,也许做一些帮助类。我有太多经验不多做面向对象编程,所以任何帮助和建议,在这里将是非常美联社preciated。
下面是我的广义code为止(测试和工程);
使用系统;
使用System.Configuration;
使用System.Web.Mvc;
使用System.Data这;
使用System.Text;
使用System.Data.SqlClient的;
使用Prototype.Models;
命名空间Prototype.Controllers
{
公共类NameOfStoredProcedureController:控制器
{
的char [] lastComma = {','};
字符串oldChar =\;
串newChar =与& QUOT;;
StringBuilder的JSON =新的StringBuilder();
私人字符串STRCON = ConfigurationManager.ConnectionStrings [SomeConnectionString]的ConnectionString。
私人的SqlConnection CON;
公共StoredProcedureController()
{
CON =新的SqlConnection(STRCON);
}
公共字符串do_NameOfStoredProcedure(INT参数)
{
con.Open();
使用(CMD的SqlCommand =新的SqlCommand(NameOfStoredProcedure,CON))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(@参数,参数);
使用(SqlDataReader的读卡器= cmd.ExecuteReader())
{
而(reader.Read())
{
json.AppendFormat([{0} \{1} \],读者[列1],读者[列2]);
}
}
con.Close();
}
如果(json.Length.ToString()。等于(0))
{
返回 [];
}
其他
{
返回[+ json.ToString()TrimEnd(lastComma)+];
}
}
//http://host.com/NameOfStoredProcedure?parameter=value
公众的ActionResult指数(INT参数)
{
返回新ContentResult类型
{
的ContentType =应用/ JSON
内容= do_NameOfStoredProcedure(参数)
};
}
}
}
解决方案
我可能不会直接从控制器访问数据库,但会相当抽象的这种访问。不是一个真正的性能优化而设计的改进。因此,通过定义一个模型,将持有的存储过程的结果启动:
公共类为MyModel
{
公共字符串列1 {获得;组; }
公共字符串列2 {获得;组; }
}
然后定义库的界面将包含在这个模型中不同的操作:
公共接口IRepository
{
IEnumerable的<为MyModel> GetModel(中间体ID);
}
下一步实施库:
公共类RepositorySql:IRepository
{
公开的IEnumerable<为MyModel> 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);
}
}