Pages

Search Code Shode

Loading...

Tuesday, February 11, 2014

MS SQL Server Function to Insert Line Break in String


Today I came across a situation where I had to break a string coming from SQL and show it in different lines on HTML. I know it's a weird situation, but so is programming :)

I googled for it but couldn't find an exact solution so I decided to write an SQL user-defined function myself.
While writing a function a thought why only limit it to a line-break (<br/>) So i made it more generic.

here is the function:
CREATE FUNCTION [dbo].Stringlinebreak(@String NVARCHAR(1000), 
                                      @Length INT, 
                                      @Char   VARCHAR(5)) 
returns NVARCHAR(max) 
  BEGIN 
      DECLARE @ReturnString NVARCHAR(max) 
      DECLARE @ActualLength INT 
      DECLARE @I INT 

      SET @ReturnString = @String 

      SELECT @ActualLength = Len(@String) 

      IF( @ActualLength > ( @Length * 2 ) ) 
        BEGIN 
            SET @I = @Length 

            WHILE @I <= @ActualLength 
              BEGIN 
                  SELECT @ReturnString = Stuff(@ReturnString, @I - 5, 1, 
                                         Substring(@ReturnString, @I-5, 1 
                                                ) + @Char) 

                  SET @I = @I + @Length 
              END 
        END 
      ELSE 
        BEGIN 
            SELECT @ReturnString = CASE 
                                     WHEN @ActualLength > @Length THEN 
                                     Stuff(@String, @Length - 5, 1, 
                                     Substring(@String, @Length-5, 1) + @Char) 
                                     ELSE @String 
                                   END 
        END 

      RETURN @ReturnString 
  END 


it can be used as:

SELECT [dbo].Stringlinebreak('Microsoft Certified Database Administrator on Microsoft SQL Server 2000', 30, '<br/>') 


and the output will be:
Microsoft Certified Datab<br/>ase Administrator on Micr<br/>osoft SQL Server 2000


So when it is displayed in HTML, the string is with line breaks.

You can try this code, here at SQL Fiddle

I hope it helps someone else too.

Happy Coding!

Wednesday, September 19, 2012

Fix HTML Table Header While Scrolling Page Using jQuery


I have heard this question various times:
Is there a cross-browser CSS/JavaScript technique to display a long HTML table such that the column headers stay fixed on-screen and do not scroll with the table body like the "freeze panes" effect in Microsoft Excel.
This blog post display how can we use a little bit of jQuery tweaking to fix position for the header of asp:Repeater  (or any table header). It will keep the header to float and be always on top.
Lets have a quick example for How to Fix HTML Table Header while scrolling page.

ASPX:

<table id="tblEmployee" border="0" cellpadding="5" cellspacing="1" class="tblborder"
        width="100%">
        <thead>
            <tr class="header" style="font-weight: bold;">
                <td align="center" colspan="7” >
                    Employee List
                </td>
            </tr>
            <tr class="header" style="font-weight: bold;">
                <td align="center" width="3%">
                    S.No.
                </td>
                <td align="center">
                    Employee Code
                </td>
                <td align="center">
                   Employee Name
                </td>
                <td align="center">
                    Department
                </td>
                <td align="center">
                    Designation
                </td>
                <td align="center">
                    Manager
                </td>
                <td align="center">
                    Action
                </td>
            </tr>

        </thead>
        <tbody>
            <asp:Repeater ID="rptEmployee" runat="server">
                <ItemTemplate>
                    <tr class="bgGray repeaterRow">
                        <td align="center" valign="top" width="3%">
                            <%# Container.ItemIndex + 1 %>
                        </td>
                        <td align="center" valign="top" nowrap="nowrap">
                            <asp:HiddenField ID="hdEmployee_ID" runat="server" Value='<%#Eval("Employee_ID")%>' />
                            <%#Eval("Employee_Code")%>
                        </td>
                        <td align="center" valign="top" nowrap="nowrap">
                            <%#Eval("Employee_Name")%>
                        </td>
                        <td align="center" width="10%" nowrap="nowrap">
                            <%#Eval("Department_Name")%>
                        </td>
                        <td align="center" valign="top" width="10%" nowrap="nowrap">
                            <%#Eval("Designation_Name")%>
                        </td>
                        <td align="center" valign="top" width="20%" nowrap="nowrap">
                            <%#Eval("Manager_Name")%>
                        </td>
                        <td align="center" valign="top">
                            <a id="aEdit" runat="server" href='<%# Eval("Employee_ID","EditEmployee.aspx?Employee_ID={0}" ) %>'>
                                Edit</a>
                        </td>
                    </tr>
                </ItemTemplate>
                <AlternatingItemTemplate>
                    <tr class="bgAlt repeaterRow">
                        <td align="center" valign="top" width="3%">
                            <%# Container.ItemIndex + 1 %>
                        </td>
                        <td align="center" valign="top" nowrap="nowrap">
                            <asp:HiddenField ID="hdEmployee_ID" runat="server" Value='<%#Eval("Employee_ID")%>' />
                            <%#Eval("Employee_Code")%>
                        </td>
                        <td align="center" valign="top" nowrap="nowrap">
                            <%#Eval("Employee_Name")%>
                        </td>
                        <td align="center" width="10%" nowrap="nowrap">
                            <%#Eval("Department_Name")%>
                        </td>
                        <td align="center" valign="top" width="10%" nowrap="nowrap">
                            <%#Eval("Designation_Name")%>
                        </td>
                        <td align="center" valign="top" width="20%" nowrap="nowrap">
                            <%#Eval("Manager_Name")%>
                        </td>
                        <td align="center" valign="top">
                            <a id="aEdit" runat="server" href='<%# Eval("Employee_ID","EditEmployee.aspx?Employee_ID={0}" ) %>'>
                                Edit</a>
                        </td>
                    </tr>
                </AlternatingItemTemplate>
            </asp:Repeater>
        </tbody>
    </table>

