Executing stored procedure or query through batch file

(1) Create a batch file with .bat extension.
Open a notepad and save it as .bat instead of .txt
Now this file becomes executable. I gave EXEC.bat as filename.
Write the following text inside the bat file.
----------------------------------------

@ECHO OFF

SQLCMD -S yourservername -d database_name -U username -P password -i "C:\Documents and Settings\sudiptasanyal\Desktop\TEST\some.sql" -o "C:\Documents and Settings\sudiptasanyal\Desktop\TEST\output.txt"

----------------------------------------

 SQLCMD - It is another means to execute queries and do lot more that we perform in SQL Server 

but in COMMAND PROMPT.

    -S is used to provide the server name that i'm pointing to[Here i'm pointing to my local system only].
    -d is the database.

    -U is user to provide the server username.

    -P is used to provide the server password.

    -i is used to provide input file location/path to SQLCMD from which it executes. Here i'm taking on .sql file which contains multiple queries.

    -o is used to provide output file location/path to SQLCMD where the messages from SQL Server 

----------------------------------------

(2) Now open notepad and save it with .sql extension. Write whatever queries that you want to execute here in this file which needs to be executed.

in my case i have written like this(remove these lines)

----------------------------------------

USE yourdatabasename

GO

SELECT member_id,last_name,first_name FROM tbl_member 

----------------------------------------

File Delete using jQuery in ASP.NET

In the page body add 
<script language="javascript" type="text/javascript" src="jquery_bundle/jquery-1.9.1.js"></script>
<input id="Button1" type="button" value="delete" runat="server" onclick="deleteFile();" />
<script language="javascript" type="text/javascript">
    function deleteFile() {
        var fileName = "1st.jpg";
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "WebService.asmx/deleteFile",
            data: "{'fileName':'" + fileName + "'}",
            dataType: "json",
            async: true,
            success: function(data) {
            alert(data.d);
            },
            error: function(result) {
                alert("Error");
            }
        });
    }
</script>
Add a webservice
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Web.Script.Serialization;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]

public class WebService : System.Web.Services.WebService
{
    public WebService()
    {
    }
   [WebMethod]
    public IList<string> deleteFile(string fileName)
    {
        IList<string> result = new List<string>();
        try
        {
            string completePath = Server.MapPath("~/upload/" + fileName);
            if (System.IO.File.Exists(completePath))
            {
                System.IO.File.Delete(completePath);
            }
            result.Add("Y");
            return result;
        }
        catch
        {
            return null;
        }
    }
}

How to Encoding and Decoding Base64 strings in C# and SQL server

