SQL Server Database BackUp Using Query

DECLARE @name VARCHAR(50) -- database name  DECLARE @path VARCHAR(256) -- path for backup files  DECLARE @fileName VARCHAR(256) -- filename for backup  DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE   @path  VARCHAR(50)= 'D:\op\Cooper\DB\OKTest\'
DECLARE  @fileDate VARCHAR(50)= CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE  @filename  VARCHAR(100)=null
DECLARE db_cursor CURSOR FOR  SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name  
 WHILE @@FETCH_STATUS = 0   BEGIN          SET @fileName = @path + @name + '_' + @fileDate + '.BAK'         BACKUP DATABASE @name TO DISK = @fileName 
        FETCH NEXT FROM db_cursor INTO @name   END  
 CLOSE db_cursor   DEALLOCATE db_cursor

HTTP Handler in ASP.NET 3.5

 What is HTTP handler: Every request into an ASP.NET application is handled by a specialized component known as an HTTP handler. The HTTP handler is the most important ingredient while handling ASP.NET requests.


HttpHanlder is the low level Request and Response API to service incoming Http requests. All handlers implement the IHttpHandler interface. There is no need to use any extra namespace to use it as it contains in the System.Web namespace. Handlers are somewhat analogous to Internet Server Application Programming Interface (ISAPI) extensions.


Each incoming HTTP request received by ASP.NET is ultimately processed by a specific instance of a class that implements IHTTPHanlder. IHttpHanlderFactory provides the infrastructure that handles the actual resolution of URL requests to IHttpHanlder instances. In addition to the default IHttpHandlerFactory classes provided by ASP.NET, developers can optionally create and register factories to support rich request resolution.

Examples: ASP.NET uses different HTTP handlers to serve different file types. For example, the handler for web Page creates the page and control objects, runs your code, and renders the final HTML.

ASP.NET default handlers:

1. Page Handler (.aspx) - Handles Web pages
2. User Control Handler (.ascx) - Handles Web user control pages
3. Web Service Handler (.asmx) - Handles Web service pages
4. Trace Handler (trace.axd) - Handles trace functionality

Why we need to create our own HTTP Handler: Sometime we need to avoid ASP.NET full page processing model, which saves lot of overheads, as ASP.NET web form model has to go through many steps such as creating web page objects, persisting view state etc. What we are interested into is to develop some low level interface that provides access to objects like Request and Response but doesn't use the full control based web form model discussed above.

Examples:

1. Dynamic image creator - Use the System.Drawing classes to draw and size your own images.
2. RSS - Create a handler that responds with RSS-formatted XML. This would allow you to add RSS feed capabilities to your sites.
3. Render a custom image,
4. Perform an ad hoc database query,
5. Return some binary data.

These examples extend the ASP.NET architecture but bypass the web-page model. The result is a leaner, more efficient component.

Where HTTP handlers are defined: All HTTP handlers are defined in the <httpHandlers> section of a configuration file which is nested in the <system.web> element.

<httpHandlers>
<add verb="*" path="trace.axd" validate="true" type="System.Web.Handlers.TraceHandler"/>
<add verb="*" path="*.config" validate="true" type="System.Web.HttpForbiddenHandler"/>
<add verb="*" path="*.cs" validate="true" type="System.Web.HttpForbiddenHandler"/>
<add verb="*" path="*.aspx" validate="true" type="System.Web.UI.PageHandlerFactory"/>
</httpHandlers>

1. Trace.axd : this handler is meant for rendering HTML page with a list of all the recently collected trace output, and this is handled by TraceHandler
2. .config: Handled by HttpForbiddenHandler
3. .cs: Handled by HttpForbiddenHandler
4. .aspx: Handled by PageHandlerFactory, which isn't a HTTP handler rather it's a class that will create the appropriate HTTP handler.

What is HTTP module: Help in processing of page request by handing application events , similar to what global.asax does. A request can pass through many HTTP modules but is being handled by only one HTTP handlers.



Use of HTTP Modules:

1. ASP.NET uses HTTP modules to enable features like caching, authentication, error pages etc.
2. <add> and <remove> tags can be used to add and inactive any http module from <httpmodule> section of a configuration file.

<httpModules>
<add name="OutputCache" type="System.Web.Caching.OutputCacheModule"/>
<add name="Session" type="System.Web.SessionState.SessionStateModule"/>
<add name="WindowsAuthentication"
type="System.Web.Security.WindowsAuthenticationModule"/>
<add name="FormsAuthentication"
type="System.Web.Security.FormsAuthenticationModule"/>
</httpModules>

Note:

1. HTTP handler plays same role what ISAPI extension
2. HTTP module plays same role what ISAPI filters does.

