SQL Server 2008 : Table Valued Parameter and Merge

Recently I was working on one project and I was supposed to Insert/Update bunch of records in database. I wanted to do it in a single round trip. I Google about it and found out two excellent features of SQL Server 2008: Table Valued Parameter and Merge Statement.

Table Valued Parameter allows you to send bunch of records to database in single round trip. To know how it can be done, please refer this link

Merge Statement is another good thing that you’ll fall in love with. It provides an efficient way to perform multiple DML operation. To know how it can be useful, please check out this link

Also check out one example of Merge here

I wanted to use Table Valued Parameters with Merge Command and I did it this way:

Create Procedure vi_GroupRightsAccess_AddUpdate
/*below is the Table valued Parameter of type tv_GroupRight
user defined table valued type created in db, and it should be read only*/
@tvpGrpRightAccess tv_GroupRight Readonly
As
Begin
--vi_GroupRightAccess is the table in my database
MERGE vi_GroupRightsAccess_Detail AS TargetTable
--Whatever columns you define in Using clause will be available in join
USING (SELECT GroupID,ModuleID,AccessID FROM @tvpGrpRightAccess) AS TvpSource
ON TargetTable.dtgroGroupId= TvpSource.GroupId and TargetTable.dtgroModuleId=TvpSource.ModuleId
--Perform intended operation if record matches
WHEN MATCHED THEN UPDATE SET TargetTable.dtgroAccessId = TvpSource.AccessID
--do what you want if it doesn’t match
WHEN NOT MATCHED THEN
INSERT(dtgroGroupId,dtgroModuleID,dtgroAccessID)
VALUES(TvpSource.Groupid,TvpSource.ModuleId,TvpSource.AccessID);

I hope this will be helpful.

Enjoy.. ;)

Show Modal Dialog with Asp.net webpage

For last few days one question is arising on asp.net forums and that is how to display Modal Dialog with Asp.net webpage. It can be done using JavaScript ShowModalDialog method.

Syntax :

returnVal = window.showModalDialog(uri[, arguments][, options]);

Where,

• uri is the uri of the document to be displayed in the dialog box.
• arguments is an optional variant that contains values that should be passed to the dialog box; these are made available in the window object's window.dialogArguments property
• options an optional string that specifies different attributes seperated by semicolon that will display modal dialog accordingly.

This method is introduced by Microsoft Interent Explorer4 and support added to Firefox 3. Firefox 3 doesn’t support dialogHide, edge, status, or unadorned arguments.

Here’s the code that is reusable. You can have following code in your class file as well and it works like a charm, once you add the namespace under which it is declared ;)

VB

Public Shared Sub OpenWindow(ByVal currentPage As Page, ByVal poppage As String, ByVal width As Int32, ByVal height As Int32) 
Dim sb As New System.Text.StringBuilder()  
sb.Append("var features;") 
sb.Append("features='dialogwidth:" & width & "px;dialogheight:" & height & "px;toolbar:0;scrollbars:0;location:0;statusbar:0;menubar:0;resizable:0';") 
sb.Append("popWin=window.showModalDialog('") 
sb.Append(poppage) 
sb.Append("','") 
sb.Append("',") 
sb.Append("features") 
sb.Append(");") 
'this will reload the parent page once modal dialog is closed
sb.Append("__doPostBack('ReturnFromDialogPostBack',popWin);") 
ScriptManager.RegisterStartupScript(currentPage, currentPage.GetType(), "OpenWindow", sb.ToString(), True) 
End Sub

C#

public static void OpenWindow(Page currentPage, string poppage, Int32 width, Int32 height)
{ 
System.Text.StringBuilder sb = new System.Text.StringBuilder();   
sb.Append("var features;");   
sb.Append("features='dialogwidth:" + width + "px;dialogheight:" + height + "px;toolbar:0;scrollbars:0;location:0;statusbar:0;menubar:0;resizable:0';");
sb.Append("popWin=window.showModalDialog('");
sb.Append(poppage);
sb.Append("','");
sb.Append("',");
sb.Append("features");
sb.Append(");");
sb.Append("__doPostBack('ReturnFromDialogPostBack',popWin);");
ScriptManager.RegisterStartupScript(currentPage, currentPage.GetType(), "OpenWindow", sb.ToString(), true);
}

Add following tag in head tag of your modal dialog page (Popup page)

<base target="_self" />

