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)