How to write custom HTTP handler:

Step 1: What all we need to know before writing handlers

There are two type of handler you actually can make

1. Synchronous handler , which implement IHttpHandler interface
2. Asynchronous handler, which implement IHttpAsyncHandler. With asynchronous handlers additional requests can be accepted, because the handler creates a new thread to process each request rather than using the worker process

Further these Interfaces require us to implement the ProcessRequest method and the IsReusable property.

1. The ProcessRequest method handles the actual processing for requests made.
   ASP.NET calls this method when a request is received. It's where the HTTP handlers perform all the processing. You can access the intrinsic ASP.NET objects (such as Request, Response, and Server) through the HttpContext object that's passed to this method.
    
2. Boolean IsReusable property specifies whether your handler can be pooled for reuse (to increase performance) or whether a new handler is required for each request.
   After ProcessRequest() finishes its work, ASP.NET checks this property to determine whether a given instance of an HTTP handler can be reused. If it returns true, the HTTP handler object can be reused for another request of the same type current. If it returns false, the HTTP handler object will simply be discarded.

Step 2: Create a ASP.NET web application and name it: SimpleHTTPHanlder

Step 3: Add a class and name it "SimpleHandler"

Step 4: Write below code



Here we are implementing IHttpHandler

Full code look like below

public class SimpleHandler : IHttpHandler
    {
        #region IHttpHandler Members

        bool IHttpHandler.IsReusable
        {
            get { return true; }
        }

        void IHttpHandler.ProcessRequest(HttpContext context)
        {
            HttpResponse response = context.Response;
            response.Write("<html><body><h1>Wow.. We created our first handler");
            response.Write("</h1></body></html>");
        }

        #endregion
    }

Step 5: Configuring HTTP handler in configuration file;

We will add our handler in <httpHandlers> section

<httpHandlers>         
<add verb="*" path="vishal.nayan" type="MyHttpHandler.SimpleHandler,MyHttpHandler"/>
</httpHandlers>

Now here we need to understand what these attributes means

1. Verb: indicates whether the request is an HTTP POST or HTTP GET request (use * for all requests).
2. Path : indicates the file extension that will invoke the HTTP handler
3. Type: identifies the HTTP handler class. This identification consists of two portions. First is the fully qualified class name , That portion is followed by a comma and the name of the DLL assembly that contains the class

Step 6: How to run our Handler

Well, visual studio doesn't allow us directly to run the handler, rather we need to run the project first and then explicitly type URL to request for handler, so in our case it is;

http://localhost:3238/myhttphandler/vishal.nayan



Now, can we create HTTPHanlder without configuring web.config file. Well yes. In visual studio we have a new item template to accomplish this. For this we can use the recognized extension .ashx. All requests that end in .ashx are automatically recognized as requests for a custom HTTP handler.



What is leeching: sites that steal bandwidth by linking to resources on your server .i.e. giving an image URL path from their server to your server, so when user tries to access that image, it's actually using your server resources.

Problem Scenario: As stated above, when any request for image is originated from same website then it's ok, but if the request is coming from another website, then there is a potential problem, as it is creating more work for your web server and reducing the bandwidth.

Solution: HTTP handler will refuse to serve the image or they substitute a dummy image if the referrer header indicates that a request originates from another site.

Add a generic handler from existing item and name it ImageHandler.ashx. Write the code below;

public class ImageHandler : IHttpHandler
{
        public void ProcessRequest(HttpContext context)
        {
            HttpRequest request = context.Request;
            HttpResponse response = context.Response;

            string imageURL = null;
            // Perform a case-insensitive comparison.
            if (request.UrlReferrer != null)
            {
                if(String.Compare(request.Url.Host, request.UrlReferrer.Host,true,CultureInfo.InvariantCulture) ==0)
                {
                    // The requesting host is correct.
                    // Allow the image (if it exists).
                    imageURL = request.PhysicalPath;
                    if (!File.Exists(imageURL))
                    {
                        response.Status = "Image Not Found";
                        response.StatusCode = 404;
                    }
                    else
                    {
                    }
                }
            }
            if (imageURL == null)
            {
                // No valid image was allowed.
                // Use the warning image instead.
                // Rather than hard-code this image, you could
                // retrieve it from the web.config file
                // (using the <appSettings> section or a custom
                // section).
                imageURL = context.Server.MapPath("~/images/notallowed.gif");
            }
            // Serve the image
            // Set the content type to the appropriate image type.
            response.ContentType = "image/" + Path.GetExtension(imageURL).ToLower();
            response.WriteFile(imageURL);
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
}

Now its time to configure it. For this handler to protect image files, you need to register it to deal with the appropriate file types. Here are the web.config settings that set this up for the .gif and .jpg file types only.

<add verb="*" path="*.gif" type="SimpleHTTPHanlder.ImageHandler,SimpleHTTPHanlder"/>
<add verb="*" path="*.jpg" type="SimpleHTTPHanlder.ImageHandler,SimpleHTTPHanlder"/>

Now this configuration setting will only allow jpg and gif images, after checking it the request is originating from same website.

So now when we have to request any image , we simple type image URL and we can see that
http://localhost:3238/images/areng.jpg



The one which is not available, is greeted by not found image. Say for this image request which doesn't exists;
http://localhost:3238/images/areds.jpg



So by this HTTP handler for images, we can restrict any request which is not coming from our web site and also increase performance by quicker response time.

How to get Asp.Net TreeView Selected Node Text In Java Script/ Client End