In C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
public class encode_decode
{
    public static string EncodeTo64(string toEncode)
    {
        byte[] toEncodeAsBytes = System.Text.Encoding.UTF8.GetBytes(toEncode);
        string returnValue = System.Convert.ToBase64String(toEncodeAsBytes);
        return returnValue;
    }
    public static string DecodeFrom64(string encodedData)
    {
        byte[] encodedDataAsBytes = System.Convert.FromBase64String(encodedData);
        string returnValue = System.Text.Encoding.UTF8.GetString(encodedDataAsBytes);
        return returnValue;
    }
}
In SQL server
CREATE  FUNCTION [dbo].[fn_base64_encode_decode]
(
@in_mode INT ,
@in_input VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @encoded_decode_base64 VARCHAR(MAX),@source VARBINARY(MAX)
IF @in_mode = 1 --ENCODE
BEGIN
SET @source = CONVERT(VARBINARY(MAX), @in_input)
SELECT @encoded_decode_base64 = CAST(N'' AS
                xml).value('xs:base64Binary(sql:variable("@source"))', 'VARCHAR(MAX)')
END
IF @in_mode = 2 --DECODE
BEGIN
SELECT @encoded_decode_base64 = CAST(N'' AS
                xml).value('xs:base64Binary(sql:variable("@in_input"))', 'VARBINARY(MAX)')
END
RETURN @encoded_decode_base64
END

Autocomplete TextBox from database using jQuery in ASP.NET

We need to jQuery package from http://jqueryui.com/ and jQuery-UI plugin and jQuery styles.
Download the packages http://jqueryui.com/autocomplete
In VS 2008 or 2010 Create web site, in the page header add jQuery packages and style as a following:

<link rel="stylesheet" href="jquery_bundle/themes/base/jquery.ui.all.css" />
<script language="javascript" type="text/javascript" src="jquery_bundle/jquery-1.9.1.js"></script>
<script language="javascript" type="text/javascript" src="jquery_bundle/ui/jquery.ui.core.js"></script>
<script language="javascript" type="text/javascript" src="jquery_bundle/ui/jquery.ui.widget.js"></script><script language="javascript" type="text/javascript" src="jquery_bundle/ui/jquery.ui.position.js"></script>
<script language="javascript" type="text/javascript" src="jquery_bundle/ui/jquery.ui.autocomplete.js"></script>
<script language="javascript" type="text/javascript" src="jquery_bundle/ui/jquery.ui.menu.js"></script>
<link rel="stylesheet" href="jquery_bundle/themes/demos.css" />
    <style>
     /* prevent horizontal scrollbar */
        .ui-autocomplete
        {
            max-height: 100px;
            overflow-y: auto;
            overflow-x: hidden;
        }
      * html .ui-autocomplete
        {
            height: 100px;
        }
    </style>
In the page body add TextBox Control and name it txtMemberName:

 <asp:TextBox ID="txtMemberName" runat="server" Style="padding: 5px; width: 400px;
 font: 11pt Verdana;"></asp:TextBox>
Add a webservice

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    public WebService()
    {
    }
    [WebMethod]
    public IList<string> GetAllMember(string keywords)
    {
        //TODO: implement real search here!
        IList<string> result = new List<string>();
        string constr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = con.CreateCommand();
        cmd.CommandText = "select first_name from tbl_member where first_name like '%" + keywords +      "%'";
        try
        {
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(dr["first_name"].ToString());
            }
            con.Close();
            return result;
        }
        catch
        {
            return null;
        }
    }
}
To implement autocomplete to a TextBox add the following script in the page header. This jQuery script will call the function that we declared in the web service and passing to it the typed keywords in the textbox.

<script language="javascript" type="text/javascript">
    $(document).ready(function() {
        $('#<%= txtMemberName.ClientID %>').autocomplete({
            source: function(request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "WebService.asmx/GetAllMember",
                    data: "{'keywords':'" + request.term + "'}",
                    dataType: "json",
                    async: true,
                    success: function(data) {
                        response(data.d);
                    },
                    error: function(result) {
                        //alert("Error");
                    }
                });
            }
        });
    });
</script>

Securing Images in ASP.NET c#

add new imageHandler.aspx

using System.IO;

protected void Page_Load(object sender, EventArgs e)
{
        string fileName = Request.QueryString["src"];
        long fileSize = 0;
        if (string.IsNullOrEmpty(fileName))
        {
            fileName = "empty.gif";
        }
        fileName = Server.MapPath("..\\images\\" + fileName);
        FileStream fileStream = default(FileStream);
        fileStream = new FileStream(fileName, FileMode.Open);
        fileSize = fileStream.Length;
        byte[] Buffer = new byte[Convert.ToInt32(fileSize) + 1];
        fileStream.Read(Buffer, 0, Convert.ToInt32(fileSize));
        fileStream.Close();
        Response.BinaryWrite(Buffer);
}

add new default.aspx
<img src="imageHandler.aspx?src=1.jpg" />

Replace all special characters in c# from string

Solution 1 : 
using System.Text.RegularExpressions;
public string RemoveSpecialCharacters(string str)
{
    return Regex.Replace(str, "[^a-zA-Z0-9_.]+", "", RegexOptions.Compiled);
}

Solution 2 :

public string RemoveSpecialChars(string str)
{
    string[] chars = new string[] { ",", ".", "/", "!", "@", "#", "$", "%", "^", "&", "*", "'", "\"", ";","_","(", ")", ":", "|", "[", "]" };

  for (int i = 0; i< chars.Lenght; i++)
  {
if (str.Contains(chars[i]))
{
str = str.Replace(chars[i],"");
}
   }
return str;
}

Way in asp.net c# to force https for an entire site?

protected void Application_BeginRequest(Object sender, EventArgs e)
{
   if (HttpContext.Current.Request.IsSecureConnection.Equals(false) &&      
   HttpContext.Current.Request.IsLocal.Equals(false))
   {
       Response.Redirect("https://" + Request.ServerVariables["HTTP_HOST"]
       + HttpContext.Current.Request.RawUrl);
   }
}


That would go in the global.asax.cs