Autocomplete dropdown with Jquery Ui and Asp.net MVC3

You probably know about Jquery UI autocomplete dropdown. It helps you to load long list with search params. You dont need to load all the values to the page. Dropdown list will load on demand.

I copied my notes here to remember next time i need to use it:

You can get the latest jquery ui js and css files from http://jqueryui.com. You should select autocomplete option to have this feature.

First, you need to set a reference to Jquery scripts in your page:

   1:   <link href="@Url.Content("~/Content/themes/ui-lightness/jquery-ui-1.8.18.custom.css")" rel="stylesheet" type="text/css" />

   2:  <script src="@Url.Content("~/Scripts/jquery-1.7.1.min.js")" type="text/javascript"></script>

   3:  <script src="@Url.Content("~/Scripts/jquery-ui-1.8.18.custom.min.js")" type="text/javascript"></script>

   4:   

   5:  <script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>

   6:  <script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>

Copy related theme and src files to related directories in your project.

Step2: You need to add your textbox to your page:

   1:  <div class="ui-widget">

   2:              <label for="customerdropdown">

   3:                  Customer:

   4:              </label>

   5:              <input id="customerdropdown" />

   6:          </div>

   7:          <div class="ui-widget" style="margin-top: 2em; font-family: Arial">

   8:              Result:

   9:              <div id="log" style="height: 200px; width: 300px; overflow: auto;" class="ui-widget-content">

  10:              </div>

  11:          </div>

Step3: Define actions in your js file:

   1:  <script>

   2:      $(function () {

   3:          function log(message) {

   4:              $("<div/>").text(message).prependTo("#log");

   5:              $("#log").scrollTop(0);

   6:          }

   7:   

   8:          $("#customerdropdown").autocomplete({

   9:              source: "/PhoenixEvent/GetCustomers/",

  10:              minLength: 2,

  11:              select: function (event, ui) {

  12:                  log(ui.item ?

  13:                      "Selected: " + ui.item.value + " aka " + ui.item.id :

  14:                      "Nothing selected, input was " + this.value);

  15:              }

  16:          });

  17:      });

  18:      </script>

Step4: You should have method in your controller to provide this list for each search term.

   1:    public JsonResult GetCustomers(string term)

   2:          {

   3:              //Phoenice company

   4:              int companyid = GetValidCompanyId();

   5:              //max 1000

   6:              var customers = db.Customers.Where(a=>a.CompanyId == companyid && (a.first_name.Contains(term) || a.last_name.Contains(term) ) ).Take(1000).ToList();

   7:              

   8:              List<object> dropDownInfos = new List<object>();

   9:   

  10:              customers.ForEach(t=>{dropDownInfos.Add(new 

  11:                                                          {

  12:                                                               id = t.CustomerId,

  13:                                                               label = t.first_name + " " + t.last_name,

  14:                                                               value = t.first_name + " " + t.last_name,

  15:                                                          });});

  16:              return Json(dropDownInfos,JsonRequestBehavior.AllowGet);

  17:          }

Don't forget the JsonRequestBehavior.AllowGet parameter in the Json call. You won't get anything back from the action method if you leave it off. You should return Json object with "id","value",and "label" columns.

Also, the name of the parameter passed into the action method is important. The jQuery UI autocomplete method will call your source URL with a query string like "?term=yoursearch". If you set the parameter name to a string named "term", the MVC model binder will grab the value from the query string for you.

NExt step is actually plug in this autocomplete to your real input param. You can do that in select action which we were just printing in log. You can get the selected value and update the actual field.

The jQuery UI Autocomplete function has several other options and events you can use in your app, and the demos are worth checking out too.

Grid Paging and dynamic ordering with ORDER by CASE statement

If you are writing dynamic query and using "EXEC" statement to get paging and dynamic ordering support, you will have performance and security problems.

SQL Server 2005 and above supports Case statement in ordering. It also has partial table expression statements. You can use both of those to build powerful queries for your grid data.