 Add In C#
 if (!IsPostBack)
        {
 SqlTree.Attributes.Add("onclick", "return OnTreeClick(event)");
       }


In Default .aspx

<script type="text/javascript">
function OnTreeClick(evt)
 {      
   var src = window.event != window.undefined ? window.event.srcElement : evt.target;
   var nodeClick = src.tagName.toLowerCase() == "a";
     if (nodeClick)
      {
        //innerText works in IE but fails in Firefox (I'm sick of browser anomalies), so use innerHTML as well
        var nodeText = src.innerText || src.innerHTML;
      Alert(nodeText);
      }
    return false; //comment this if you want postback on node click
 }

    </script>



    <div style="border:1px solid; height:260px; width:260px; overflow:auto; padding:3px 3px 3px 3px;" >
                                                                        <asp:TreeView ID="SqlTree"
                                                                CssClass="example3" Style="text-align: left; vertical-align:top;"
                                                                          NodeWrap="true"   ShowLines="true"  ShowExpandCollapse="true" runat="server" Width="250px"  Height="250px"
                                                                >
                                                                        </asp:TreeView></div>        

delete duplicate records from a datatable in SQL server.

During work with one application I got requirement like get the unique records from datatable in sql server. Actually our datatable does not contain any primary key column because of that it contains duplicate records that would be like this

Actually above table does not contain any primary key column because of that same type of records exist.

Now I want to get duplicate records from datatable for that we need to write query like this

WITH tempTable as ( SELECT ROW_NUMBER() Over(PARTITION BY Name,Position ORDER BY Name) As RowNumber,* FROM EmployeData ) SELECT * FROM tempTable

Once we run above query we will get data like this

If you observe above table I added another column RowNumber this column is used to know which record contains duplicate values based on rows with RowNumber greater than 1.  

Now we want to get the records which contains unique value from datatable for that we need to write the query like this

WITH tempTable as ( SELECT ROW_NUMBER() Over(PARTITION BY Name,Position ORDER BY Name) As RowNumber,* FROM EmployeData ) DELETE FROM tempTable where RowNumber >1 SELECT * FROM EmployeData order by Id asc

Once we run above query all duplicate records will delete from our table and that would be like this

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This error message is due to the server setting of Remote Connection Query Timeout. The default is 600 seconds, or 10 minutes.

EXEC SP_CONFIGURE 'remote query timeout', 1800
reconfigure
EXEC sp_configure
EXEC SP_CONFIGURE 'show advanced options', 1
reconfigure
EXEC sp_configure
EXEC SP_CONFIGURE 'remote query timeout', 1800
reconfigure
EXEC sp_configure

Asp.net Preventing SQL Injection Attacks using Parameterized Queries

Asp.net Preventing SQL Injection Attacks using Parameterized Queries