It will prevent popup page to open new window.

Add below line of code if you want to perform some server side processing or add it in JavaScript function.

Response.Write("<script language='JavaScript' type='text/JavaScript'>window.close();</script>")

I hope this helps!! 

Generate Nested Gridview Dynamically

As i said in my last post, I’ll write about how to generate Nested Gridview dynamically. As there are many articles available on the net, let me provide you the link to the article that i referred.

Gridview Control to show master-child or master-slave data

It provides detail explanation with snapshot. There is one disadvantage that Gridview control is available only on client side. It means if you want to perform some action on server side like you want to get child controls Gridview then it’s not possible.

I hope this helps!!

Enjoy!! :)

Nested Gridview in Asp.net 2.0

As i said in my last post that i will write about Check/ Uncheck checkboxes of Treeview on the basis of database value and i think it’s very easy so skipping that part and here i am going to write about Nested Gridview that will look like below image:

Rather than displaying Nested Gridview in the same row, we’ll display it in below row that gives user proper understanding of the data. Let’s see, How it can be done?

First of all i am using Northwind Database that comes with SQL Server and if you are not having it, you can download it.

Here’s the HTML Source Code for Parent GridView, change it according the language C# or VB and your requirement:

  1: <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="OrderID" CellPadding="4" ForeColor="#333333">
  2:   <Columns>
  3:     <asp:TemplateField>
  4:      <ItemTemplate>
  5:        <asp:Label ID="lblSign" runat="server" />
  6:      </ItemTemplate>
  7:     </asp:TemplateField>
  8:     <asp:BoundField DataField="OrderID" HeaderText="OrderID" InsertVisible="False" ReadOnly="True" SortExpression="OrderID" />
  9:     <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" SortExpression="CustomerID" />
 10:     <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
 11:     <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
 12:     <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
 13:     <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
 14:     <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
 15:     <asp:BoundField DataField="Postal" HeaderText="Postal" SortExpression="Postal" />
 16:     <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
 17:     <asp:TemplateField>
 18:      <ItemTemplate>
 19:       <tr>
 20:        <td colspan="100%">
 21:         <div id="div<%#Eval("OrderID") %>" style="display: none; position: relative;">
 22:         </div>
 23:        </td>
 24:       </tr>
 25:      </ItemTemplate>
 26:     </asp:TemplateField>
 27:   </Columns>
 28:   <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
 29:   <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
 30:   <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
 31:   <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
 32:   <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
 33:   <EditRowStyle BackColor="#999999" />
 34:   <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
 35: </asp:GridView>

In above code we’ve inserted <tr> and <td> and <div> tag that is highlighted by Yellow background colour. Gridview is rendered as HTML table and GridView rows will be rendered as <tr> and cells will be as <td> so that we are explicitly inserting those tags to show our child Gridview exactly below to the corresponding Gridview Row rather than in last column of Parent Gridview.

SQL DataSource Code for Parent GridView :

  1: <asp:SqlDataSource ID="SqlDataSource1" runat="server"
  2: SelectCommand="SELECT TOP (10) OrderID, CustomerID, EmployeeID, ShipName as Name,
  3: ShipAddress as Address, ShipCity as City, ShipRegion as Region, ShipPostalCode as Postal,
  4: ShipCountry as Country FROM Orders"
  5: ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>">
  6: </asp:SqlDataSource>

Child GridView Code :

  1: <asp:GridView ID="gvDetails" runat="server" CellPadding="4" ForeColor="#333333" GridLines="Both"
  2:                                             DataSourceID="SqlDataSource2" Style="position: relative" AutoGenerateColumns="False">
  3: <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
  4: <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
  5: <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
  6: <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
  7: <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
  8: <EditRowStyle BackColor="#999999" />
  9: <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
 10: <Columns>
 11:  <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
 12:  <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
 13:  <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />
 14:  <asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity" />
 15:  <asp:BoundField DataField="Discount" HeaderText="Discount" SortExpression="Discount" />
 16:  <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"  SortExpression="CompanyName" />
 17: </Columns>
 18: </asp:GridView>