First example: dynamic ordering and row numbering

   1:  DECLARE @temp table

   2:  (ShipNo int,

   3:   Supplier varchar(10),

   4:   Shiptime datetime

   5:  )

   6:   

   7:  select dateadd(day,5,getdate())

   8:   

   9:  INSERT INTO @temp VALUES (1,'WRWE' ,dateadd(day,1,getdate()))

  10:  INSERT INTO @temp VALUES (1, 'AXTFA', dateadd(day,3,getdate()))

  11:  INSERT INTO @temp VALUES (2, 'ABLRA',dateadd(day,5,getdate()))

  12:  INSERT INTO @temp VALUES (2, 'CADRB',dateadd(day,8,getdate()))

  13:  INSERT INTO @temp VALUES (3, 'BGRTEE',dateadd(day,15,getdate()))

  14:  INSERT INTO @temp VALUES (4, 'AAAAAA',dateadd(day,25,getdate()))

  15:   

  16:  Declare @OrderBy nvarchar(255)

  17:  DECLARE @SortDir nvarchar(5)

  18:   

  19:  SET @OrderBy = 'Supplier';

  20:  SET @SortDir = 'asccc';

  21:   

  22:  --SQL 2005 and above 

  23:   

  24:  --separate data columns for different data types in cases

  25:  SELECT ROW_NUMBER() OVER (ORDER BY 

  26:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Supplier' THEN Supplier END ASC,

  27:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'ShipNo' THEN ShipNo END ASC,

  28:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Shiptime' THEN Shiptime END ASC,

  29:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Supplier' THEN Supplier END desc,

  30:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'ShipNo' THEN ShipNo END desc,

  31:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Shiptime' THEN Shiptime END desc,

  32:   

  33:   CASE WHEN 1=1 then Shipno end --default

  34:   )

  35:  AS RowNo,

  36:  * --get all

  37:  FROM @temp

  38:  ;

Next, we will add paging elements into our query. We need to use rownumber in where clause to make paging work.

   1:   

   2:   

   3:  --for paging you can take this in subset and specify rowno

   4:  DECLARE @rowstart int

   5:  DECLARE @rowend int

   6:  set @rowstart = 1;

   7:  set @rowend = 3; --paging support

   8:   

   9:  ;

  10:  with subset as

  11:           (

  12:           SELECT ROW_NUMBER() OVER (ORDER BY 

  13:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Supplier' THEN Supplier END ASC,

  14:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'ShipNo' THEN ShipNo END ASC,

  15:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Shiptime' THEN Shiptime END ASC,

  16:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Supplier' THEN Supplier END desc,

  17:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'ShipNo' THEN ShipNo END desc,

  18:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Shiptime' THEN Shiptime END desc,

  19:   

  20:   CASE WHEN 1=1 then Shipno end --default

  21:   )

  22:  AS RowNo,

  23:  * --get all

  24:  FROM @temp

  25:   

  26:  )

  27:  select * from subset

  28:  where RowNo between @rowstart and @rowend

You need to separate case statement for different data type. Otherwise, you will get conversion errors.

the underlying provider failed to open

You may receive this error if you open multiple connections within same transaction scope using Entity framework. You can open connection at top to solve this. if it is still not working, then you may have issue with MS-DTC (Distributed transaction co-ordinator).

   1:   var somedb = new SomeDbEntities();

   2:   

   3:  public void SomeMethodTOUpdateDb(){

   4:    using (TransactionScope scope = new TransactionScope())

   5:                  {

   6:                      //Generate link name

   7:                      somedb.Connection.Open();

   8:                      //do db related work

   9:                      Biller biller = new Biller();

  10:                      biller.BillerId = Guid.NewGuid();

  11:                      billerview.ToBiller(biller);

  12:                      billerview.BillerId = biller.BillerId; //update billerid

  13:                      db.Billers.AddObject(biller);

  14:                      YouCanCallAnotherMethodDoMoreDbWork(); 

  15:                      db.SaveChanges();

  16:                      //commit all

  17:                      scope.Complete();

  18:                   }

  19:  }

I love TELERIK JUSTMOCK and ASP.NET MVC

I am working on ASP.NET MVC3 project and using different mocking and testing tools such as Nunit,Moq, Telerik JustMock, MVcContrib, and Rhino.

I love commercial version of Telerik JustMock just because of partial method mocking feature. You can test one method and mock other calls within that class,so you can have unit tests for each method.

