Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

Python

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

SQL Server Stored Proc Code

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!

Stored Proc

Create the ASP .Net MVC3 Application

Open an MVC3 Project in Visual Studio 2010

MVC 3

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

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 GetCatProd()
 {
   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 list = new List();
    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 CatProd { get; set; }
 }

 public class CatProdAppService
 {
  ICatProdRepository _catProd;

  public CatProdAppService(ICatProdRepository catProd)
  : base()
  {
   this._catProd = catProd;
  }

 public IEnumerable GetCatProd()
 {
  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>

Tabs

Test the code to see that the Categories and Products are returned to the View.

Categories and Products View in Page