JSD Ideas: June 2010

Friday, June 11, 2010

Using ConvertAll and Aggregate in LINQ


string strImportResult = this.ImportResult.ToList()
     .ConvertAll<string>(Import => string.Format("<tr><td> 
         {0}</td><td>{1}</td><td>{2}</td><td>
         {3}</td></tr>"
         , Import.CustodianName
         , Import.ImportFile
         , Import.ImportStatus
         , Import.Particular))
    .Aggregate((ImportA, ImportB) => ImportA + ImportB);

Multipal Field Group By IN LINQ



var objOrderList = from OrderList in
        (from OrderDetail in objOrderDetail
      join Item in objItem on OrderDetail.ItemId equals Item.ItemId
      join ItemGroup in objItemGroup on Item.GroupId equals ItemGroup.GroupId
      select new
      {
       GroupName = ItemGroup.GroupName,
       ItemName = Item.ItemName,
       Rate = Item.Rate,
       Qty = OrderDetail.Qty
      }
      )
       group OrderList by new
       {
        OrderList.GroupName,
        OrderList.ItemName
       } into OrderList
       select new
       {
        GroupName = OrderList.Key.GroupName,
        ItemName = OrderList.Key.ItemName,
        OrderTotal = OrderList.Sum(OL => OL.Qty * OL.Rate)
       };

Left Outer Join and Group By In LINQ


List Table1 = new List();
List Table2 = new List();

var objList = from ListData in
                  (from a in Table1
                   join b in Table2 on a.Id equals b.Id into t2
                   from t2Data in t2.DefaultIfEmpty()
                   select new
                   {
                       Product = a.Product,
                       Title = t2Data.Title,
                       Amount = a.Amount
                   }
                )
              group ListData by new
              {
                  ListData.Product,
                  ListData.Title
              } into GroupData
              select new
              {
                  Product = GroupData.Key.Product,
                  Title = GroupData.Key.Title,
                  Amount = GroupData.Sum(OL => OL.Amount)
              };

EditTemplates for FieldType in MVC

View->Shared->EditorTemplates->Decimal.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.TextBox("", String.Format("{0:F}", Model)) %>

DisplayFor in MVC

Views->Shared->DisplayTemplates->EmailAddress.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<a href="mailto:<%=  Html.Encode(Model) %>"><%= Html.Encode(Model) %></a>
<img src="/Content/images/sendemail.gif" />

EditorFor in MVC

Models->Supplier:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;

namespace FeaturesOfMVC2.Models
{
    [MetadataType(typeof(Supplier_Validation))]
    public partial class Supplier
    {
        class Supplier_Validation
        {
            [ScaffoldColumn(false)]
            [DisplayName("Supplier Id :")]
            public int SupplierId { get; set; }

            [Required(ErrorMessage = "Name Required!")]
            [DisplayName("Supplier Name :")]
            public string SupplierName { get; set; }

            [UIHint("StateDDL")]
            [DisplayName("State :")]
            public int StateId { get; set; }

            [UIHint("CityDDL")]
            [DisplayName("City :")]
            public int CityId { get; set; }

            [UIHint("EmailAddress")]
            [DisplayName("Email Address :")]
            public string EmailId { get; set; }
        }
    }
}

Views->Shared->EditorTemplates->StateDDL.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.DropDownList("", new SelectList(ViewData["States"] as IEnumerable, "Id", "Name", Model))%>

Views->Shared->EditorTemplates->CityDDL.ascx


<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%= Html.DropDownList("", new SelectList(ViewData["Cities"] as IEnumerable, "Id", "Name", Model))%>

Views->Supplier->Edit.aspx


<%= Html.EditorFor(model => model.StateID) %>
<%= Html.EditorFor(model => model.CityID) %>

JSON Calling from MVC Page

HTML:


<%= Html.DropDownList("", new SelectList(ViewData["States"] as IEnumerable, "Id", "Name", Model))%>
<%= Html.DropDownList("", new SelectList(ViewData["Cities"] as IEnumerable, "Id", "Name", Model))%>

Javascript:


<script type="text/javascript" language="javascript">
  $(document).ready(function() {
      $("select[id='StateId']").change(function() {
          $.ajax({
              type: "POST",
              url: "/Supplier/StateCityInfo/",
              data: { StateId: $(this).val() },
              dataType: "json",
              error: function(xhr, status, error) {
                  // you may need to handle me if the json is invalid
                  // this is the ajax object
                  alert(status);
              },
              success: function(data) {
                  $("#CityId").empty();
                  $.each(data, function(key, City) {
                      $("#CityId").append($("<option></option>").val(City.Id).html(City.Name));
                  });
              }
          });
      })
  })