 C# Code

SqlCommand cmd = new SqlCommand("select Name,Total=value from countrydetails where value =@value", con);

cmd.Parameters.AddWithValue("@value", txtSearch.Text);

VB.NET Code

Dim

cmd As New SqlCommand("select Name,Total=value from countrydetails where value =@value", con)

cmd.Parameters.AddWithValue("@value", txtSearch.Text)

To know more about how SQL injection occurs check this article SQL injection Attacks in Asp.net.

If you want to check example to prevent SQL injection attacks first design one table countrydetails in your database like as shown below Column Name

Data Type

Allow Nulls

ID   Int(set identity property=true)

name  Varchar(50)

value   Int

Once we create table we need to enter some dummy data for our application purpose

Now in your Default.aspx page write the following code

<html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>Prevent SQL Injection Attacks in Asp.net Website</title> </head> <body> <form id="form1" runat="server"> <div> Enter Count:<asp:TextBox ID="txtSearch" runat="server" /> <asp:Button ID="btnsearch" Text="Search" runat="server" onclick="btnsearch_Click" /> <br /><br /> <asp:GridView ID="gvDetails" CellPadding="5" runat="server"> <HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" /> </asp:GridView> </div> </form> </body> </html>

After completion of aspx page write the following code in codebehind

C# Code

using System; using System.Data; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnsearch_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")) { con.Open(); SqlCommand cmd = new SqlCommand("select Name,Total=value from countrydetails where value =@value", con); cmd.Parameters.AddWithValue("@value", txtSearch.Text); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); con.Close(); gvDetails.DataSource = dt; gvDetails.DataBind(); } } }

VB.NET Code

Imports System.Data Imports System.Data.SqlClient Partial Class VBcode Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) End Sub Protected Sub btnsearch_Click(ByVal sender As Object, ByVal e As EventArgs) Dim dt As New DataTable() Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB") con.Open() Dim cmd As New SqlCommand("select Name,Total=value from countrydetails where value =@value", con) cmd.Parameters.AddWithValue("@value", txtSearch.Text) Dim da As New SqlDataAdapter(cmd) da.Fill(dt) con.Close() gvDetails.DataSource = dt gvDetails.DataBind() End Using End Sub End Class

When we run above code we will get output like as shown below

Demo

Convert Amount Into Words / Number into words

// Calling Function
  string a = commonobj.ConvertNumerictoWords("21545.456");
               Output is = TWENTY ONE THOUSAND FIVE HUNDRED  FORTY FIVE DIHRAM AND FOUR HUNDRED  FIFTY SIX FILLS ONLY

//In Toggle style
  string b=System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(a.ToLower());
        Output is =Twenty One Thousand Five Hundred  Forty Five Dihram And Four Hundred  Fifty Six Fills Only


 public string ConvertNumerictoWords(string Values)
    {
        String number = "ZERO";
        string Floats = "ZERO";
        if (Values.Contains("."))
        {
            bool isNum;
            double retNum;
            isNum = Double.TryParse(Values, System.Globalization.NumberStyles.Any, System.Globalization.NumberFormatInfo.InvariantInfo, out retNum);
            if (isNum == true)
            {
                string[] Text = Values.Split('.');
                number = ConvertNumbertoWords(Convert.ToInt32(Text[0].ToString()));
                Floats = ConvertNumbertoWords(Convert.ToInt32(Text[1].ToString()));
            }
            else
                return "Amount is not Valid!";
        }
        else
        {
            bool isNum;
            double retNum;
            isNum = Double.TryParse(Convert.ToString(Values), System.Globalization.NumberStyles.Any, System.Globalization.NumberFormatInfo.InvariantInfo, out retNum);
            if (isNum == true)
            {
                number = ConvertNumbertoWords(Convert.ToInt32(Values));
            }
            else
                return "Amount is not Valid!";
        }
        return number + " DIHRAM AND " + Floats + " FILLS ONLY";
    }






 // Convert Number into Words


   public string ConvertNumbertoWords(int number)
    {
        if (number == 0)
            return "ZERO";
        if (number < 0)
            return "minus " + ConvertNumbertoWords(Math.Abs(number));
        string words = "";
        if ((number / 1000000) > 0)
        {
            words += ConvertNumbertoWords(number / 1000000) + " MILLION ";
            number %= 1000000;
        }
        if ((number / 1000) > 0)
        {
            words += ConvertNumbertoWords(number / 1000) + " THOUSAND ";
            number %= 1000;
        }
        if ((number / 100) > 0)
        {
            words += ConvertNumbertoWords(number / 100) + " HUNDRED ";
            number %= 100;
        }
        if (number > 0)
        {
            if (words != "")
                words += " ";
            var unitsMap = new[] { "ZERO", "ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE", "TEN", "ELEVEN", "TWELVE", "THIRTEEN", "FOURTEEN", "FIFTEEN", "SIXTEEN", "SEVENTEEN", "EIGHTEEN", "NINETEEN" };
            var tensMap = new[] { "ZERO", "TEN", "TWENTY", "THIRTY", "FORTY", "FIFTY", "SIXTY", "SEVENTY", "EIGHTY", "NINETY" };

            if (number < 20)
                words += unitsMap[number];
            else
            {
                words += tensMap[number / 10];
                if ((number % 10) > 0)
                    words += " " + unitsMap[number % 10];
            }
        }
        return words;
    }
   

Swaping/Updating one column data into anothe column data with JOIN SQL SERVER Query

update IE_Prescription
set IE_Prescription.GenericName = IE_Master_Medicine.GenericName
from  IE_Prescription
    inner join IE_Master_Medicine on
        IE_Prescription.GenericCode = IE_Master_Medicine.Code
        and IE_Prescription.GenericName <> IE_Master_Medicine.GenericName