I have a helper class to build HttpContext. You may need that to check viewresults, session and similar data. These simple examples will show arrange, assert, and partial method mocking.

   1:     public class HttpContextTelerikMock

   2:      {

   3:   

   4:   

   5:   

   6:          public HttpContextBase MockContext { get; set; }

   7:          public HttpRequestBase MockRequest { get; set; }

   8:          public HttpResponseBase MockResponse { get; set; }

   9:          public HttpSessionStateBase MockSession { get; set; }

  10:          public HttpServerUtilityBase MockServer { get; set; }

  11:          public IPrincipal MockUser { get; set; }

  12:          public IIdentity MockIdentity { get; set; }

  13:   

  14:      

  15:          public HttpContextTelerikMock()

  16:          {

  17:              //do nothing

  18:          }

  19:          

  20:          public void SetupSessionOnly(Dictionary<string, string> sessionStubs )

  21:          {

  22:   

  23:              MockContext = Mock.Create<HttpContextBase>();

  24:              foreach (KeyValuePair<string, string> keyValuePair in sessionStubs)

  25:              {

  26:                  Mock.Arrange(() => MockContext.Session[keyValuePair.Key]).Returns(keyValuePair.Value);

  27:   

  28:              }

  29:          }

  30:   

  31:          /// <summary>

  32:          /// Basic mocks for response, request,session, and server

  33:          /// </summary>

  34:          public void BareInit()

  35:          {

  36:              MockContext =   Mock.Create<HttpContextBase>();

  37:              MockRequest = Mock.Create<HttpRequestBase>();

  38:              MockResponse = Mock.Create<HttpResponseBase>();

  39:              MockSession = Mock.Create<HttpSessionStateBase>();

  40:              MockServer = Mock.Create<HttpServerUtilityBase>();

  41:   

  42:   

  43:              Mock.Arrange(()=> MockContext.Request).Returns(MockRequest);

  44:              Mock.Arrange(()=> MockContext.Response).Returns(MockResponse);

  45:              Mock.Arrange(()=> MockContext.Session).Returns(MockSession);

  46:              Mock.Arrange(()=> MockContext.Server).Returns(MockServer);

  47:              

  48:          }

  49:   

  50:          public HttpContextBase SetupCurrentUser(string username)

  51:          {

  52:              //Context.User

  53:              MockContext =   Mock.Create<HttpContextBase>();

  54:              MockRequest = Mock.Create<HttpRequestBase>();

  55:              MockResponse = Mock.Create<HttpResponseBase>();

  56:              var MockUser = Mock.Create<IPrincipal>();

  57:              var MockIdentity = Mock.Create<IIdentity>();

  58:              Mock.Arrange(()=> MockContext.User).Returns(MockUser);

  59:              Mock.Arrange(()=> MockUser.Identity).Returns(MockIdentity);

  60:              Mock.Arrange(()=> MockUser.Identity.Name).Returns(username);

  61:              

  62:              //Request

  63:              Mock.Arrange(()=> MockRequest.InputStream).Returns(new MemoryStream());

  64:   

  65:              //Response

  66:              Mock.Arrange(()=> MockResponse.OutputStream).Returns(new MemoryStream());

  67:              return MockContext;

  68:          }

  69:      }

You can extend this to arrange httpcontext for different requirements.

In this simple example, I want to arrange a another method inside this controller to return a specific response,so i can test single method not all the related calls.

I have interfaces for Repositories to mock their behavior. In this example, "setupRepo" will be a mock object to prevent direct database call in the controller. Our goal is to test only this specific method not all related calls.

   1:          public IBillerSetupRepo setupRepo;  

   2:           //

   3:          // GET: /Default1/Edit/5

   4:          public ActionResult Edit(Guid id)

   5:          {

   6:              string username = User.Identity.Name;

   7:              if (HasAccessToBiller(username, id))

   8:              {

   9:                  Biller biller = setupRepo.GetBiller(id);

  10:                  return View(biller.ToModelView());

  11:              }

  12:              else

  13:              {

  14:                  return View("NotAuthorized");

  15:              }

  16:              

  17:          }

We are returning two different views: notauthorized and edit with model. First test checks the unauthorized view. It will return this view, if user does not have access to this biller.

   1:     [Test]

   2:          public void Edit_SendNotAuthorized()

   3:          {

   4:              BillerController target= new BillerController();

   5:              Guid billerid = Guid.NewGuid();

   6:              string username = "Test234";

   7:              //partial method mock

   8:              Mock.Arrange(() => target.HasAccessToBiller(username, billerid)).Returns(false);

   9:   

  10:              //context adjustment

  11:              HttpContextTelerikMock helper = new HttpContextTelerikMock();

  12:              target.ControllerContext = new ControllerContext(helper.SetupCurrentUser(username), new RouteData(), target);

  13:   

  14:              //Check viewpage

  15:              ViewResult result = target.Edit(billerid) as ViewResult;

  16:              Assert.AreEqual("NotAuthorized", result.ViewName);

  17:   

  18:   

  19:          }

