MVC3 Using Ninject and SQL Server Data
MVC3 ASP .Net Using SQL Server and Ninject 2.2
This article describes how to use an IoC container (Ninject) and SQL Server data with Dependency Injection to send data to a View. This will work with any SQL Server version (2000, 2005, 2008R2) as it makes use of a SqlCommand object, SqlConnection object and SqlDataReader.
Create a SQL Stored Procedure
This article will use the Northwind Database for SQL Server. The view is dbo.[Products by Category]. Test the Northwind View for the data source to use in the project.
Create a stored procedure. This one uses Sql Server Development Tools Juneau CTP3 in Visual Studio 2010. Alt + X to execute the Query instead of F5!
Create the ASP .Net MVC3 Application
Open an MVC3 Project in Visual Studio 2010
Create the ASP .Net MVC3 Interface and Repository
Add a class library with an interface which has a method to return a collection and a class type to return the fields from the stored procedure.
public interface ICatProdRepository
{
IEnumerable
}
public class CatProd
{
public string CategoryName { get; set; }
public string ProductName { get; set; }
public int UnitsInStock { get; set; }
public string StockStatus { get; set; }
public CatProd()
:base() { }
}
Add a repository to get the data from SQL Server. This class inherits from the interface created and connects to SQL Server via the Connection string in Web.config (A better implementation might have been to package the connection & command into a separate class that returns a SqlDataReader and reference the DLL in the bin folder of the project. Next time...). Data is retrieved from the DataReader into a method that returns a CatProd item which is then added to the IList collection of the CatProd type.
public class SqlCatProdRepository : ICatProdRepository
{
public IEnumerable
{
var conString = ConfigurationManager.ConnectionStrings["Northwind"];
string sCn = conString.ConnectionString;
SqlDataReader dr = null;
SqlConnection conn = new SqlConnection(sCn);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("usp_GetCatProd", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
IList
dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
var p = CreateCatProd(dr);
list.Add(p);
}
return list;
}
catch (Exception ex)
{
throw ex;
}
finally
{
dr.Close();
}
}
private CatProd CreateCatProd(SqlDataReader dr)
{
return new CatProd
{
CategoryName = dr["CategoryName"].ToString(),
ProductName = dr["ProductName"].ToString(),
UnitsInStock = Int32.Parse(dr["UnitsInStock"].ToString()),
StockStatus = dr["StockStatus"].ToString(),
};
}
}
Create the MVC3 PageView Model and Service
Add the Page View Model and a Service that will send the class to the Controller and the View using Constructor Injection in the App Service.
public class CatProdPageView
{
public IEnumerable
}
public class CatProdAppService
{
ICatProdRepository _catProd;
public CatProdAppService(ICatProdRepository catProd)
: base()
{
this._catProd = catProd;
}
public IEnumerable
{
return this._catProd.GetCatProd();
}
}
Add a Main Service that will return the CatProdPageView type by again using Constructor Injection instantiating a CatProdAppService in the Main Service constructor.
public class MainService
{
CatProdAppService _cs;
public MainService(CatProdAppService cs)
: base()
{
this._cs = cs;
}
public CatProdPageView GetCatProdViewModel()
{
CatProdPageView cpv = new CatProdPageView();
cpv.CatProd = this._cs.GetCatProd();
return cpv;
}
}
Add the Controller to the project to return the CatProdViewModel to the View.
public class CatProdController : Controller
{
MainService _mnSrvc;
public CatProdController(MainService mnSrvc)
: base()
{
this._mnSrvc = mnSrvc;
}
public ActionResult Index()
{
CatProdPageView cpv = this._mnSrvc.GetCatProdViewModel();
return View(cpv);
}
}
Create the MVC3 View
Add the View to the Project - Right click next to the ActionResult Index() code on the page to do this. Using the CatProdPageView View Model, we will iterate through a foreach loop to return the collection in a table.
@model NW.AppService.ViewModels.CatProdPageView
@{
ViewBag.Title = "Categories and Products";
}
<h2>Categories and Products</h2>
<table>
<tr><th>Category</th><th>Product Name</th><th>Units In Stock</th><th>Stock Status</th></tr>
@foreach (var itm in Model.CatProd)
{
<tbody>
<tr>
<td>@Html.Raw(@itm.CategoryName.ToString())</td>
<td>@Html.Raw(@itm.ProductName.ToString())</td>
<td>@Html.Raw(@itm.UnitsInStock.ToString())</td>
<td>@Html.Raw(@itm.StockStatus.ToString())</td>
</tr>
</tbody>
}
</table>
Adding Ninject to the MVC3 Project
Add a Reference to Ninject to the project. It may be downloaded here. Create a class that implements the IDependencyResolver Interface to bind the objects (i.e., repository to interface).
public class NinjectDR : IDependencyResolver
{
private IKernel _kernel;
public IKernel Kernel
{
get {
return this._kernel;
}
}
public NinjectDR()
: base()
{
this._kernel = new StandardKernel();
AddBindings();
}
public object GetService(Type serviceType)
{
return this._kernel.TryGet(
serviceType);
}
public IEnumerable<object> GetServices(Type serviceType)
{
return this._kernel.GetAll(
serviceType);
}
public IBindingToSyntax<T> Bind<T>()
{
return this._kernel.Bind<T>();
}
public T ResolveType<T>()
{
return this._kernel.Get<T>();
}
private void AddBindings()
{
Bind<CatProdAppService>().ToSelf();
Bind<MainService>().ToSelf();
Bind<ICatProdRepository>().To<SqlCatProdRepository>();
}
}
Add code to the Application_Start() in Global.asax
DependencyResolver.SetResolver(new NinjectDR());
Add a tab in _Layout.cshtml for the Products.
<li>@Html.ActionLink("Products", "Index", "CatProd")</li>
Test the code to see that the Categories and Products are returned to the View.
