Friday, June 11, 2010

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)

Tuesday, April 20, 2010

Pivot / UnPivot Example

SQL Query :


DECLARE @Tbl TABLE(Col1 INT, A VARCHAR(1), B VARCHAR(1), C VARCHAR(1) )

INSERT INTO @Tbl
SELECT 1, 'a', 'b', 'c'
UNION
SELECT 2, 'd', 'e', 'f'
UNION
SELECT 3, 'g', 'h', 'i'

SELECT
*
FROM
(
SELECT *
FROM
(
SELECT
Col1 AS Cols
, A AS [A(N1)]
, B AS [B(N2)]
, C AS [C(N3)]
FROM @Tbl
) A
UNPIVOT
( [Value] For [Col1] IN (
[A(N1)]
, [B(N2)]
, [C(N3)]
) ) AS upvt
) B
PIVOT
(
MIN ( [Value])
FOR [Cols]
IN ([1], [2], [3])
) AS pvt;

Input :

----------------
Col1 A B C
----------------
1 a b c
2 d e f
3 g h i
----------------

OutPut :
    
-------------------
Col1 1 2 3
-------------------
A(N1) a d g
B(N2) b e h
C(N3) c f i
-------------------

Friday, April 2, 2010

Search Mutipal Keyword in Multipal Field


BEGIN --Key Words
DECLARE @String as VARCHAR(500)
SET @String = 'Kansas,64108'
END

BEGIN --Sample Data
DECLARE @UserMaster TABLE (UserName VARCHAR(50), Address VARCHAR(50), City VARCHAR(50), Zip VARCHAR(50))
INSERT INTO @UserMaster VALUES ('aa', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('bb', '1810 Cherry Street', 'New york City', '64119')
INSERT INTO @UserMaster VALUES ('cc', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('dd', '1810 Cherry Street', 'New york City', '64119')
INSERT INTO @UserMaster VALUES ('ee', '1810 Cherry Street', 'Kansas City', '64108')
INSERT INTO @UserMaster VALUES ('ff', '1810 Cherry Street', 'New york City', '64119')
END

BEGIN --Return Result
DECLARE @XML XML
SET @XML ='<Root><SearchKey>' + REPLACE( @String, ',', '</SearchKey><SearchKey>') + '</SearchKey></Root>'

SELECT UserMaster.*, SearchInfo.SearchKey
FROM @UserMaster AS UserMaster
INNER JOIN (
SELECT x.Name.value('.', 'VARCHAR(MAX)') AS SearchKey
FROM @XML.nodes('/Root/SearchKey')x(Name)
) SearchInfo ON UserMaster.City Like '%' + SearchInfo.SearchKey + '%'
OR UserMaster.Zip Like '%' + SearchInfo.SearchKey + '%'
OR UserMaster.Address Like '%' + SearchInfo.SearchKey + '%'
END