SQL DataSource Code for Child GridView

  1: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
  2: SelectCommand="SELECT C.CompanyName, OD.UnitPrice, OD.Quantity, OD.Discount, E.FirstName, E.LastName, OD.OrderID, C.CustomerID, E.EmployeeID FROM  dbo.[Order Details] AS OD INNER JOIN
  3: dbo.Orders AS O ON OD.OrderID = O.OrderID INNER JOIN
  4: dbo.Customers AS C ON O.CustomerID = C.CustomerID INNER JOIN
  5: dbo.Employees AS E ON O.EmployeeID = E.EmployeeID AND O.OrderID LIKE @OrderId AND C.CustomerID = @CustomerID AND
  6: E.EmployeeID = @EmployeeId">
  7: <SelectParameters>
  8:   <asp:Parameter Name="OrderId" />
  9:   <asp:Parameter Name="CustomerId" />
 10:   <asp:Parameter Name="EmployeeId" />
 11: </SelectParameters>
 12: </asp:SqlDataSource>

We’ll supply OrderId, CustomerID, EmployeeID to SQL DataSource of ChildGridView in RowDataBound of Parent GridView.

Parent GridView Event Handlers

C#

  1: protected void gvOrders_RowCreated(object sender, GridViewRowEventArgs e)
  2: {
  3:    if (e.Row.RowType == DataControlRowType.Header || e.Row.RowType == DataControlRowType.DataRow)
  4:    {
  5:       e.Row.Cells[1].Visible = false;
  6:       e.Row.Cells[2].Visible = false;
  7:       e.Row.Cells[3].Visible = false;
  8:    }
  9: }

VB

  1: Protected Sub gvOrders_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvOrders.RowCreated
  2:   If e.Row.RowType = DataControlRowType.Header Or e.Row.RowType = DataControlRowType.DataRow Then
  3:      e.Row.Cells(1).Visible = False
  4:      e.Row.Cells(2).Visible = False
  5:      e.Row.Cells(3).Visible = False
  6:   End If
  7: End Sub

C#

  1: protected void gvOrders_RowDataBound(object sender, GridViewRowEventArgs e)
  2: {
  3:   if (e.Row.RowType == DataControlRowType.Header)
  4:   {
  5:     e.Row.Cells[1].Visible = false;
  6:     e.Row.Cells[2].Visible = false;
  7:     e.Row.Cells[3].Visible = false;
  8:   }
  9:   if (e.Row.RowType == DataControlRowType.DataRow)
 10:   {        
 11:     SqlDataSource s = (SqlDataSource)e.Row.FindControl("SqlDataSource2");
 12:     s.SelectParameters[0].DefaultValue= e.Row.Cells[1].Text;
 13:     s.SelectParameters[1].DefaultValue = e.Row.Cells[2].Text;
 14:     s.SelectParameters[2].DefaultValue = e.Row.Cells[3].Text;
 15:
 16:     ((Label)e.Row.FindControl("lblSign")).Text = "+";
 17:     ((Label)e.Row.FindControl("lblSign")).Attributes["onclick"] = "HideShowDiv('div" + e.Row.Cells[1].Text + "', '" + ((Label)e.Row.FindControl("lblSign")) .ClientID + "');";
 18:     e.Row.Cells[0].Attributes["onmouseover"] = "this.style.cursor='pointer'";
 19:     e.Row.Cells[0].Attributes["onmouseout"] = "this.style.cursor='pointer'";
 20:         
 21:   }
 22: }

JavaScript

  1:  function HideShowDiv(DivId,LblSign)
  2:  {
  3:       var mydiv= document.getElementById(DivId);
  4:       var LblSign=document.getElementById(LblSign);
  5:       if(mydiv!=null && LblSign!=null)
  6:       {
  7:             if(mydiv.style.display!="block")
  8:            {
  9:                 mydiv.style.display="block";
 10:                 LblSign.innerHTML="-";
 11:            }
 12:            else
 13:           {
 14:                 mydiv.style.display="none";
 15:                 LblSign.innerHTML="+";
 16:           } 
 17:       }
 18:  }

Above JavaScript function will Hide/ Show Div and changes Sign of Parent Row on expansion of Div and so on. I hope this helps!! :)

Next Time i will write “How to generate Child Gridview Dynamically” :)

Enjoy!!

Retrieve selected treenode value at server side

As i said in my previous post here’s the way to retrieve selected treenode value at server side.

In above image all the nodes are selected including parent node. In my first post we concatenated value of parent node with child node and assigned it to child node so that whenever we want to retrieve value of child node, we can have parent node value.

Here’s the output of selected node:

