How to make and use Split function in sql Server

1. Create a Function in SQL as follows:

Create FUNCTION dbo.StringSplit
(
@SplitStr nvarchar(2000),
@SplitChar nvarchar(20)
)
RETURNS @RtnValue table
(
Data nvarchar(2000)
)
AS
BEGIN
Declare @Count int
Set @Count = 1
While (Charindex(@SplitChar,@SplitStr)>0)
Begin
Insert Into @RtnValue (Data)
Select
Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))
Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))
Set @Count = @Count + 1
End
Insert Into @RtnValue (Data)
Select Data = ltrim(rtrim(@SplitStr))
Return
END

2. you need to pass two parameter one is string and otherone is spliting character
here is example

select * from SplitString('Contact-No-Deal\Email\08-30-2010\SP10000175-08-30-2010-16-56-28-5322-BalanceRequest.doc', '\')

output will be like this:

Contact-No-Deal
Email
08-30-2010
SP10000175-08-30-2010-16-56-28-5322-BalanceRequest.doc

Dynamically passing dates between clause

Create PROCEDURE dbo.test
(
@Table nvarchar(50),
@Field nvarchar(50),
@BeginDate DateTime,
@EndDate DateTime
)
AS
DECLARE @Sql nvarchar(1000)
SET @Sql = 'SELECT ' + @Table + '.* FROM ' + @Table + ' WHERE ' + @Field + ' BETWEEN ''' + convert(nvarchar(20), @BeginDate) + ''' AND ''' + convert(nvarchar(20), @EndDate) + ''''
EXEC sp_executesql @Sql

function which will return st, th, nd and rd with date in SQL SERVER

1.create a function as below
create function [dbo].[get_tidy_date]
(
    @date datetime = null
)
returns nvarchar(50)
as begin
     
    declare @d int,
    @m nvarchar(15),
    @y nvarchar(4),
    @end nvarchar(1),
    @return nvarchar(50)
 
    if @date is null
        set @date=getdate()
    select @d=datepart(d, @date), @m=datename(m, @date), @Y=
datename(yyyy,@date), @end=right(convert(nvarchar(2), @d),1)
    set @return=
        convert(nvarchar(2), @d)
        +case
            when @d in(11, 12, 13) then 'th'
            when @end='1' then 'st'
            when @end='2' then 'nd'
            when @end='3' then 'rd'
            else 'th'  
        end
        +' '+@m+' '+@y
    return @return
 
end


2.Call this function as follows:

SELECT dbo.get_tidy_date(GETDATE())

Output is like this
2nd March 2011

Gridview Sorting with up and down arrow + paging

1. Add Gridview and enable sorting and write sortexpression field

      <asp:GridView ID="grdCompany" runat="server" AutoGenerateColumns ="False"
        DataKeyNames="ID,TraceStatus,CaseRefNo" Width="100%"
        onrowdatabound="grdCompany_RowDataBound" AllowPaging="True" AllowSorting="True"
              onrowcreated="grdCompany_RowCreated" onsorting="grdCompany_Sorting"
              onpageindexchanging="grdCompany_PageIndexChanging">
        <Columns>
        <asp:hyperlinkfield headertext="Reference No." datatextfield="CaseRefNo"  SortExpression="CaseRefNo"
                ItemStyle-Width="80px"
                datanavigateurlformatstring="viewcase.aspx?refid={0}&TraceType=C"
                datanavigateurlfields="CaseRefNo" >
            <HeaderStyle ForeColor="White" />
            <ItemStyle Width="80px" />
            </asp:hyperlinkfield>
        <%--<asp:BoundField DataField="CompanyName1" HeaderText="Company Name" ItemStyle-Width ="100px" /> 
        <asp:BoundField DataField="PropertyName1" HeaderText="Property Name" ItemStyle-Width ="100px" />  --%>
        <asp:TemplateField HeaderText="Company Name/ <br/>Property Name" SortExpression="CompanyName1">
        <ItemTemplate>
          <%#Eval("CompanyName1")%>
          <br />
          <%#Eval("PropertyName1") %>
        </ItemTemplate>
            <HeaderStyle ForeColor="White" />
        </asp:TemplateField> <asp:TemplateField HeaderText="Trace Level<br/>Date of Submission" SortExpression="Tracelevel">
        <ItemTemplate>
          <%#Eval("Tracelevel")%>
          <br />
          <%#Eval("DOS")%>
        </ItemTemplate>
                <HeaderStyle ForeColor="White" />
        </asp:TemplateField>
        <%--<asp:BoundField DataField="Tracelevel" HeaderText="Trace Level" ItemStyle-Width ="100px" /> 
        <asp:BoundField DataField="DOS" HeaderText="Date of Submission" ItemStyle-Width ="100px" /> 
        <asp:BoundField DataField="TraceStatus" HeaderText="Status" ItemStyle-Width ="100px" />  --%>
        <asp:TemplateField HeaderText="Status" SortExpression="TraceStatus">
        <ItemTemplate>
          <asp:Label ID="lblStatus" runat="server" Text='<% #Eval("TraceStatus") %>' ></asp:Label>
          <asp:HyperLink ID="vwReport" runat="server" Text="View Report"></asp:HyperLink>
        </ItemTemplate>
            <HeaderStyle ForeColor="White" />
        </asp:TemplateField>
        </Columns>
        <HeaderStyle CssClass ="t-heading"   height="35px" /> <RowStyle CssClass ="b-table-text-inside3" height="30px" />
        </asp:GridView>



2. write following code in code behind page.

DataView dv = new DataView();
private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";


//Write a method which return view
  private DataView bindgridSort()
    {
        clsCase objCase = new clsCase();
        objCase.pClientId = clsSessions.sesUserID;
        objCase.pTraceType = "C";
        DataSet dsCase = new DataSet();
        dsCase = objCase.GetAllCases();

        if (dsCase.Tables[0].Rows.Count > 0)
        {
            if (ViewState["sortExpr"] != null)
            {
                dv = new DataView(dsCase.Tables[0]);
                dv.Sort = (string)ViewState["sortExpr"];
            }
            else
                dv = dsCase.Tables[0].DefaultView;
        }
        else
        {
            lblMsg.Text = "No Details Found.";
        }
        return dv;

// Add row_created events

protected void grdCompany_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            int sortColumnIndex = GetSortColumnIndex();
            if (sortColumnIndex != -1)
            {
                AddSortImage(sortColumnIndex, e.Row);
            }
        }
    }
// Add sorting event
 protected void grdCompany_Sorting(object sender, GridViewSortEventArgs e)
    {
        string sortExpression = e.SortExpression;
        ViewState["SortExpression"] = sortExpression;
        if (GridViewSortDirection == SortDirection.Ascending)
        {
            GridViewSortDirection = SortDirection.Descending;
            SortGridView(sortExpression, DESCENDING);
        }
        else
        {
            GridViewSortDirection = SortDirection.Ascending;
            SortGridView(sortExpression, ASCENDING);
        }
    }


 private int GetSortColumnIndex()
    {
        // Iterate through the Columns collection to determine the index
        // of the column being sorted.
        foreach (DataControlField field in grdCompany.Columns)
        {
            if (field.SortExpression == (string)ViewState["SortExpression"])
            {
                return grdCompany.Columns.IndexOf(field);
            }
        }
        return -1;
    }
    private void AddSortImage(int columnIndex, GridViewRow headerRow)
    {
        // Create the sorting image based on the sort direction.
        Image sortImage = new Image();
        if (GridViewSortDirection == SortDirection.Ascending)
        {
            sortImage.ImageUrl = "Images/uparrow.gif";
            sortImage.AlternateText = "Ascending Order";
            sortImage.ImageAlign = ImageAlign.Top;
            sortImage.ImageAlign = ImageAlign.Middle;
        }
        else
        {
            sortImage.ImageUrl = "Images/downarrow.gif";
            sortImage.ImageAlign = ImageAlign.AbsBottom;
            sortImage.ImageAlign = ImageAlign.Middle;
            sortImage.AlternateText = "Descending Order";
        }
        // Add the image to the appropriate header cell.
        // headerRow.Cells[columnIndex].Controls.Add(space);
        headerRow.Cells[columnIndex].Controls.Add(sortImage);
    }
    private SortDirection GridViewSortDirection
    {
        get
        {
            if (ViewState["sortDirection"] == null)
                ViewState["sortDirection"] = SortDirection.Ascending;
            return (SortDirection)ViewState["sortDirection"];
        }
        set { ViewState["sortDirection"] = value; }
    }
    private void SortGridView(string sortExpression, string direction)
    {
        DataView dv = bindgridSort();
        dv.Sort = sortExpression + direction;
        grdCompany.DataSource = dv;
        grdCompany.DataBind();
    }
    protected void grdCompany_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdCompany.PageIndex = e.NewPageIndex;
        BindCaseDetails("C");
    }

Download file from griedview cell link in asp.net

1. Add gridview

<asp:GridView ID="GridView1" runat="server" SkinID="gridviewSkin" DataKeyNames="UploadID,UserId,AllocatedTo,AllocatedforQC"
                    OnRowCommand="GridView1_RowCommand" 
                    OnRowDataBound="GridView1_RowDataBound"
                    OnPageIndexChanging="GridView1_PageIndexChanging">
                    <Columns>
                        <asp:BoundField  HeaderText="Job#" DataField="uploadID" /> 
                        <asp:TemplateField HeaderText="Client Name-Uploaded By/ <br>Uploaded Date/<br>Duration/<br>File Size/<br>" ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="left">
                            <ItemTemplate>
                                <b><%#Eval("ClientName")%></b>-<%#Eval("FullName")%><img src="images/spacer.gif" alt="" width="10"  height="13px" /><br />
                               
                                <%#Eval("CLIENT_UPLOAD_DATE")%><img src="images/spacer.gif" alt="" width="10" height="13px" /><br />
                               
                                <%#Eval("Duration")%> [HH:MM:SS]<img src="images/spacer.gif" alt="" width="10" height="13px" /><br />
                               
                                <%#Eval("fileSize")%> kb<img src="images/spacer.gif" alt="" width="10" height="13px" /><br />
                            </ItemTemplate>
                        </asp:TemplateField>
                         <asp:BoundField  HeaderText="Transcribe By" DataField="ALLOCATEDTONAME" />
                       <asp:TemplateField HeaderText="Transcription File" ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="5%">
                            <ItemTemplate>
                                <asp:LinkButton Text='<img src="Images/downloadaudio.gif" alt="" border="0">' runat="server" ID="lnkDownload" CommandArgument='<%#Eval("CLIENT_UPLOAD_FILENAME") %>' CommandName="lnkDownload" BorderStyle="None" BorderWidth="0"></asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Download File(s)<br> for QC" HeaderStyle-Width="2%" ItemStyle-HorizontalAlign="Center">
                        <ItemTemplate>
                            <asp:Image ID="FileImage1" CssClass="handcursor" runat="server" ImageUrl="Images/downloaddoc.gif" onclick='<%#Eval("UPLOADID", "return ShowPopup(\"{0}\")")%>' />
                        </ItemTemplate>
                            <HeaderStyle Width="2%"></HeaderStyle>
                            <ItemStyle Wrap="true" />
                        </asp:TemplateField>
                       <%--<asp:TemplateField HeaderText="Upload" ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="5%">
                            <ItemTemplate>
                            <asp:LinkButton ID="lnkUpload" runat="server" Text="Upload" ></asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>--%>
                        <asp:TemplateField HeaderText="Accept/Reject QC" ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="5%">
                            <ItemTemplate>
                            <%--<asp:LinkButton ID="lnkAcceptQC" runat="server" Text="Release to QC" CommandArgument='<%#Eval("UploadID") %>' CommandName="AcceptQC"></asp:LinkButton>--%>
                                <asp:LinkButton ID="lnkAccept" runat="server" Text="Release to Client" OnClientClick="return confirmB();"  CommandArgument='<%#Eval("UploadID") %>' CommandName="Accept"></asp:LinkButton>
                                <br /><br />
                                <asp:LinkButton ID="lnkReject" runat="server" Text="Reject" CommandArgument='<%#Eval("UploadID") %>' CommandName="Reject"></asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                       
                        <asp:BoundField HeaderText="Status" HeaderStyle-Width="10%" DataField="STATUS" ItemStyle-HorizontalAlign="Center">
                            <HeaderStyle Width="8%"></HeaderStyle>
                            <ItemStyle Wrap="true" />
                        </asp:BoundField>
                    </Columns>
                </asp:GridView>

2. add code in code behind

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
 if (e.CommandName.Equals("lnkDownload"))
        {
           
            string strFilePath = System.Configuration.ConfigurationManager.AppSettings["uploadDictationFolder"].ToString();
            //+ "/" + e.CommandArgument.ToString()
           DownloadFile(e.CommandArgument.ToString(),strFilePath);
            //Response.Write(strFilePath);
         
        }
}

    public int DownloadFile(string fileName, string Path)
        {
            HttpResponse response = HttpContext.Current.Response;
            FileInfo file = null;
            int messageToReturn = 0;
            string path = Path + "\\" + fileName;
            try
            {
                file = new FileInfo(path);
                if (file.Exists)
                {
                    response.AddHeader("Content-Disposition", "attachment; filename=\"" + file.Name + "\"");
                    response.AddHeader("Content-Length", file.Length.ToString());
                    response.ContentType = GetContentType(file);
                    response.TransmitFile(file.FullName);
                    response.Buffer = true;
                    response.End();
                    messageToReturn = (int)FileHandlerEnum.Success;
                    messageToReturn = (int)FileHandlerEnum.Success;
                }
                else
                {
                    messageToReturn = (int)FileHandlerEnum.FailedFileNotExist;
                }
            }
            catch (IOException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //file = null;
                //response.End();
            }
            return messageToReturn;
        }

Dynamically Renaming or re-writing ASPX page name in asp.net

1. Add global.asax file and include this code
here i will rename MP4B.aspx and will put logo and content of page dynamically
 void Application_BeginRequest(object sender, EventArgs e)
    {
        string fullOrigionalpath = Request.Url.ToString();
        if (fullOrigionalpath.Contains("/Mobile4Business/"))
        {
            Context.RewritePath("/Mobile4Business/MP4B.aspx");
        }
       
    } 

 private string GetPageName(string url)
    {
        int startIndex = url.LastIndexOf('/');
        startIndex = startIndex + 1;
        int endindex = url.LastIndexOf('?');
        if (endindex == -1)
        { endindex = url.Length;
        }
        return url.Substring(startIndex, (endindex - startIndex));
    }

2.  write code in code behind ....Here you can use your own code for binding page
 protected void Page_Load(object sender, EventArgs e)
    {
      
      if(!IsPostBack)
     {
            string fullOrigionalpath = Request.RawUrl.ToString();
            string rawURL = GetPageName(fullOrigionalpath);
            string[] RID = rawURL.Split('.');
             Session["RID"] = Convert.ToString( Convert.ToInt32(RID[0]));
            fillpageinfo();
        
      }
    }
    private void fillpageinfo()
    {
       
           SqlDataReader dr;
           dr = objReseller.GetRsellerDetails();
           if(dr.HasRows)
            {
                dr.Read();
                HtmlImage RLogo1 = (HtmlImage)this.Page.FindControl("RLogo");
                RLogo1.Src = "..\\images\\" + dr["LogoFileName"].ToString();
                ResellerPageText.InnerHtml = dr["PageContent"].ToString();
          
            }
     
      
    }

 private string GetPageName(string url)
    {
        int startIndex = url.LastIndexOf('/');
        startIndex = startIndex + 1;
        int endindex = url.LastIndexOf('?');
        if (endindex == -1)
        {
            endindex = url.Length;
        }
        return url.Substring(startIndex, (endindex - startIndex));
    }

Progress bar using Ajax

1. Add Scipt manager
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>

2.Add update panel and add ajax progress in it and file upload control in it
 <asp:UpdatePanel ID="UpdatePanel1" runat="server">
  <ContentTemplate>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                                    <td class="middleFormField2">
                                        <asp:Label ID="lblNew" Text="Upload New Dictation:" runat="server"></asp:Label>
                                        <asp:Label ID="lblUpdate" Text="File Name:" runat="server"></asp:Label><strong><font
                                            color="#FF0000">*</font></strong>
                                    </td>
                                    <td align="left" class="middleFormField2">
                                        <table>
                                            <tr>
                                                <td valign="top">
                                                    <asp:UpdateProgress ID="UpdateProgress2" AssociatedUpdatePanelID="UpdatePanel1" runat="server">
                                                        <ProgressTemplate>
                                                            <div style="visibility: hidden" id="progressBarDiv">
                                                                <font style="color: #000000" color="#336633" size="2"></font>
                                                                <img src="images/preloader.gif" />
                                                            </div>
                                                        </ProgressTemplate>
                                                    </asp:UpdateProgress>
                                                </td>
                                                <td>
                                                    <asp:FileUpload ID="txtUploadDictation" runat="server" Font-Names="verdana" Font-Size="Small" />
                                                    <asp:RequiredFieldValidator ID="txtUploadDictation_validate" runat="server" ControlToValidate="txtUploadDictation"
                                                        Display="None" ErrorMessage="You have not uploaded a file for dictation. Please upload a file to proceed"
                                                        SetFocusOnError="true" ValidationGroup="client" />
                                                    <asp:Label ID="lblFileName" runat="server" Font-Bold="true" Text=""></asp:Label>
                                                    <div style="visibility: hidden" id="progressBarDivText">
                                                        <font style="color: Purple" size="1">Processing, Please Wait...</font>
                                                    </div>
                                                </td>
                                            </tr>
                                        </table>
                                    </td>
                                </tr>
<tr>
                                    <td colspan="2" valign="top" class="middleFormField">
                                        <div align="center">
                                            <asp:ImageButton ID="submitButton" runat="server" ImageUrl="images/submitDictationBtn.gif"
                                                CausesValidation="true" Width="137" Height="20" border="0" OnClick="submitButton_Click"
                                                OnClientClick="javascript:showWait();" ValidationGroup="client" />
                                        </div>
                                    </td>
                                </tr>
</table>
</ContentTemplate>
 <Triggers>
                <asp:PostBackTrigger ControlID="submitButton"></asp:PostBackTrigger>
            </Triggers>
        </asp:UpdatePanel>
</asp:UpdatePanel>

3.Add Javascript which will fire on submit button
 <script language="javascript" type="text/javascript">
 function showWait() {
            var FUpload = document.getElementById('<%= txtUploadDictation.ClientID %>');
            if (FUpload.value.length > 0) {
                $get('ctl00_pageContent_UpdateProgress2').style.display = 'block';
            }
            var div = document.getElementById('progressBarDiv');
            div.style.visibility = 'visible';
            document.getElementById('progressBarDiv').innerHTML = '<img src=images/preloader.gif><font size=2></font>';1. Add Scipt manager
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>

2.Add update panel and add ajax progress in it and file upload control in it
 <asp:UpdatePanel ID="UpdatePanel1" runat="server">
  <ContentTemplate>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                                    <td class="middleFormField2">
                                        <asp:Label ID="lblNew" Text="Upload New Dictation:" runat="server"></asp:Label>
                                        <asp:Label ID="lblUpdate" Text="File Name:" runat="server"></asp:Label><strong><font
                                            color="#FF0000">*</font></strong>
                                    </td>
                                    <td align="left" class="middleFormField2">
                                        <table>
                                            <tr>
                                                <td valign="top">
                                                    <asp:UpdateProgress ID="UpdateProgress2" AssociatedUpdatePanelID="UpdatePanel1" runat="server">
                                                        <ProgressTemplate>
                                                            <div style="visibility: hidden" id="progressBarDiv">
                                                                <font style="color: #000000" color="#336633" size="2"></font>
                                                                <img src="images/preloader.gif" />
                                                            </div>
                                                        </ProgressTemplate>
                                                    </asp:UpdateProgress>
                                                </td>
                                                <td>
                                                    <asp:FileUpload ID="txtUploadDictation" runat="server" Font-Names="verdana" Font-Size="Small" />
                                                    <asp:RequiredFieldValidator ID="txtUploadDictation_validate" runat="server" ControlToValidate="txtUploadDictation"
                                                        Display="None" ErrorMessage="You have not uploaded a file for dictation. Please upload a file to proceed"
                                                        SetFocusOnError="true" ValidationGroup="client" />
                                                    <asp:Label ID="lblFileName" runat="server" Font-Bold="true" Text=""></asp:Label>
                                                    <div style="visibility: hidden" id="progressBarDivText">
                                                        <font style="color: Purple" size="1">Processing, Please Wait...</font>
                                                    </div>
                                                </td>
                                            </tr>
                                        </table>
                                    </td>
                                </tr>
<tr>
                                    <td colspan="2" valign="top" class="middleFormField">
                                        <div align="center">
                                            <asp:ImageButton ID="submitButton" runat="server" ImageUrl="images/submitDictationBtn.gif"
                                                CausesValidation="true" Width="137" Height="20" border="0" OnClick="submitButton_Click"
                                                OnClientClick="javascript:showWait();" ValidationGroup="client" />
                                        </div>
                                    </td>
                                </tr>
</table>
</ContentTemplate>
 <Triggers>
                <asp:PostBackTrigger ControlID="submitButton"></asp:PostBackTrigger>
            </Triggers>
        </asp:UpdatePanel>
</asp:UpdatePanel>

3.Add Javascript which will fire on submit button
 <script language="javascript" type="text/javascript">
 function showWait() {
            var FUpload = document.getElementById('<%= txtUploadDictation.ClientID %>');
            if (FUpload.value.length > 0) {
                $get('ctl00_pageContent_UpdateProgress2').style.display = 'block';
            }
            var div = document.getElementById('progressBarDiv');
            div.style.visibility = 'visible';
            document.getElementById('progressBarDiv').innerHTML = '<img src=images/preloader.gif><font size=2></font>';
            var div1 = document.getElementById('progressBarDivText');
            div1.style.visibility = 'visible';
            //document.getElementById('progressBarDiv').innerHTML = '<img src=images/Progress4.gif><font size=2></font>';
        }
</script>

            var div1 = document.getElementById('progressBarDivText');
            div1.style.visibility = 'visible';
            //document.getElementById('progressBarDiv').innerHTML = '<img src=images/Progress4.gif><font size=2></font>';
        }
</script>