Interview Question: August 2014

CREATE TABLE [dbo].[District_mst](

[state_id] [int] NULL,

[district_id] [int] NULL,

[district] [varchar](50) NULL

) ON [PRIMARY]
CREATE TABLE [dbo].[State_mst](

[state_id] [int] NULL,

[state] [varchar](50) NULL

) ON [PRIMARY]

=======================

using System;
using System.Collections;
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.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{

SqlConnection conn = new SqlConnection("Data Source=ADMIN-PC;Initial Catalog=test; Integrated Security=True");

protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
PopulateTreeview();
foreach (TreeNode node in tvTables.Nodes)
{
node.Checked = true;
if (node.ChildNodes.Count > 0)
checkChildNode(node);
}

}
}
private void checkChildNode(TreeNode node)
{
foreach (TreeNode chNode in node.ChildNodes)
{
chNode.Checked = true;
if (chNode.ChildNodes.Count > 0)
checkChildNode(chNode);
}
}

private void PopulateTreeview()
{
try
{
DataSet ds = new DataSet();
DataTable dtparent = new DataTable();
DataTable dtchild = new DataTable();
dtparent = FillParentTable();
dtparent.TableName = "A";
dtchild = FillChildTable();
dtchild.TableName = "B";

ds.Tables.Add(dtparent);
ds.Tables.Add(dtchild);

ds.Relations.Add("children", dtparent.Columns["state_id"], dtchild.
Columns["state_id"]);

if (ds.Tables[0].Rows.Count > 0)
{
tvTables.Nodes.Clear();

foreach (DataRow masterRow in ds.Tables[0].Rows)
{
TreeNode masterNode = new TreeNode((string)masterRow["state"],
Convert.ToString(masterRow["state_id"]));
tvTables.Nodes.Add(masterNode);
tvTables.CollapseAll();
foreach (DataRow childRow in masterRow.GetChildRows("Children"))
{
TreeNode childNode = new TreeNode((string)childRow["district"],
Convert.ToString(childRow["state_id"]));
masterNode.ChildNodes.Add(childNode);
childNode.Value = Convert.ToString(childRow["district_id"]);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Unable to populate treeview" + ex.Message);
}
}

private DataTable FillParentTable()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
conn.Open();
string cmdstr = "Select * from State_mst";
SqlCommand cmd = new SqlCommand(cmdstr, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
cmd.ExecuteNonQuery();
dt = ds.Tables[0];
conn.Close();
dtnew = dt.Copy();

return dtnew;
}

private DataTable FillChildTable()
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
conn.Open();
string cmdstr = "Select * from District_mst";
SqlCommand cmd = new SqlCommand(cmdstr, conn);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
cmd.ExecuteNonQuery();
dt = ds.Tables[0];
conn.Close();
dtnew = dt.Copy();
return dtnew;
}
protected void btn_save_Click(object sender, EventArgs e)
{
Response.Write("<b>Seleted values of nodes:</b><br/>");
foreach (TreeNode item in this.tvTables.CheckedNodes)
{

conn.Open();
string str = "Insert into Category1 values ('" + item.Text + "')";
SqlCommand com = new SqlCommand(str, conn);
com.ExecuteNonQuery();
conn.Close();
Response.Write(item.Value + "<br/>");
}
}
}

====================

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"

CodeFile="Default.aspx.cs" Inherits="_Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">

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) {

if (src.checked == true) {

var nodeText = getNextSibling(src).innerText || getNextSibling(src).innerHTML;

var nodeValue = GetNodeValue(getNextSibling(src));

document.getElementById("label").innerHTML += nodeText + ",";

}

else {

var nodeText = getNextSibling(src).innerText || getNextSibling(src).innerHTML;

var nodeValue = GetNodeValue(getNextSibling(src));

var val = document.getElementById("label").innerHTML;

document.getElementById("label").innerHTML = val.replace(nodeText + ",", "");

}

var parentTable = GetParentByTagName("table", src);

var nxtSibling = parentTable.nextSibling;

//check if nxt sibling is not null & is an element node

if (nxtSibling && nxtSibling.nodeType == 1) {

//if node has children

if (nxtSibling.tagName.toLowerCase() == "div") {

//check or uncheck children at all levels

CheckUncheckChildren(parentTable.nextSibling, src.checked);

}

//check or uncheck parents at all levels

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;

//checkbox checked

if (check) {

var isAllSiblingsChecked = AreAllSiblingsChecked(srcChild);

if (isAllSiblingsChecked)

checkUncheckSwitch = true;

else

return; //do not need to check parent if any(one or more) child not checked

}

else //checkbox unchecked

{

checkUncheckSwitch = false;

}

var inpElemsInParentTable = parentNodeTable.getElementsByTagName("input");

if (inpElemsInParentTable.length > 0) {

var parentNodeChkBox = inpElemsInParentTable[0];

parentNodeChkBox.checked = checkUncheckSwitch;

//do the same recursively

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) {

//check if the child node is an element node

if (parentDiv.childNodes[i].tagName.toLowerCase() == "table") {

var prevChkBox = parentDiv.childNodes[i].getElementsByTagName("input")[0];

//if any of sibling nodes are not checked, return false

if (!prevChkBox.checked) {

return false;

}

return true;

}

//utility function to get the container of an element by tagname

function GetParentByTagName(parentTagName, childElementObj) {

var parent = childElementObj.parentNode;

while (parent.tagName.toLowerCase() != parentTagName.toLowerCase()) {

parent = parent.parentNode;

}

return parent;

}

function getNextSibling(element) {

var n = element;

do n = n.nextSibling;

while (n && n.nodeType != 1);

return n;

}

//returns NodeValue

function GetNodeValue(node) {

var nodeValue = "";

var nodePath = node.href.substring(node.href.indexOf(",") + 2, node.href.length - 2);

var nodeValues = nodePath.split("\\");

if (nodeValues.length > 1)

nodeValue = nodeValues[nodeValues.length - 1];

else

nodeValue = nodeValues[0].substr(1);

return nodeValue;

}

</script>

</asp:Content>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

<tr>

</td>

</tr>

<tr>

<td>

<asp:TreeView ID="tvTables" runat="server" ForeColor="Black" Font-Size="13pt" ShowCheckBoxes="All"

OnClick="OnTreeClick(event)">

</asp:TreeView>

<br />

</div>

</td>

</tr>

<tr>

</td>

</tr>

</table>

</asp:Content>

Interview Question

Friday, August 8, 2014

Treeview in Asp.net C# with Checkbox

Monday, August 4, 2014

SQL Server XQuery(XML) Methods