C#

  1:  protected void btnGetNode_Click(object sender, EventArgs e)
  2:  {
  3:         TreeNodeCollection tn = tvSample.CheckedNodes;
  4:         for (int i = 0; i < tn.Count; i++)
  5:         {
  6:             String[] arr =  tn[i].Value.Split('$');
  7:             if (arr.Length == 2)
  8:             {
  9:                 Response.Write("<br>Parent Node Value" + arr[1] + "<br>");
 10:                 Response.Write("<br>Child Node: " + tn[i].Text + " Value: " + arr[0]);
 11:             }
 12:         }
 13:  }

VB

  1: Protected Sub btnGetNode_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnGetNode.Click
  2:         Dim tn As TreeNodeCollection = tvSample.CheckedNodes
  3:         For i As Integer = 0 To tn.Count - 1
  4:             Dim arr As [String]() = tn(i).Value.Split("$"c)
  5:             If arr.Length = 2 Then
  6:                 Response.Write("<br>Parent Node Value" & arr(1) & "<br>")
  7:                 Response.Write("<br>Child Node: " & tn(i).Text & " Value: " + arr(0))
  8:             End If
  9:         Next
 10:     End Sub
 11: 

As shown in above code, we are splitting node value with ‘$’ sign. The first value is parent node value and second value is child node value. Another way is to loop through all the nodes and check for checked node value.

I hope this helps!! :)

In my next post i will write about check tree nodes on the basis of database value.

TreeView Node Value at Client Side

As i said in my last post in this post i will demonstrate how to retrieve value of selected nodes at client side. First of all if you are not aware how to Populate Treeview dynamically with database fields and check/ Uncheck TreeView Checkboxes then please refer respective posts.

Here’s the snap of treeview selected nodes:

As you can see in above image that Designer is the parent node and others are child nodes. Now it is very necessary to understand how this will be rendered so that we can write appropriate ClientScript.

Here’s the rendered HTML of treeview node:

  1: <td style="white-space:nowrap;">
  2: <input type="checkbox" name="tvSamplen54CheckBox" id="tvSamplen54CheckBox" />
  3: <a class="tvSample_0" href="javascript:__doPostBack('tvSample','s14\\ENL44273F$14')" onclick="TreeView_SelectNode(tvSample_Data, this,'tvSamplet54');" id="tvSamplet54">ElizabethLincoln</a>
  4: </td>

our area of interest is <a> tag having green background colour in above code. It contains node value in it’s href attribute.

so Here’s the Client Script function that is useful to retrieve value of selected node at Client Side.

  1: //this function will return <a> tag and we can find the nodevalue in href attribute of it
  2:     function getNextSibling(element)
  3:     {
  4:         var n = element;
  5:         do n = n.nextSibling;
  6:         while (n && n.nodeType != 1);
  7:         return n;
  8:     }
  9:     //returns NodeValue
 10:     function GetNodeValue(node)
 11:     {
 12:         var nodeValue = "";
 13:         var nodePath = node.href.substring(node.href.indexOf(",") + 2, node.href.length - 2);
 14:         var nodeValues = nodePath.split("\\");
 15:         if (nodeValues.length > 1)
 16:             nodeValue = nodeValues[nodeValues.length - 1];
 17:         else
 18:             nodeValue = nodeValues[0].substr(1);
 19:         return nodeValue;
 20:     } 

Here’s the function that will call the function in above code. This function i posted in my previous post
but highlighted lines are additional.

  1: function OnTreeClick(evt)
  2: {
  3:
  4:  var src = window.event != window.undefined ? window.event.srcElement : evt.target;
  5:
  6:  var isChkBoxClick = (src.tagName.toLowerCase() == "input" && src.type == "checkbox");
  7:
  8:  if(isChkBoxClick)
  9:  {
 10:    //gets node value if node is checked, this was my requirement
 11:     if (src.checked==true)
 12:     {
 13:         var nodeText = getNextSibling(src).innerText || getNextSibling(src).innerHTML;
 14:
 15:         var nodeValue = GetNodeValue(getNextSibling(src));
 16:
 17:         alert("Text: " + nodeText + "," + "Value: " + nodeValue);
 18:     }
 19:      var parentTable = GetParentByTagName("table", src);
 20:
 21:      var nxtSibling = parentTable.nextSibling;
 22:
 23:      //check if nxt sibling is not null & is an element node
 24:       if(nxtSibling && nxtSibling.nodeType == 1)
 25:      {
 26:
 27:           //if node has children    
 28:          if(nxtSibling.tagName.toLowerCase() == "div")
 29:          {
 30:
 31:              //check or uncheck children at all levels
 32:              CheckUncheckChildren(parentTable.nextSibling, src.checked);
 33:
 34:          }
 35:      }
 36:
 37:  //check or uncheck parents at all levels
 38:  CheckUncheckParents(src, src.checked);
 39:
 40:  }
 41:
 42: }