Second call will check the call, if user has access to this biller:

   1:     [Test]

   2:          public void Edit_HasAccess_SendModelTOView()

   3:          {

   4:              IBillerSetupRepo mocksetuprepo = Mock.Create<IBillerSetupRepo>();

   5:              BillerController target = new BillerController();

   6:              Guid billerid = Guid.NewGuid();

   7:              string username = "Test234";

   8:              Biller testbiller = new Biller

   9:                                      {

  10:                                          BillerName = "Testbiller2xbv342342"

  11:                                      };

  12:              //partial method mock

  13:              Mock.Arrange(() => target.HasAccessToBiller(username, billerid)).Returns(true);

  14:              //Arrange repo to return our model

  15:              Mock.Arrange(() => mocksetuprepo.GetBiller(billerid)).Returns(testbiller);

  16:              HttpContextTelerikMock helper = new HttpContextTelerikMock();

  17:              target.ControllerContext = new ControllerContext(helper.SetupCurrentUser(username), new RouteData(), target);

  18:              target.setupRepo = mocksetuprepo;

  19:   

  20:   

  21:              ViewResult result = target.Edit(billerid) as ViewResult;

  22:              Assert.AreEqual(testbiller.BillerName, ((BillerView)result.Model).BillerName);

  23:              //Assert that mock object was called

  24:              Mock.Assert(() => mocksetuprepo.GetBiller(billerid), Occurs.Once());

  25:              

  26:          }

Running Unit Tests in database

Well, you may be thinking about how to test behavior of the stored procedures for different inputs. You may want to be sure that your procs are doing what they are supposed to do. They are giving correct access or sending correct payment amount. You can test those from your app as well, but independent database tests are better in terms of flexibility to run them without apps.

I have recently setup one build automation and decided to re-create database from scratch with seed data to test everything. Of course, it takes time to write tests for app and database, but it is a good start.

Some quick tips to test your stored procs:

You need to call only one proc to run all of your database tests. It can insert results into temp table and just return that.

Example unit test call:

   1:  --======================TESTs======================

   2:  IF OBJECT_ID('tempdb..#testresults') IS NOT NULL

   3:  BEGIN

   4:  PRINT '#temp exists!'

   5:  --drop

   6:  drop table #testresults;

   7:  END

   8:  ELSE

   9:  BEGIN

  10:  PRINT '#temp does not exist!'

  11:  END

  12:   

  13:   

  14:  Create table #testresults

  15:  (resultid int identity(1,1) primary key,

  16:  [Status] nvarchar(50) not null,

  17:  Expected nvarchar(250) null,

  18:  Actual nvarchar(250) null,

  19:  ErrMsg nvarchar(250) null

  20:  )

  21:  --======================TEST 1======================

  22:  declare @accesCheck bit

  23:  EXEC Proc_Exp_HasUserAccessToFunction @username,'testfunc1',@accesCheck output;

  24:  EXEC TestUtil_Assert 1,@accesCheck,'testfunc1 access is not right';

  25:  --======================TEST 2======================

  26:  EXEC Proc_Exp_HasUserAccessToFunction @username,'testfunc2',@accesCheck output;

  27:  EXEC TestUtil_Assert 1,@accesCheck,'testfunc2 access is not right';

  28:   

  29:   

  30:  --select results

  31:  select * from #testresults;

  32:   

  33:  drop table #testresults;

You can create Assert statements at database side as well. Here is Assert.areEqual call:

   1:  --===================SAMPLE TEST HELPER: assert=====================

   2:   

   3:  create procedure dbo.TestUtil_Assert

   4:  @expected nvarchar(250),

   5:  @actual nvarchar(250),

   6:  @errmsg nvarchar(250)

   7:  as

   8:   

   9:    if (@expected is null and @actual is null) or  (@expected = @actual)

  10:        insert into #testresults

  11:        select 'PASS' as Status,cast( @expected as nvarchar(250)),cast(@actual as nvarchar(250)), cast( @errmsg as nvarchar(250))

  12:    ELSE

  13:      insert into #testresults

  14:      select 'FAILURE' as Status,cast( @expected as nvarchar(250)),cast(@actual as nvarchar(250)), cast( @errmsg as nvarchar(250))

Tips: The version of SQL Server in use does not support datatype 'datetime2'

If you are using edmx model and you see this error, it means your database reference is not right. This happens when you update your edmx from another version of sql server and then run on different version.

1. Right click edmx model
2. Select edit with option
3. select xml editor from the dialog menu
4. Change ProviderManifestToken at the top to reference 2005 like this:

1. <Schema Namespace="YOUR_DBModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005"