Asp.net C# , Sqlserver Professional: 2011

Monday 26 December 2011

Conversion in sqlserver when we cast or convert command

Thursday 1 December 2011

Remove whitespace from HTML

private static readonly Regex RegexBetweenTags = new Regex(@">(?! )\s+", RegexOptions.Compiled);
private static readonly Regex RegexLineBreaks = new Regex(@"([\n\s])+?(?<= {2,})<", RegexOptions.Compiled);
-----------------------------
protected override void Render(HtmlTextWriter writer)
{
using (HtmlTextWriter htmlwriter = new HtmlTextWriter(new System.IO.StringWriter()))
{
base.Render(htmlwriter);
string html = htmlwriter.InnerWriter.ToString();
writer.Write(RemoveWhitespaceFromHtml(html));
}
}
public static string RemoveWhitespaceFromHtml(string html)
{
html = RegexBetweenTags.Replace(html, ">");
html = RegexLineBreaks.Replace(html, "<");

return html.Trim();
}

Friday 25 November 2011

Compress ViewState

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.IO.Compression;

/// <summary>
/// Summary description for ViewStateCompressor
/// </summary>
public class ViewStateCompressor
{
public ViewStateCompressor()
{
//
// TODO: Add constructor logic here
//
}
public static byte[] CompressViewState(byte[] uncompData)
{
using (MemoryStream mem = new MemoryStream())
{
CompressionMode mode = CompressionMode.Compress;
// Use the newly created memory stream for the compressed data.
using (GZipStream gzip = new GZipStream(mem, mode, true))
{
//Writes compressed byte to the underlying
//stream from the specified byte array.
gzip.Write(uncompData, 0, uncompData.Length);
}
return mem.ToArray();
}
}

public static byte[] DecompressViewState(byte[] compData)
{
GZipStream gzip;
using (MemoryStream inputMem = new MemoryStream())
{
inputMem.Write(compData, 0, compData.Length);
// Reset the memory stream position to begin decompression.
inputMem.Position = 0;
CompressionMode mode = CompressionMode.Decompress;
gzip = new GZipStream(inputMem, mode, true);

using (MemoryStream outputMem = new MemoryStream())
{
// Read 1024 bytes at a time
byte[] buf = new byte[1024];
int byteRead = -1;
byteRead = gzip.Read(buf, 0, buf.Length);
while (byteRead > 0)
{
//write to memory stream
outputMem.Write(buf, 0, byteRead);
byteRead = gzip.Read(buf, 0, buf.Length);
}
gzip.Close();
return outputMem.ToArray();
}
}
}
}
-----------------------------in page
pages smartNavigation="true" enableEventValidation="false" maxPageStateFieldLength="5000" viewStateEncryptionMode="Never"
-----------------------------in page cs

protected override void SavePageStateToPersistenceMedium(object pageViewState)
{
LosFormatter losformatter = new LosFormatter();
StringWriter sw = new StringWriter();
losformatter.Serialize(sw, pageViewState);
string viewStateString = sw.ToString();
byte[] b = Convert.FromBase64String(viewStateString);
b = ViewStateCompressor.CompressViewState(b);
ClientScript.RegisterHiddenField("__CUSTOMVIEWSTATE", Convert.ToBase64String(b));
}

// Deserialize view state
protected override object LoadPageStateFromPersistenceMedium()
{
string custState = Request.Form["__CUSTOMVIEWSTATE"];
byte[] b = Convert.FromBase64String(custState);
b = ViewStateCompressor.DecompressViewState(b);
LosFormatter losformatter = new LosFormatter();
return losformatter.Deserialize(Convert.ToBase64String(b));
}

Wednesday 26 October 2011

Checking child nodes when checking the parent node

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Test</title>

