Decode URL using SQL Server

Create FUNCTION [dbo].[UrlDecode](@url varchar(max))
RETURNS varchar(MAX)
AS
BEGIN
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(MAX)
    SET @count = Len(@url)
    SET @i = 1
    SET @urlReturn = ''
    WHILE (@i <= @count)
     BEGIN
        SET @c = substring(@url, @i, 1)
        IF @c LIKE '[!%]' ESCAPE '!'
         BEGIN
            SET @cenc = substring(@url, @i + 1, 2)
            SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]'
                                THEN CAST(SUBSTRING(@cenc, 1, 1) as int)
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 1, 1)))-55 as int)
                            END * 16 +
                            CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]'
                                THEN CAST(SUBSTRING(@cenc, 2, 1) as int)
                                ELSE CAST(ASCII(UPPER(SUBSTRING(@cenc, 2, 1)))-55 as int)
                            END)
            SET @urlReturn = @urlReturn + @c
            SET @i = @i + 2
         END
        ELSE
         BEGIN
            SET @urlReturn = @urlReturn + @c
         END
        SET @i = @i +1
     END
    RETURN @urlReturn
END

Check given string is Guid or not in SQL Server

Below function is used to find whether given string is Valid Guid or not.


Create function [dbo].[IsGuid] ( @testString varchar(38))
returns int
as
begin
    declare @ret int
    select  @ret = 0,
            @testString = replace(replace(@testString, '{', ''), '}', '')
    if len(isnull(@testString, '')) = 36 and
       @testString NOT LIKE '%[^0-9A-Fa-f-]%' and
       -- check for proper positions of hyphens (-) 
       charindex('-', @testString) = 9 and
       charindex('-', @testString, 10) = 14 and
       charindex('-', @testString, 15) = 19 and
       charindex('-', @testString, 20) = 24 and
       charindex('-', @testString, 25) = 0
          set @ret = 1
   
    return @ret
end



Usage

select  dbo.isGuid('some string')

Get Number of Records count based on the search string

The following Store procedure can be used for following purpose. you can modified based on your requirement.

1.Get number of records based on the search string
2.Execute the SQL Query inside the store procedure.
3.Using the view inside the Store procedure.

Create PROCEDURE [dbo].[GetCount]
(
    @Id uniqueidentifier=null,
    @SearchCriteria nvarchar(max) = null,
    @ApplicationId uniqueidentifier
)
as
Begin
DECLARE @nparam NVARCHAR(40)
DECLARE @Query nvarchar(max)
SELECT @Query = '
      SELECT
      Count(Id)
    From [vwGetAllQuestion] WHERE ID = isnull(@Id,Id)' + isnull(@SearchCriteria,'')
    set @nparam = '@Id uniqueidentifier'

--select @Query
EXEC sp_executesql @Query, @nparam, @Id

end

Export Data Table to CSV file with comma values

public void WriteCSVToResponse(DataTable dt, string fileName, string delimiter)
    {
        //prepare the output stream
        Response.Clear();
        Response.ContentType = "text/csv";
        Response.AppendHeader("Content-Disposition",
            string.Format("attachment; filename={0}", fileName));
      
        //write the csv column headers
        if (dt != null && dt.Rows.Count > 0)
        {
           
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName != "FormsResponseXML")
                {
                    Response.Write(dt.Columns[i].ColumnName);
                    Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                }
            }

            //write the data
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName != "FormsResponseXML")
                    {                       
                        var append = row[i].ToString().Contains(",")? string.Format("\"{0}\"", row[i].ToString()): row[i].ToString();
                        Response.Write(append);
                        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                    }
                }
            }
        }

        Response.End();
    }

Convert DataTable to CSV file

 public void WriteCSVToResponse(DataTable dt, string fileName, string delimiter)
    {
        //prepare the output stream
        Response.Clear();
        Response.ContentType = "text/csv";
        Response.AppendHeader("Content-Disposition",
            string.Format("attachment; filename={0}", fileName));

        //write the csv column headers
        if (dt != null && dt.Rows.Count > 0)
        {
           
            for (int i = 0; i < dt.Columns.Count; i++)
            {
               
                    Response.Write(dt.Columns[i].ColumnName);
                    Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
               
            }

            //write the data
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                   
                        Response.Write(row[i].ToString());
                        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                   
                }
            }
        }

        Response.End();
    }

What is an application object and where we use application object in asp.net | application object sample using asp.net

What is an application object?

Application object is used to store the information and access variables from any page in application. Application object is same as session object only the difference is session object is used to maintain the session for particular user. If one user enters in to the application then session id will create for that particular user if he leaves from the application then the session id will deleted.  they will get different session id but application object is same for all users once application object is created that application object is used throughout the application regardless of user. The information stored in application object accessed throughout all the pages in application (like database connection information) and we can change the application object in one place those changes automatically reflected in all the pages.

How to Make by pressing Enter Key ASP Button fires Click Event using JQUERY

<div class="headerSearch formPanel" formButtonId="<%=SearchButton1.ClientID %>">

<label for="txtSearch">
Product Search:</label>
<input type="text" id="TextBox1" class="text" value="Enter Keyword" runat="server"
onfocus="clearText(this.id)" />
<div class="button buttonBlack">
<asp:LinkButton ID="SearchButton1" runat="server" OnClientClick="return ProductSearch();"
OnClick="SearchButton1_Click" Text="<span></span>Go" />
</div>
</div >


Include this JQUERY in your ASPX page


$(function(){
$("div.formPanel").each(function()
{
if($(this).attr("formButtonId"))
{
$(this).bind("keypress", function(e){
if (e.keyCode == 13)
{
e.returnValue = false;
e.cancel = true;
var button = $("#" + $(this).attr("formButtonId"));

var retValue = button.triggerHandler("click");
if(retValue == undefined || retValue == true)
eval(button.attr("href"));

return false;
}
});
}
});
});