In my next post i will write about how to retrieve value of selected nodes at client side.

I hope this helps!!

Check/ Uncheck Treeview Checkboxes with Javascript

As i said in my last post that i will post code how to retrieve treeview node values at client side as well as server side. But before that we have to add client script to check/ uncheck treeview checkboxes.

First of all add following in your page_load event:

C#

  1: If(!isPostBack)
  2: {
  3:     TreeView1.Attributes.Add("onclick","OnTreeClick(event)");
  4: }

VB

  1: if not Ispostback then
  2:   TreeView1.Attributes.Add("onclick","OnTreeClick(event)")
  3: End if

JavaScript

  1: <script type="text/javascript">
  2: function OnTreeClick(evt)
  3: {
  4:  var src = window.event != window.undefined ? window.event.srcElement : evt.target;
  5:  var isChkBoxClick = (src.tagName.toLowerCase() == "input" && src.type == "checkbox");
  6:  if(isChkBoxClick)
  7:  {
  8:      var parentTable = GetParentByTagName("table", src);
  9:      var nxtSibling = parentTable.nextSibling;
 10:      //check if nxt sibling is not null & is an element node
 11:       if(nxtSibling && nxtSibling.nodeType == 1)
 12:      {
 13:           //if node has children    
 14:          if(nxtSibling.tagName.toLowerCase() == "div")
 15:          {
 16:              //check or uncheck children at all levels
 17:              CheckUncheckChildren(parentTable.nextSibling, src.checked);
 18:          }
 19:      }
 20:  //check or uncheck parents at all levels
 21:  CheckUncheckParents(src, src.checked);
 22:  }
 23: }
 24:
 25:  function CheckUncheckChildren(childContainer, check)
 26: {
 27:  var childChkBoxes = childContainer.getElementsByTagName("input");
 28:       var childChkBoxCount = childChkBoxes.length;
 29:  for(var i=0;i<childChkBoxCount;i++)
 30:  {
 31:      childChkBoxes[i].checked = check;
 32:  }
 33: }
 34:
 35: function CheckUncheckParents(srcChild, check)
 36: {
 37:  var parentDiv = GetParentByTagName("div", srcChild);
 38:  var parentNodeTable = parentDiv.previousSibling;
 39:  if(parentNodeTable)
 40:  {
 41:      var checkUncheckSwitch;
 42:     //checkbox checked 
 43:      if(check)
 44:      {
 45:          var isAllSiblingsChecked = AreAllSiblingsChecked(srcChild);
 46:          if(isAllSiblingsChecked)
 47:                  checkUncheckSwitch = true;
 48:          else
 49:                  return; //do not need to check parent if any(one or more) child not checked
 50:      }
 51:      else //checkbox unchecked
 52:      {
 53:          checkUncheckSwitch = false;
 54:      }
 55:  
 56:      var inpElemsInParentTable = parentNodeTable.getElementsByTagName("input");
 57:      if(inpElemsInParentTable.length > 0)
 58:      {
 59:          var parentNodeChkBox = inpElemsInParentTable[0];
 60:          parentNodeChkBox.checked = checkUncheckSwitch;
 61:          //do the same recursively
 62:         CheckUncheckParents(parentNodeChkBox, checkUncheckSwitch);
 63:      }
 64:  }
 65: }
 66:
 67: function AreAllSiblingsChecked(chkBox)
 68: {
 69:  var parentDiv = GetParentByTagName("div", chkBox);
 70:  var childCount = parentDiv.childNodes.length;
 71:  for(var i=0;i<childCount;i++)
 72:  {
 73:      if(parentDiv.childNodes[i].nodeType == 1)
 74:      {
 75:          //check if the child node is an element node
 76:          if(parentDiv.childNodes[i].tagName.toLowerCase() == "table")
 77:          {
 78:              var prevChkBox = parentDiv.childNodes[i].getElementsByTagName("input")[0];
 79:              //if any of sibling nodes are not checked, return false
 80:              if(!prevChkBox.checked)
 81:              {
 82:                  return false;
 83:              }
 84:          }
 85:      }
 86:  }
 87:  return true;
 88: }
 89:
 90: //utility function to get the container of an element by tagname
 91: function GetParentByTagName(parentTagName, childElementObj)
 92: {
 93:  var parent = childElementObj.parentNode;
 94:  while(parent.tagName.toLowerCase() != parentTagName.toLowerCase())
 95:      {
 96:          parent = parent.parentNode;
 97:      }
 98:  return parent;
 99: }