<script language="javascript" type="text/javascript">
function OnTreeClick(evt)
{
var src = window.event != window.undefined ? window.event.srcElement : evt.target;
var isChkBoxClick = (src.tagName.toLowerCase() == "input" && src.type == "checkbox");
if(isChkBoxClick)
{
var parentTable = GetParentByTagName("table", src);
var nxtSibling = parentTable.nextSibling;
if(nxtSibling && nxtSibling.nodeType == 1)
{
if(nxtSibling.tagName.toLowerCase() == "div") //if node has children
{
CheckUncheckChildren(parentTable.nextSibling, src.checked);
}
}
CheckUncheckParents(src, src.checked);
}
}
function CheckUncheckChildren(childContainer, check)
{
var childChkBoxes = childContainer.getElementsByTagName("input");
var childChkBoxCount = childChkBoxes.length;
for(var i = 0; i<childChkBoxCount; i++)
{
childChkBoxes[i].checked = check;
}
}
function CheckUncheckParents(srcChild, check)
{
var parentDiv = GetParentByTagName("div", srcChild);
var parentNodeTable = parentDiv.previousSibling;

if(parentNodeTable)
{
var checkUncheckSwitch;

if(check)
{
var isAllSiblingsChecked = AreAllSiblingsChecked(srcChild);
if(isAllSiblingsChecked)
checkUncheckSwitch = true;
else
return;
}
else
{
checkUncheckSwitch = false;
}

var inpElemsInParentTable = parentNodeTable.getElementsByTagName("input");
if(inpElemsInParentTable.length > 0)
{
var parentNodeChkBox = inpElemsInParentTable[0];
parentNodeChkBox.checked = checkUncheckSwitch;

CheckUncheckParents(parentNodeChkBox, checkUncheckSwitch);
}
}
}
function AreAllSiblingsChecked(chkBox)
{
var parentDiv = GetParentByTagName("div", chkBox);
var childCount = parentDiv.childNodes.length;
for(var i=0; i<childCount; i++)
{
if(parentDiv.childNodes[i].nodeType == 1)
{
if(parentDiv.childNodes[i].tagName.toLowerCase() =="table")
{
var prevChkBox = parentDiv.childNodes[i].getElementsByTagName("input")[0];

if(!prevChkBox.checked)
{
return false;
}
}
}
}
return true;
}
function GetParentByTagName(parentTagName, childElementObj)
{
var parent = childElementObj.parentNode;
while(parent.tagName.toLowerCase() != parentTagName.toLowerCase())
{
parent = parent.parentNode;
}
return parent;
}
</script>

</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TreeView ID="TreeViewDemo" runat="server" ExpandDepth="0" ShowCheckBoxes="All">
<Nodes>
<asp:TreeNode NavigateUrl="" Text="P" Value="P">
<asp:TreeNode NavigateUrl="" Text="P:C1" Value="C1"></asp:TreeNode>
<asp:TreeNode NavigateUrl="" Text="P:C2" Value="C2"></asp:TreeNode>
</asp:TreeNode>
<asp:TreeNode NavigateUrl="" Text="P1" Value="P1">
<asp:TreeNode NavigateUrl="" Text="P1:C1" Value="P1:C1"></asp:TreeNode>
<asp:TreeNode NavigateUrl="" Text="P1:C2" Value="P1:C2"></asp:TreeNode>
</asp:TreeNode>
<asp:TreeNode NavigateUrl="" Text="P2" Value="P2">
<asp:TreeNode NavigateUrl="" Text="P2:C1" Value="P2:C1"></asp:TreeNode>
<asp:TreeNode NavigateUrl="" Text="P2:C2" Value="P2:C2"></asp:TreeNode>
</asp:TreeNode>
</Nodes>
</asp:TreeView>
</div>
</form>
</body>
</html>

------------------------------------------------------------------

protected void Page_Load(object sender, EventArgs e)

{

TreeViewDemo.Attributes.Add("onclick", "OnTreeClick(event)");

}

Monday 24 October 2011

Find Latitude And Longitude From C# Asp.net

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Net;
using System.Web.UI;