CSS:

.tblborder
{
    background-color: #7c7a90;
    font-family: Calibri;
    font-size: 12px;
    font-weight: bold;
    text-align: center;
}

.header
{
    border: 1px solid #aed0ea;
    color: Gray;
    font-weight: bold;
    font-size: 14px;
    font-family: Tahoma;
    background: #d7ebf9 url(../../../Styles/images/ui-bg_glass_80_d7ebf9_1x400.png) 50% 50% repeat-x;
    text-align:left;
}
.bgGray
{
    font-weight: normal;
    background: #f2f5f7 url(../../../Styles/images/ui-bg_diamond_100_f2f5f7_10x8.png) 50% 50% repeat;
    color: #362b36;
    padding:3px;
}
.bgAlt
{
    background: #eff6fc;
    font-weight: normal;
    color: #362b36;
    padding:3px;
}
.highlightRow
{
    background: rgb(228,245,252);
    color: Black;
}
.repeaterRow
{
    text-decoration: none;
}



jQuery/Javascript:

    <script type="text/javascript">
jQuery.fn.fixedtableheader = function (options) {
    var settings = jQuery.extend({
        headerrowsize: 1,
        highlightrow: false,
        highlightclass: "highlight"
    }, options);
    this.each(function (i) {
        var $tbl = $(this);
        var $tblhfixed = $tbl.find("tr:lt(" + settings.headerrowsize + ")");
        var headerelement = "thead";
        if ($tblhfixed.find(headerelement).length == 0) headerelement = "td";
        if ($tblhfixed.find(headerelement).length > 0) {
            $tblhfixed.find(headerelement).each(function () {
                $(this).css("width", $(this).width());
            });
            var $clonedTable = $tbl.clone().empty();
            var tblwidth = GetTblWidth($tbl);
            $clonedTable.attr("id", "fixedtableheader" + i).css({
                "position": "fixed",
                "top": "0",
                "left": $tbl.offset().left
            }).append($tblhfixed.clone()).width(tblwidth).hide().appendTo($("body"));
            if (settings.highlightrow) $("tr:gt(" + (settings.headerrowsize - 1) + ")", $tbl).hover(function () {
                $(this).addClass(settings.highlightclass);
            }, function () {
                $(this).removeClass(settings.highlightclass);
            });
            $(window).scroll(function () {
                if (jQuery.browser.msie && jQuery.browser.version == "6.0") $clonedTable.css({
                    "position": "absolute",
                    "top": $(window).scrollTop(),
                    "left": $tbl.offset().left
                });
                else $clonedTable.css({
                    "position": "fixed",
                    "top": "0",
                    "left": $tbl.offset().left - $(window).scrollLeft()
                });
                var sctop = $(window).scrollTop();
                var elmtop = $tblhfixed.offset().top;
                if (sctop > elmtop && sctop <= (elmtop + $tbl.height() - $tblhfixed.height())) $clonedTable.show();
                else $clonedTable.hide();
            });
            $(window).resize(function () {
                if ($clonedTable.outerWidth() != $tbl.outerWidth()) {
                    $tblhfixed.find(headerelement).each(function (index) {
                        var w = $(this).width();
                        $(this).css("width", w);
                        $clonedTable.find(headerelement).eq(index).css("width", w);
                    });
                    $clonedTable.width($tbl.outerWidth());
                }
                $clonedTable.css("left", $tbl.offset().left);
            });
        }
    });

    function GetTblWidth($tbl) {
        var tblwidth = $tbl.outerWidth();
        return tblwidth;
    }
};

    </script>

    <script type="text/javascript">
        $(function () {
            $("#tblEmployee").fixedtableheader({
                headerrowsize: 2
            }); 

            /* to highlight row on mouseover*/
            $('.repeaterRow').hover(function () {
                $(this).children().addClass('highlightRow');
            }, function () {
                $(this).children().removeClass('highlightRow');
            });

        });
    </script>

And that’s it. Just place this fixedtableheader function in a general javascript file reuse and call this function wherever you want. Only thing to remember is to follow the table structure as mentioned. Let me know if anything is not clear

Happy Coding!


اشتہارات