100:     </script>

Next time i will post about how to retrieve value at Client Side and Server Side.

I hope this helps!!

Populate Treeview With Database Fields

This post describes how to populate treeview with database fields. People often required to populate treeview with database fields and they choose different approaches to make it work. Here I am going to describe the simplest way.

Here I'll be using Sql Server Sample Database: Pubs

Relations:

Treeview :


VB Code :

Public Sub GenerateTreeView()
   Dim ds As New DataSet
   Dim conn As New SqlConnection
   Dim connString As String
   Dim strQuery As String
   Dim da As SqlDataAdapter
   Dim dtJob As New DataTable
   Dim dtEmp As New DataTable
   Dim nodeJob As TreeNode
   Dim nodeEmp As TreeNode     

   Try

   connString = ConfigurationManager.ConnectionStrings("pubsConnectionString").ToString()

   strQuery = "select job_id,job_desc from jobs"
   da = New SqlDataAdapter(strQuery, connString)
   da.Fill(dtJob)
   dtJob.TableName = "Job"
   ds.Tables.Add(dtJob)


   strQuery = "SELECT emp_id, fname, lname, job_id FROM employee ORDER BY job_id"
   da = New SqlDataAdapter(strQuery, connString)
   da.Fill(dtEmp)
   dtEmp.TableName = "Employee"
   ds.Tables.Add(dtEmp)

   'define RelationShip
   ds.Relations.Add("JobEmployee", ds.Tables("Job").Columns("job_id"),
 ds.Tables("Employee").Columns("job_id"))

   For Each dr As DataRow In ds.Tables("job").Rows       'Parent Nodes
       nodeJob = New TreeNode()
       nodeJob.Value = dr("job_id")
       nodeJob.Text = dr("job_desc")
       nodeJob.ShowCheckBox = True
       nodeJob.Expanded = False
       tvSample.Nodes.Add(nodeJob)
       For Each drr As DataRow In dr.GetChildRows("JobEmployee")    'Child Nodes
           nodeEmp = New TreeNode()
           nodeEmp.Text = drr("fname") & " " & drr("lname")
           'here concating parent node value with child node value
           'so that it will be easy to get the selected node value
           nodeEmp.Value = drr("emp_id") & "$" & dr("job_id") 
           nodeEmp.ShowCheckBox = True
           nodeJob.ChildNodes.Add(nodeEmp)
       Next
   Next

  Catch ex As Exception
    strQuery = Nothing
    da = Nothing
    conn = Nothing
    connString = Nothing
  Finally
 End Try
End Sub
 

C# Code:

 public void GenerateTreeView()
     {
         DataSet ds = new DataSet();
         SqlConnection conn = new SqlConnection();
         string connString = null;
         string strQuery = null;
         SqlDataAdapter da;
         DataTable dtJob = new DataTable();
         DataTable dtEmp = new DataTable();
         TreeNode nodeJob; 
         TreeNode nodeEmp; 
        
         try {
            
             connString =
ConfigurationManager.ConnectionStrings["pubsConnectionString"].ConnectionString.ToString();
            
             //Job Table Data
             strQuery = "select job_id,job_desc from jobs";
             da = new SqlDataAdapter(strQuery, connString);
             da.Fill(dtJob);
             dtJob.TableName = "Job";
             ds.Tables.Add(dtJob);
            
            //Employee Table Data
             strQuery = "SELECT emp_id, fname, lname, job_id FROM employee ORDER BY job_id";
             da = new SqlDataAdapter(strQuery, connString);
             da.Fill(dtEmp);
             dtEmp.TableName = "Employee";
             ds.Tables.Add(dtEmp);
            
             //define RelationShip
             ds.Relations.Add("JobEmployee", ds.Tables["Job"].Columns["job_id"],
ds.Tables["Employee"].Columns["job_id"]);
            
             foreach (DataRow dr in ds.Tables["job"].Rows) {
                 nodeJob = new TreeNode();
                 nodeJob.Value = dr["job_id"].ToString();
                 nodeJob.Text = dr["job_desc"].ToString();
                 nodeJob.ShowCheckBox = true;
                 nodeJob.Expanded = false;
                 tvSample.Nodes.Add(nodeJob);
                 foreach (DataRow drr in dr.GetChildRows("JobEmployee") ) {
                     nodeEmp = new TreeNode();
                     nodeEmp = new TreeNode();
                     nodeEmp.Text = drr["fname"].ToString() + drr["lname"].ToString();
                     nodeEmp.ShowCheckBox = true;
                     nodeJob.ChildNodes.Add(nodeEmp);
                 }
             }
         }
         catch (Exception ex) {
             strQuery = null;
             da = null;
             conn = null;
             connString = null;
         }
        
         finally {
         }
        
     }


}