namespace GoogleGeocoder
{
public interface ISpatialCoordinate
{
decimal Latitude {get; set; }
decimal Longitude {get; set; }
}
public struct Coordinate : ISpatialCoordinate
{
private decimal _latitude;
private decimal _longitude;

public Coordinate(decimal latitude, decimal longitude)
{
_latitude = latitude;
_longitude = longitude;
}

#region ISpatialCoordinate Members

public decimal Latitude
{
get
{
return _latitude;
}
set
{
this._latitude = value;
}
}

public decimal Longitude
{
get
{
return _longitude;
}
set
{
this._longitude = value;
}
}

#endregion
}

public class Geocode
{
private const string _googleUri = "http://maps.google.com/maps/geo?q=";
private const string _googleKey = "ABQIAAAAAbmewSRic45gmeA3SqPU2xTN7OoKn4WqNHLTV0WVkMCPFkG3kxRiF6l_Jlmlap7r6S592BJ_XDSJtg";
private const string _outputType = "csv"; // Available options: csv, xml, kml, json

private static Uri GetGeocodeUri(string address)
{
address = HttpUtility.UrlEncode(address);
return new Uri(String.Format("{0}{1}&output={2}&key={3}", _googleUri, address, _outputType, _googleKey));
}
public static Coordinate GetCoordinates(string address)
{
WebClient client = new WebClient();
Uri uri = GetGeocodeUri(address);
string[] geocodeInfo = client.DownloadString(uri).Split(',');
return new Coordinate(Convert.ToDecimal(geocodeInfo[2]), Convert.ToDecimal(geocodeInfo[3]));
}

}
}
------------------------------------
using GoogleGeocoder;

Coordinate coordinate = Geocode.GetCoordinates("limkon");
decimal latitude = coordinate.Latitude;
decimal longitude = coordinate.Longitude;

Convert dd/MM/yyyy to mm/dd/yyyy in c# asp.net

string UrDate = "YourDate in dd/mm/yyyy"
System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
dateInfo.ShortDatePattern = "dd/MM/yyyy";
DateTime validDate = Convert.ToDateTime(UrDate, dateInfo);

Tuesday 20 September 2011

COALESCE in sqlserver

DECLARE @str NVARCHAR(Max)
SELECT @str = COALESCE(@str + ';', '') + columnname FROM tablename
SELECT @str

Tuesday 13 September 2011

Share image with facebook in asp.net

Design page
------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Admin_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Test Image share with facebook </title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblShare" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
------------------------
Code page
-----------------------

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class Admin_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string str = "";
str = " <div class='a2a_kit a2a_default_style'><a class='a2a_button_facebook'></a><a class='a2a_button_twitter'></a><a class='a2a_button_email'></a> </div> <script type='text/javascript'> var a2a_config = a2a_config || {};a2a_config.linkname = 'Capture America';a2a_config.linkurl = 'http://www.floripaweb.net/images/DOTNET%20logo.png'; </script> <script type='text/javascript' src='http://static.addtoany.com/menu/page.js'></script>";
lblShare.Text = str;
}
}

How Select a perticular item from checkListBox

"CheckBoxListName".FindByValue("Value").Selected = true;

PIVOT Table Example In Sqlserver

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
------------------------------------------

create table Test1 ( [No] int, [ID] int, Date Datetime, Value decimal (38,2))
------------------------------------------
insert into Test1 values (1, 1001, '05/01/2009', 101.00)
insert into Test1 values (1, 1001, '05/15/2009', 102.00)
insert into Test1 values (1, 1001, '05/20/2009', 105.00)
insert into Test1 values (2, 1001, '05/01/2009', 41.00)
insert into Test1 values (2, 1001, '05/15/2009', 44.00)
insert into Test1 values (3, 1001, '06/01/2009', 330.00)
----------------------------------------
select * from Test1
--------------------------------------
SELECT [No] , [ID] ,[05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]
FROM (
SELECT [No], [ID], [Date], [Value]
FROM Test1) up
PIVOT ( sum([Value]) FOR [Date] in ([05/01/2009] ,[05/15/2009] ,[05/20/2009] ,[06/01/2009]) )AS pvt

Friday 1 July 2011

SQL Tuning/SQL Optimization Techniques

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'.
For Example:
Write the query as
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of:
SELECT * FROM student_details;
2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

 SELECT subject, count(subject) 

FROM student_details 

WHERE subject != 'Science' 

AND subject != 'Maths' 

GROUP BY subject;

Instead of:

 SELECT subject, count(subject) 

FROM student_details 

GROUP BY subject 

HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

 SELECT name 

FROM employee 

WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 

FROM employee_details) 

AND dept = 'Electronics';

Instead of:

 SELECT name 