</script>

Supplier Controller:


public class SupplierController : Controller
{
  [AcceptVerbs("POST")]
  public ActionResult StateCityInfo(int StateId)
  {
      return Json(this.AllCity(StateId));
  }
}

JSON Calling from ASP.Net

HTML:


<body>
    <form id="form1" runat="server">
    No Of Doors :
    <input id="txtNoOfDoor" type="text" /><br />
    <input type="button" id="Button1" value="Get All Cars" />
    <input type="button" id="Button2" value="Get Car By No Of Door" />
    <div id="output">
    </div>
    </form>
</body>

Javascript:


<script type="text/javascript" language="javascript">
        $(function() {
            $('#Button1').click(getCars);
            $('#Button2').click(getCarsByDoors);
        });

        function getCars() {
            $.ajax({
                type: "POST",
                url: "CarService.asmx/GetAllCars",
                data: "{}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function(response) {
                    var cars = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d;
                    $('#output').empty();
                    for (var i = 0; i < cars.length; i++) {
                        $('#output').append('<p><strong>' + cars[i].Make + ' ' +
                                cars[i].Model + '</strong><br /> Year: ' +
                                cars[i].Year + '<br />Doors: ' +
                                cars[i].Doors + '<br />Colour: ' +
                                cars[i].Colour + '<br />Price: £' +
                                cars[i].Price + '</p>');
                    }
                },

                failure: function(msg) {

                    $('#output').text(msg);

                }
            });
        }

        function getCarsByDoors() {
            var data = '{doors: ' + $('#txtNoOfDoor').val() + ', Name : "Jayesh" }';
            $.ajax({
                type: "POST",
                url: "CarService.asmx/GetCarsByDoors",
                data: data,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function(response) {
                    var cars = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d;
                    $('#output').empty();
                    for (var i = 0; i < cars.length; i++) {
                        $('#output').append('<p><strong>' + cars[i].Make + ' ' +
                                cars[i].Model + '</strong><br /> Year: ' +
                                cars[i].Year + '<br />Doors: ' +
                                cars[i].Doors + '<br />Colour: ' +
                                cars[i].Colour + '<br />Price: £' +
                                cars[i].Price + '</p>');
                    }
                },

                failure: function(msg) {

                    $('#output').text(msg);

                }
            });
        }

</script>

CarService.asmx:


/// <summary>
/// Summary description for CarService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
[ScriptService]
public class CarService : System.Web.Services.WebService
{
    List<Car> objCarList = new List<Car>{
                                    new Car{Make="Audi",Model="A4",Year=1995,Doors=5,Colour="Red",Price=2995f},
                                    new Car{Make="Ford",Model="Focus",Year=2002,Doors=5,Colour="Black",Price=3250f},
                                    new Car{Make="BMW",Model="5 Series",Year=2006,Doors=4,Colour="Grey",Price=24950f},
                                    new Car{Make="Renault",Model="Laguna",Year=2000,Doors=5,Colour="Red",Price=3995f},
                                    new Car{Make="Toyota",Model="Previa",Year=1998,Doors=5,Colour="Green",Price=2695f},
                                    new Car{Make="Mini",Model="Cooper",Year=2005,Doors=2,Colour="Grey",Price=9850f},
                                    new Car{Make="Mazda",Model="MX 5",Year=2003,Doors=2,Colour="Silver",Price=6995f},
                                    new Car{Make="Ford",Model="Fiesta",Year=2004,Doors=3,Colour="Red",Price=3759f},
                                    new Car{Make="Honda",Model="Accord",Year=1997,Doors=4,Colour="Silver",Price=1995f}
                                };

    [WebMethod]
    public List<Car> GetAllCars()
    {
        return objCarList;
    }

    [WebMethod]
    public List<Car> GetCarsByDoors(int doors, string Name)
    {
        var query = from c in objCarList
                    where c.Doors == doors
                    select c;

        return query.ToList();
    }

    [WebMethod]
    public string GetAllCarsInString()
    {
        JavaScriptSerializer objJS = new JavaScriptSerializer();
        return objJS.Serialize(objCarList);
    }
}

Car Class:


public class Car
{
    public string Make { get; set; }
    public string Model { get; set; }
    public int Year { get; set; }
    public int Doors { get; set; }
    public string Colour { get; set; }
    public float Price { get; set; }
}

Merge Statement

SQL Statement:


DECLARE @tblMergeOutput TABLE
 (
  ID                BIGINT IDENTITY(1,1)
  , MergeAction    NVARCHAR(100)
  , InsertedId    INT
  , DeletedId        INT
  , GroupID        INT
 )

 CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
 CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

 INSERT INTO #tblSource VALUES (1,1,'a11')
 INSERT INTO #tblSource VALUES (2,1,'a21')
 INSERT INTO #tblSource VALUES (3,1,'a31')
 INSERT INTO #tblSource VALUES (4,1,'a41')

 INSERT INTO #tblTarget VALUES (1,1,'a1')
 INSERT INTO #tblTarget VALUES (3,1,'a3')
 INSERT INTO #tblTarget VALUES (5,1,'a5')
 INSERT INTO #tblTarget VALUES (6,2,'b2')
 INSERT INTO #tblTarget VALUES (7,2,'b1')

 MERGE #tblTarget
 USING (
   SELECT *
   FROM #tblSource
  ) AS tblSource
   ON tblSource.GroupID = #tblTarget.GroupID
    AND tblSource.ID = #tblTarget.ID
 WHEN MATCHED THEN 
  UPDATE
  SET Name = tblSource.Name
 WHEN NOT MATCHED THEN
  INSERT (
     ID
     , GroupID
     , Name
    )
  VALUES ( 
     tblSource.ID
     , tblSource.GroupID
     , tblSource.Name
    )
 WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
  THEN DELETE
 OUTPUT 
  $action
  , inserted.Id
  , deleted.Id
  , tblSource.GroupID
 INTO @tblMergeOutput;

 SELECT * FROM #tblSource
 SELECT * FROM #tblTarget order by GroupID, ID
 SELECT * FROM @tblMergeOutput

 DROP TABLE #tblSource
 DROP TABLE #tblTarget

Target Table Output:


 ------------------
 ID  GroupID   Name
 ------------------
 1   1         a11
 2   1         a21
 3   1         a31
 4   1         a41
 6   2         b2
 7   2         b1
 ------------------

Merge Table Output:


 ----------------------------------------------------
 ID  Merge Action    InsertedID   DeletedID   GroupID
 ----------------------------------------------------
 1   INSERT          2            NULL        1
 2   INSERT          4            NULL        1
 3   UPDATE          1            1           1
 4   UPDATE          3            3           1
 5   DELETE          NULL         5           NULL
 ----------------------------------------------------

Wednesday, June 9, 2010

User Tree


DECLARE @tblUInfo TABLE (UserID INT, ParentUserID INT, UserName VARCHAR(100) )

INSERT INTO @tblUInfo VALUES (1,0, 'A')
INSERT INTO @tblUInfo VALUES (2,1, 'A1')
INSERT INTO @tblUInfo VALUES (3,1, 'A2')
INSERT INTO @tblUInfo VALUES (4,0, 'B')
INSERT INTO @tblUInfo VALUES (5,4, 'B1')
INSERT INTO @tblUInfo VALUES (6,4, 'B2')

;WITH cteNodes AS
(
 --initialization
 SELECT UserID, ParentUserID, ISNULL(UserName, '' ) AS UserTree, 0 AS UserLevel
 FROM @tblUInfo
 --WHERE ParentUserID IS NULL

 UNION ALL

 ----recursive execution
 SELECT P.UserID, N.ParentUserID, ISNULL( N.UserName, '' ), P.UserLevel + 1
 FROM cteNodes AS P
  INNER JOIN @tblUInfo AS N ON N.UserID = P.ParentUserID
)
--- SELECT * FROM cteNodes ORDER BY UserID, UserLevel OPTION (MaxRecursion 32767)

, cteEmployeePath AS
(
 SELECT tblUInfo.UserID
  , tblUInfo.UserName
  ,(
   SELECT CAST(UserTree AS VARCHAR ) + ':'
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
   ORDER BY UserLevel DESC
   FOR XML PATH('')
   ) AS UserTree
  ,(
   SELECT MAX(UserLevel)
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
  ) AS UserLevel
 FROM @tblUInfo AS tblUInfo
)
SELECT UserID, ( REPLICATE('--', UserLevel) + UserName ) AS UserName 
FROM cteEmployeePath 
ORDER BY UserTree 
OPTION (MaxRecursion 32767)