Thursday, November 19, 2009

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.. ;)

Friday, October 2, 2009

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!! Happy

Tuesday, July 14, 2009

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!! :)

Monday, May 18, 2009

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:
Nested Gridview
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!!

Tuesday, May 5, 2009

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.

treeviewcheckboxIn 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:

data

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.

Friday, April 17, 2009

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:

treenode

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!! Smile

Monday, April 13, 2009

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!! Happy