FROM employee

WHERE salary = (SELECT MAX(salary) FROM employee_details) 

AND age = (SELECT MAX(age) FROM employee_details) 

AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.
For Example: Write the query as

 Select * from product p 

where EXISTS 

(select * from order_items o 

where o.product_id = p.product_id)

Instead of:

 Select * from product p 

where product_id IN 

(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

 SELECT d.dept_id, d.dept 

FROM dept d 

WHERE EXISTS ( SELECT 'X' 

FROM employee e 

WHERE e.dept = d.dept);

Instead of:

 SELECT DISTINCT d.dept_id, d.dept 

FROM dept d,employee e 

WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

 SELECT id, first_name 

FROM student_details_class10 

UNION ALL 

SELECT id, first_name 

FROM sports_team;

Instead of:

 SELECT id, first_name, subject 

FROM student_details_class10 

UNION 

SELECT id, first_name 

FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as

 SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:
SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as

 SELECT id, first_name, age 

FROM student_details 

WHERE first_name LIKE 'Chan%';

Instead of:

 SELECT id, first_name, age 

FROM student_details 

WHERE SUBSTR(first_name,1,3) = 'Cha';

Write the query as

 SELECT id, first_name, age 

FROM student_details 

WHERE first_name LIKE NVL ( :name, '%');

Instead of:

 SELECT id, first_name, age 

FROM student_details 

WHERE first_name = NVL ( :name, first_name);

Write the query as

 SELECT product_id, product_name 

FROM product 

WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

 SELECT product_id, product_name 

FROM product 

WHERE unit_price >= MAX(unit_price) 

and unit_price <= MIN(unit_price)

Write the query as

 SELECT id, name, salary 

FROM employee 

WHERE dept = 'Electronics' 

AND location = 'Bangalore';

Instead of:

 SELECT id, name, salary 

FROM employee 

WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.
Write the query as

 SELECT id, name, salary 

FROM employee 

WHERE salary < 25000;

Instead of:

 SELECT id, name, salary 

FROM employee 

WHERE salary + 10000 < 35000;

Write the query as

 SELECT id, first_name, age 

FROM student_details 

WHERE age > 10;

Instead of:

 SELECT id, first_name, age 

FROM student_details 

WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively.
DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

 SELECT id 

FROM employee 

WHERE name LIKE 'Ramesh%' 

and location = 'Bangalore';

Instead of:

 SELECT DECODE(location,'Bangalore',id,NULL) id

FROM employee 

WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.
10) To write queries which provide efficient performance follow the general SQL standard rules.
a) Use single case for all SQL verbs

b) Begin all SQL verbs on a new line

c) Separate all words with a single space

d) Right or left aligning verbs within the initial SQL verb

Monday 13 June 2011

Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salaryFROM employee ORDER BY salary DESC) aORDER BY salary
where n > 1 (n is always greater than one)

with cte as
(
select salary ,ROW_NUMBER() over (order by salary desc) as 'rownum'
from employee
)

select salary from cte where rownum = n (n is always greater than one)

Count Duplicate Records – Rows

SELECT YourColumn, COUNT(*) TotalCount FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
For Delete
------
delete
FROM YourTable
WHERE ID NOT IN (SELECT MAX(ID) FROM YourTable GROUP BY YourColumn) and YourColumn=0

delete

FROM Exam_Backup

WHERE ID NOT IN (SELECT MAX(ID) FROM Exam_Backup GROUP BY QuestionNo) and optionName=0

WITH CTE

WITH CTE (last_name,first_name,dcount)

(

SELECT last_name,first_name ,

ROW_NUMBER() OVER (PARTITION BY last_name,first_name ORDER BY last_name) AS dcount

FROM tbl_member

)

SELECT * FROM CTE WHERE dcount > 1

Monday 23 May 2011

Accordion In Repeter