In my next posts i will write about javascript that will be useful to check/ uncheck all the checkboxes of treeview and how to retrieve value of all the checked nodes server side and client side.

I hope this helps :)

Ubuntu 8.10, blank screen shows after login screen

Recently i installed Ubuntu 8.10 (Intrepid Ibex) on my machine. After loging in i got only brown blank screen and i couldn't do anything except moving mouse around the screen. I thought i didn't installed it properly, so i reinstalled it twice. Finally i found out the solution. It happened because graphic card can not handle the compiz effects.

There is a bug in the Intel Video driver for the Intel 830MG and 845G video cards. Desktop effects with Compiz will not work on those chips and freeze the system. During that time keyboard too won't work (meaning Numlock doens't work), just to be sure, are you able to get to consoles (ctrl+alt+f1)? If that too doesn't work, it's probably your kernel crashing.

To fix the problem you need to remove the Compiz, here's how:

1. From the Grub boot menu slect the “recovery mode” option.

2. Select “root Drop to root shell prompt”

Type the following at the prompt:

3. sudo apt-get remove compiz

4. sudo apt-get remove compiz-core

5. exit

6. Select “resume Resume normal boot”

Or you can use the following trick:

1. Select FailSafe Terminal session from the options at Login screen and Login with your Username and Password.

2. Type gconf-editor. It will open configuration editor window, from it select

desktop->gnome->application->window_manager.

3. From right side of the window edit the value for current and default by removing "usr/bin/compiz" with "usr/bin/metacity" and reboot.

4. Select Gnome session and Login with your Username and Password.

That's it!! :)

.Net Frameworks and Silverlight

Friends as I said that I will post about topics that often surfaces on Asp.net Forums as well as Orkut Communities. So here's one of the question I came across:

Is it possible to Use SilverLight with Asp.net 2.0?

Answer : No

You can not use it directly with Framework 2.0 but of course you can use it with Framework 3.0 and 3.5. But in a way if you want to use Silverlight with your application developed with framework 2.0 then you just have to compile all the Silverlight files in Framework 3.5 and add reference of it in your application. That's It!!

Though it becomes confusing for people when experts say that yes you can do it with Orcas. Just compile your files in Orcas, add them in your application with proper reference. People often wonder what is this Orcas? Orcas is the Codename for .NET Framework 3.5.

Here are some Codenames of .NET Frameworks :

• Framework 2.0 - Whidbey
  

• Framework 3.0 - Vista

• Framework 3.5 - Orcas

Silverlight applications can be written in any .NET programming language. Microsoft has positioned Expression Blend Version 2.0 and 2.5 for designing the UI of Silverlight 1.0 and 2 applications respectively. Silverlight 2.0 is the Latest Version.

Mono is a project led by Novell, which provides you with similar kind of IDE of .NET .It can be run on Linux, Unix, Mac OS X, Solaris, and of course Windows. Latest Version of Mono is 2.2.

The same Mono team is developing Moonlight, open source implementation of the Silverlight 1.0 and 2.0 runtimes. This project is officially supported by Microsoft. Lates version of Moonlight is 1.0 that was released on January 20, 2009.

Here are two sample application developed in Silvelight..just check them out..

Hard Rock Memorabilia and Mscui

To know how to make Silverlight work with VS2005 (Indirect way) , Click Here

I hope this post will be helpful !! :)