Table Structure:
Header Table : COLUMN_NAME DATA_TYPE IS_NULLABLE MAXIMUM_LENGTH
Id int No Null
Header nvarchar No 50
------------------------------------------------------------------------------------
Detail Table : DetailId int No Null
HeaderId int No Null
Detail nvarchar No 50
------------------------------------------------------------------------------------
Css :
#wrapper {
width: 800px;
margin-left: auto;
margin-right: auto;
}
.accordionButton {
width: 800px;
float: left;
background: #EBE8D4;
border-bottom: 1px solid #FFFFFF;
cursor: pointer;
}
.accordionContent {
width: 800px;
float: left;
background: #C5D5FC;
display: none;
}
------------------------------------------------------------------------------------

Javascript :

$(document).ready(function() {

$('div.accordionButton').click(function() {

$('div.accordionContent').slideUp('normal');

$(this).next().slideDown('normal');

});

$("div.accordionContent").hide();

});

-----------------------------------------------------------------------------------

Default.aspx :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="AccordionInRepeter_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

</div>

<asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_ItemDataBound">

<asp:Panel ID="Header" runat="server" CssClass="accordionButton" Width="600px">

<asp:Image ID="Image1" runat="server" />

<asp:Label ID="lblHeader" runat="server" Text="Label" Font-Bold="True"></asp:Label>

</asp:Panel>

<asp:Panel ID="Content" runat="server" CssClass="accordionContent" Width="600px">

<asp:Label ID="lblDetails" runat="server" Text="Label"></asp:Label>

<asp:Panel ID="PanelSaveAndUpdate" runat="server" style="text-align:center" >

</asp:Panel>

</ItemTemplate>

</asp:Repeater>

</div>

</div>

</form>

</body>

</html>

-----------------------------------------------------------------------------

Default.aspx.cs

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class AccordionInRepeter_Default : System.Web.UI.Page

{

string strQuery = "";

SqlConnection con;

SqlDataAdapter da;

DataTable DtHeader;

protected void Page_Load(object sender, EventArgs e)

{

con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=" + Server.MapPath("App_Data\\Database.mdf").ToString() + ";Integrated Security=True;User Instance=True");

if (con.State != ConnectionState.Open)

{

con.Open();

}

if (!IsPostBack)

{

populateRepeter();

}

private void populateRepeter()

{

strQuery = "Select id,Header from Header order by id asc";

da = new SqlDataAdapter(strQuery, con);

DtHeader = new DataTable();

da.Fill(DtHeader);

Repeater1.DataSource = DtHeader;

Repeater1.DataBind();

}

protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)

{

if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)

{

Label lblHeader = (Label)e.Item.FindControl("lblHeader");

Label lblDetails = (Label)e.Item.FindControl("lblDetails");

Image Image1 = (Image)e.Item.FindControl("Image1");

Panel PanelSaveAndUpdate = (Panel)e.Item.FindControl("PanelSaveAndUpdate");

if (lblHeader != null)

{

strQuery = "Select Detailid,Detail from Detail where HeaderId=" + DtHeader.Rows[e.Item.ItemIndex]["Id"].ToString() + "";

da = new SqlDataAdapter(strQuery, con);

DataTable dtDetail = new DataTable();

da.Fill(dtDetail);

string strDetail = "";

if (dtDetail.Rows.Count > 0)

{

Image1.ImageUrl = "Plus.ico";

lblHeader.Text = DtHeader.Rows[e.Item.ItemIndex]["Header"].ToString();

for (int i = 0; i < dtDetail.Rows.Count; i++)

{

strDetail += " <span>        </span>" + dtDetail.Rows[i]["Detail"].ToString() + "</br>";

}

PanelSaveAndUpdate.Visible = true;

}

else

{

lblHeader.Text = " <span>     </span>" + DtHeader.Rows[e.Item.ItemIndex]["Header"].ToString();

PanelSaveAndUpdate.Visible = false;

}

lblDetails.Text = strDetail;

}

-------------------------------------------------------------------------------

Thursday 31 March 2011

How To Select,Insert And Update Data From XML File

----------------XML file XMLFile.xml--------------------------
<?xml version="1.0" encoding="utf-8"?>
<DocumentElement>
  <Person>
   <id>100</id>
   <Name>Sudipta</Name>
   <City>Kol</City>
   <Age>1212</Age>
  </Person>
  <Person>
   <id>101</id>
   <Name>Sudipta123</Name>
   <City>kol</City>
   <Age>26</Age>
  </Person>
  <Person>
   <id>10</id>
   <Name>Kol</Name>
   <City>Kola</City>
   <Age>10</Age>
  </Person>
</DocumentElement>
----------------Design Page : Default.aspx----------------------

<div style="font-family: Verdana; font-size: 12px">
   <table style="width: 80%" cellpadding="2" cellspacing="2" align="center">
   <tr>
   <td>
   Id
   </td>
   <td>
   <asp:TextBox ID="txtId" runat="server"></asp:TextBox>
   </td>
   <td>
   Name
   </td>
   <td>
   <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
   </td>
   <td>
   City
   </td>
   <td>
   <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
   </td>
   <td>
   Age
   </td>
   <td>
   <asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
   </td>
   <td>
   <asp:Button ID="btnInsertIntoXml" runat="server" Text="Insert Into Xml"
   onclick="btnInsertIntoXml_Click" />
   </td>
   </tr>
   </table>

   <table style="width: 80%" cellpadding="2" cellspacing="2" align="center">
   <tr>
   <td>
   Name
   </td>
   <td>
   <asp:TextBox ID="txtUpdateName" runat="server"></asp:TextBox>
   </td>
   <td>
   City
   </td>
   <td>
   <asp:TextBox ID="txtUpdateCity" runat="server"></asp:TextBox>
   </td>
   <td>
   Age
   </td>
   <td>
   <asp:TextBox ID="txtUpdateAge" runat="server"></asp:TextBox>
   </td>
   <td>
   <asp:HiddenField ID="hidId" runat="server" />
   <asp:Button ID="btnUpdateXml" runat="server" Text="Update Xml " OnClick="btnUpdateXml_Click" />
   </td>
   </tr>
   <tr>
   <td style="width: 100%;" colspan="7">
   <asp:Repeater ID="Repeater1" runat="server"
   onitemdatabound="Repeater1_ItemDataBound"
   onitemcommand="Repeater1_ItemCommand">
   <HeaderTemplate>
   <table style="width: 99%" cellpadding="4" cellspacing="1">
   <tr style="background-color: #E0E1E1">
   <td style="width: 50%; font-weight: bold">
   Name
   </td>
   <td style="width: 35%; font-weight: bold">
   City
   </td>
   <td style="width: 9%; font-weight: bold">
   Age
   </td>
   <td style="width: 5%; font-weight: bold">
   Edit
   </td>
   </tr>
   </HeaderTemplate>
   <ItemTemplate>
   <tr>
   <td style="width: 50%">
   <asp:Label ID="lblName" runat="server" Text="Label"></asp:Label>
   </td>
   <td style="width: 35%">
   <asp:Label ID="lblCity" runat="server" Text="Label"></asp:Label>
   </td>
   <td style="width: 9%">
   <asp:Label ID="lblAge" runat="server" Text="Label"></asp:Label>
   </td>
   <td style="width: 5%">
   <asp:LinkButton ID="lnkEdit" runat="server" style="text-decoration:none;">Edit</asp:LinkButton>
   </td>
   </tr>
   </ItemTemplate>
   <AlternatingItemTemplate>
   <tr style="background-color: #E0E1E1">
   <td style="width: 50%">
   <asp:Label ID="lblName" runat="server" Text="Label"></asp:Label>
   </td>
   <td style="width: 35%">
   <asp:Label ID="lblCity" runat="server" Text="Label"></asp:Label>
   </td>
   <td style="width: 9%">
   <asp:Label ID="lblAge" runat="server" Text="Label"></asp:Label>
   </td>
   <td style="width: 5%; " >
   <asp:LinkButton ID="lnkEdit" runat="server" style="text-decoration:none;" >Edit</asp:LinkButton>
   </td>
   </tr>
   </AlternatingItemTemplate>
   <FooterTemplate>
   </table>
   </FooterTemplate>
   </asp:Repeater>
   </td>
   </tr>
   </table>
   </div>
-------------Cs Page :Default.aspx.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml;
using System.Xml.XPath;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
   DataSet ds = new DataSet("XmlData");
   DataTable dt = new DataTable();
   protected void Page_Load(object sender, EventArgs e)
   {
   if (!IsPostBack)
   {
   PopulateRepeter();
   }
   }

   private void PopulateRepeter()
   {
   ds.ReadXml(Server.MapPath("XMLFile.xml"));
   dt = ds.Tables[0];
   Repeater1.DataSource = dt;
   Repeater1.DataBind();
   }

   protected void btnUpdateXml_Click(object sender, EventArgs e)
   {
   XmlDocument xmlDoc = new XmlDocument();
   xmlDoc.Load(Server.MapPath("XMLFile.xml"));
   XmlNodeList nodeList = xmlDoc.SelectNodes("DocumentElement/Person[id='" + hidId.Value.ToString() + "']");
   nodeList[0].ChildNodes[1].InnerText = txtUpdateName.Text;
   nodeList[0].ChildNodes[2].InnerText = txtUpdateCity.Text;
   nodeList[0].ChildNodes[3].InnerText = txtUpdateAge.Text;
   xmlDoc.Save(Server.MapPath("XMLFile.xml"));
   PopulateRepeter();
   }
   protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
   {
   if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
   {
   Label lblName = (Label)e.Item.FindControl("lblName");
   if (lblName != null)
   {
   lblName.Text = dt.Rows[e.Item.ItemIndex]["Name"].ToString();
   }
   Label lblCity = (Label)e.Item.FindControl("lblCity");
   if (lblCity != null)
   {
   lblCity.Text = dt.Rows[e.Item.ItemIndex]["City"].ToString();
   }
   Label lblAge = (Label)e.Item.FindControl("lblAge");
   if (lblAge != null)
   {
   lblAge.Text = dt.Rows[e.Item.ItemIndex]["Age"].ToString();
   }
   LinkButton lnkEdit = (LinkButton)e.Item.FindControl("lnkEdit");
   if (lnkEdit != null)
   {
   lnkEdit.CommandName = "Edit";
   lnkEdit.CommandArgument = dt.Rows[e.Item.ItemIndex]["Id"].ToString() + "," + dt.Rows[e.Item.ItemIndex]["Name"].ToString() + "," + dt.Rows[e.Item.ItemIndex]["City"].ToString() + "," + dt.Rows[e.Item.ItemIndex]["Age"].ToString();
   }

   }
   }
   protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
   {
   string[] field = e.CommandArgument.ToString().Split((",").ToCharArray());
   if (e.CommandName == "Edit")
   {
   hidId.Value = field[0].ToString();
   txtUpdateName.Text = field[1].ToString();
   txtUpdateCity.Text = field[2].ToString();
   txtUpdateAge.Text = field[3].ToString();

   }

   }
   protected void btnInsertIntoXml_Click(object sender, EventArgs e)
   {
   XmlDocument doc = new XmlDocument();
   doc.Load(Server.MapPath("XMLFile.xml"));
   XmlNode node = doc.CreateNode(XmlNodeType.Element, "Person", null);
   XmlNode Id = doc.CreateElement("id");
   Id.InnerText = txtId.Text.Trim();
   XmlNode Name = doc.CreateElement("Name");
   Name.InnerText = txtName.Text.Trim();
   XmlNode City = doc.CreateElement("City");
   City.InnerText = txtCity.Text.Trim();
   XmlNode Age = doc.CreateElement("Age");
   Age.InnerText = txtAge.Text.Trim();
   node.AppendChild(Id);
   node.AppendChild(Name);
   node.AppendChild(City);
   node.AppendChild(Age);
   doc.DocumentElement.AppendChild(node);
   doc.PreserveWhitespace = true;
   doc.Save(Server.MapPath("XMLFile.xml"));
   PopulateRepeter();
   }
}

Friday 25 March 2011

How To Use JSON Datasource (in WebService) in Asp.net

JSON
JSON (an acronym for JavaScript Object) is a lightweight text-based open standard designed for human-readable data interchange. It is derived from the JavaScript programming language for representing simple data structures and associative arrays, called objects. Despite its relationship to JavaScript, it is language-independent, with parsers available for most programming languages.
The JSON format was originally specified by Douglas Crockford, The official Internet media type for JSON is application/json. The JSON filename extension is .json.
---------------------------------------------------------
Efficiency
JSON is primarily used for communicating data over the Internet, but has certain inherent characteristics that may limit its efficiency for this purpose. Most of the limitations are general limitations of textual data formats and also apply to XML and YAML. For example, despite typically being generated by an algorithm (by machine), parsing must be accomplished on a character-by-character basis. Additionally, the standard has no provision for data compression, interning of strings, or object references. Compression can, of course, be applied to the JSON formatted data (but the decompressed output typically still requires further full parsing for recognizable keywords, tags and delimiters).
---------------How To Implement  JSON ----------------
Download jquery-1.3.2.min.js  and past it in the js folder in your web application
http://code.google.com/p/jqueryjs/downloads/detail?name=jquery-1.3.2.min.js&downloadBtn
--------------- Page Design
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
   <title>Jeson Test</title>
   <script type="text/javascript" src="js/jquery-1.3.2.min.js"></script>
   <script type="text/javascript">
   function testJson() {
   $.ajax({
   type: "POST",
   url: "Json.asmx/TestJSON",
   data: "{}",
   contentType: "application/json; charset=utf-8",
   dataType: "json",
   success: function(msg) {
   $("#jsonResponse").html(msg);
   var data = eval("(" + msg.d + ")");
   var t = "<table border=1> <tr>" +
   "<td> <strong>Name</strong></td> <td> " +
   "<strong>Company</strong></td> <td> " +
   "<strong>Address</strong></td> <td> " +
   "<strong>Phone</strong></td> <td> " +
   "<strong>Country</strong></td> </tr> ";
   jQuery.each(data, function(rec) {
   t = t + " <tr> <td> " + this.Name + "</td> <td> " +
   this.Company + "</td> <td> " + this.Address +
   "</td> <td> " + this.Phone + "</td> <td> " + this.Country + "</td> </tr> ";
   });
   t = t + " </table> ";
   $("#jsonDiv").html(t);
   },
   error: function(msg) {
   }
   });
   };
   </script>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   <input id="testjson" type="button" value="Test JSON Call" onclick="testJson()" />
   <br />
   <div id="jsonDiv" style="display: block;">
   </div>
   </div>
   </form>
</body>
</html>
----------------Web service
using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
public class Json : System.Web.Services.WebService {
   public Json () {}
   [WebMethod]
   [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
   public string TestJSON()
   {
   Employee[] e = new Employee[3];
   e[0] = new Employee();
   e[0].Name = "Sudipta Sanyal";
   e[0].Company = "Esolz";
   e[0].Address = "Kolkata";
   e[0].Phone = "1204675";
   e[0].Country = "India";
   e[1] = new Employee();
   e[1].Name = "Protom Nandi";
   e[1].Company = "Esolz";
   e[1].Address = "Kolkata";
   e[1].Phone = "1204675";
   e[1].Country = "India";
   e[2] = new Employee();
   e[2].Name = "Tanmay Chak";
   e[2].Company = "Esolz";
   e[2].Address = "Kolkata";
   e[2].Phone = "1204675";
   e[2].Country = "India";
   return new JavaScriptSerializer().Serialize(e);
   }
}

----------------Employee Class

Asp.net C# , Sqlserver Professional

Monday 26 December 2011

Conversion in sqlserver when we cast or convert command

Thursday 1 December 2011

Remove whitespace from HTML

Friday 25 November 2011

Compress ViewState

Wednesday 26 October 2011

Checking child nodes when checking the parent node

Monday 24 October 2011

Find Latitude And Longitude From C# Asp.net

Convert dd/MM/yyyy to mm/dd/yyyy in c# asp.net

Tuesday 20 September 2011

COALESCE in sqlserver

Tuesday 13 September 2011

Share image with facebook in asp.net

How Select a perticular item from checkListBox

PIVOT Table Example In Sqlserver

Friday 1 July 2011

SQL Tuning/SQL Optimization Techniques

Monday 13 June 2011

Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

Count Duplicate Records – Rows

Monday 23 May 2011

Accordion In Repeter

Thursday 31 March 2011

How To Select,Insert And Update Data From XML File

Friday 25 March 2011

How To Use JSON Datasource (in WebService) in Asp.net

Get all non-clustered indexes

Google Website Translator Gadget

Followers