Tuesday, May 1, 2012

Query for getting no of Male & Female for a particular Dept - Sql Server 2005

--create table.
create table Emp
(
            Dept varchar(2),
            Name varchar(50),
            Gender Varchar(1)
)

--Insert data into table.

insert into Emp
values('A','Test','M')

insert into Emp
values('A','Test','F')

insert into Emp
values('B','Test','M')

insert into Emp
values('B','Test','M')

insert into Emp
values('A','Test','M')

insert into Emp
values('B','Test','F')

insert into Emp
values('A','Test','M')

insert into Emp
values('C','Test','F')

insert into Emp
values('C','Test','M')

insert into Emp
values('A','Test','M')

insert into Emp
values('A','Test','F')

--query--

select Dept, SUM(Case when gender = 'M' then 1 else 0 End) as Male,SUM(Case when gender = 'F' then 1 else 0 End) as Female from Emp Group by Dept

--Result-----

Dept    Male    Female
A         4          2
B         2          1
C         1          1

Happy Coding...


Tuesday, April 17, 2012

Execute .NET Code under SQL Server 2005

Hi all,
let us start with a interesting topics - registering .Net Dll into Sql Server 2005 and call its methods.
Before starting, you have to enable the managed code execution feature of the SQL Server which is disabled by default. To enable it, execute the following code as a query in your SQL Server query editor.
sp_configure 'clr enable', 1
GO
RECONFIGURE
GO
Now Set trust worthy option of particular database
ALTER DATABASE TestingCLR SET TRUSTWORTHY ON
GO
Now register sqlServerTest.dll under that using the following code:
CREATE ASSEMBLY SqlServerTest
AUTHORIZATION dbo
FROM 'E:\Important\SQL\sqlServerTest\sqlServerTest\bin\debug\sqlServerTest.dll'
WITH PERMISSION_SET = UNSAFE
GO
please note here if you are using some functionality into your Dll like sending mails or messages.
for example if you have implemented system.messging then you need to registered assembly for system.messaging like below.
CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO
Now call this registered Dll functions using stored procedures like below..
CREATE PROCEDURE usp_UseSqlAssembly
AS
BEGIN
 @results = SqlServerTest.getResults()
END
do changes according to your need....
Happy coding...

Monday, April 16, 2012

Form submission using Post Method -by ASP.Net Handler !

The topic describes how to post a form using ASP.Net generic handlers.


public class reqTpNb : IHttpHandler {
    
    public void ProcessRequest (HttpContext context) {



 string formToSubmit = fnBuildForm("http://ahsaan-ansari@blogspot.com", strMsg);
                context.Response.Write(formToSubmit);

}

public string fnBuildForm(string url,string data)
    {
        string formID = "PostForm";
        //Build the form using the specified data to be posted.
        System.Text.StringBuilder strForm = new System.Text.StringBuilder();
        strForm.Append("<form id=\"" + formID + "\" name=\"" + formID + "\" action=\"" + url + "\"            method=\"POST\">");


        strForm.Append("<input type=\"hidden\" name=\"msg\" value=\"" + data + "\">"); 
        strForm.Append("</form>");
        
        //Build the JavaScript which will do the Posting operation.
        System.Text.StringBuilder strScript = new System.Text.StringBuilder();
        strScript.Append("<script language='javascript'>");
        strScript.Append("var v" + formID + " = document." + formID + ";");
        strScript.Append("v" + formID + ".method='post';");
        strScript.Append("v" + formID + ".action='" + url + "';");
        strScript.Append("v" + formID + ".submit();");
        strScript.Append("</script>");
        //Return the form and the script concatenated.
        //(The order is important, Form then JavaScript)
        return strForm.ToString() + strScript.ToString();         
    }


Enjoy happy coding....



Wednesday, August 10, 2011

How to work around the access denied cross-domain frame issue in ASP.NET Ajax 1.0

Some users have run into an issue when hosting ASP.NET Ajax applications in a frame or iframe that's in a different domain from the top-level window. If you try to do that and browse to the page using IE, you'll receive an "access denied" error client-side any time a DOM event is raised in the frame.
The code that's responsible for that is Sys.UI.getLocation. This is a tricky piece of code that determines the pixel coordinates of a DOM element relative to the top-left corner of the page, so that an absolutely positioned child of the body using these coordinates would exactly cover the element you measured. This is useful for drag & drop, popup scenarios like auto-complete and when an event is raised to get mouse coordinates relative to the event source.
How to fix ? 
Copy and paste the below code to bottom of page and enjoy cross domain scripting.

<script type= "text/javascript">


Sys.UI.DomElement.getLocation=function(a){if(a.self||a.nodeType===9)return new Sys.UI.Point(0,0);var b=a.getBoundingClientRect();if(!b)return new Sys.UI.Point(0,0);var c=a.document.documentElement,d=b.left-2+c.scrollLeft,e=b.top-2+c.scrollTop;try{var g=a.ownerDocument.parentWindow.frameElement||null;if(g){var f=2-(g.frameBorder||1)*2;d+=f;e+=f}}catch(h){}return new Sys.UI.Point(d,e)};
</script>

Tuesday, July 5, 2011

Removing white spaces in ASP.NET



When we request a web page it contains some data to upload on the server and download from the server.
A web page created have lots of white spaces like tabs ,space, new line etc,that make web page 10%-50% heavier in size.

For overcoming this problem,i have use a white space remover class at application level.
Add the following tag into web.config.
---------------------------------------------------------------------------

<httpModules>
      <add type="WhitespaceModule" name="WhitespaceModule"/>                   
</httpModules>
---------------------------------------------------------------------------
Add the following cs class into app_code folder of your project.

WhiteSpaceModule.cs
---------------------------------------------------------------------------

#region Using
using System;
using System.IO;
using System.Web;
using System.IO.Compression;
using System.Text.RegularExpressions;

#endregion

/// <summary>
/// Removes whitespace from the webpage.
/// </summary>
public class WhitespaceModule : IHttpModule
{
  #region IHttpModule Members
  void IHttpModule.Dispose()
  {
    // Nothing to dispose;
  }
  void IHttpModule.Init(HttpApplication context)
  {
    context.BeginRequest += new EventHandler(context_BeginRequest);
  }
  #endregion
  void context_BeginRequest(object sender, EventArgs e)
  {
    HttpApplication app = sender as HttpApplication;
    if (app.Request.RawUrl.Contains(".aspx") && app.Request.Headers["X-MicrosoftAjax"] != "Delta=true")
    {
      app.Response.Filter = new WhitespaceFilter(app.Response.Filter);
    }
  }
  #region Stream filter
  private class WhitespaceFilter : Stream
  {
    public WhitespaceFilter(Stream sink)
    {
      _sink = sink;
    }
    private Stream _sink;
    private static Regex reg = new Regex(@"(?<=[^])\t{2,}|(?<=[>])\s{2,}(?=[<])|(?<=[>])\s{2,11}(?=[<])|(?=[\n])\s{2,}");
    #region Properites
    public override bool CanRead
    {
      get { return true; }
    }
    public override bool CanSeek
    {
      get { return true; }
    }
    public override bool CanWrite
    {
      get { return true; }
    }
    public override void Flush()
    {
      _sink.Flush();
    }
    public override long Length
    {
      get { return 0; }
    }
    private long _position;
    public override long Position
    {
      get { return _position; }
      set { _position = value; }
    }
    #endregion
    #region Methods
    public override int Read(byte[] buffer, int offset, int count)
    {
      return _sink.Read(buffer, offset, count);
    }
    public override long Seek(long offset, SeekOrigin origin)
    {
      return _sink.Seek(offset, origin);
    }
    public override void SetLength(long value)
    {
      _sink.SetLength(value);
    }
    public override void Close()
    {
      _sink.Close();
    }
    public override void Write(byte[] buffer, int offset, int count)
    {
      byte[] data = new byte[count];
      Buffer.BlockCopy(buffer, offset, data, 0, count);
      string html = System.Text.Encoding.Default.GetString(buffer);
      html = reg.Replace(html, string.Empty);
      byte[] outdata = System.Text.Encoding.Default.GetBytes(html);
      _sink.Write(outdata, 0, outdata.GetLength(0));
    }
    #endregion
  }
  #endregion
}
--------------------------------------------------------------------------
Remove "&& app.Request.Headers["X-MicrosoftAjax"] != "Delta=true""
condition in if condition if you are not using partial rendering of page,means ajax.

Thursday, June 2, 2011

Combined CSS using handler is not working in firefox and chrome.

I am combining more than one CSS using a handler below.

<%@ WebHandler Language="C#" Class="HttpCombiner" %>

using System;
using System.Net;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Configuration;
using System.Web;

public class HttpCombiner : IHttpHandler {
    
    public void ProcessRequest(HttpContext context)
        {
        // Get the list of CSS files from QueryString.
        string files = context.Request.QueryString["fileName"];
        if (string.IsNullOrEmpty(files))
            {
                return; // If no file name is mentioned then don't proceed.
            }

        // Get the list of files specified in the querystring (joined by ',').
        string[] arrFileNames = files.Split(',');
        if ((arrFileNames != null) && (arrFileNames.Length > 0))
            {
            string filePath = string.Empty;

            // Read the content of each CSS file and write it in the response.
            for (int fileCount = 0; fileCount < arrFileNames.Length; fileCount++)
                {
                filePath = context.Server.MapPath(arrFileNames[fileCount]).Trim();
                if (File.Exists(filePath)) // Check if the file exists in the physical location or not.
                    {
                        context.Response.Write(File.ReadAllText(filePath));
                    }
                }
            //context.Response.ContentType = "text/css"; // Set the content type.
            }
        }
    public bool IsReusable
    {
        get
        {
            return true;
        }
    }
}

its working fine in IE 7.0 and above but not working in FireFox and Chrome.
when i include CSS Separately it working fine in all browsers.
any help or suggestion????

Tuesday, May 31, 2011

Combining Css and javascript into one http request in ASP.NET 2.0.

We always include many javascript and css file into our page.aspx file,


here is code that combined all javascript into one javascript and all CSS into one CSS  using a handler.
here is steps for doing so....


make a handler named HttpCombiner.ashx into your root directory.


//HttpCombiner.ashx



<%@ WebHandler Language="C#" Class="HttpCombiner" %>


using System;
using System.Net;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Configuration;
using System.Web;


public class HttpCombiner : IHttpHandler {
    
    public void ProcessRequest(HttpContext context)
        {
        // Get the list of CSS files from QueryString.
        string files = context.Request.QueryString["fileName"];
        if (string.IsNullOrEmpty(files))
            {
                return; // If no file name is mentioned then don't proceed.
            }


        // Get the list of files specified in the querystring (joined by ',').
        string[] arrFileNames = files.Split(',');
        if ((arrFileNames != null) && (arrFileNames.Length > 0))
            {
            string filePath = string.Empty;


            // Read the content of each CSS file and write it in the response.
            for (int fileCount = 0; fileCount < arrFileNames.Length; fileCount++)
                {
                filePath = context.Server.MapPath(arrFileNames[fileCount]).Trim();
                if (File.Exists(filePath)) // Check if the file exists in the physical location or not.
                    {
                        context.Response.Write(File.ReadAllText(filePath));
                    }
                }
            //context.Response.ContentType = "text/css"; // Set the content type.
            }
        }


    public bool IsReusable
    {
        get
        {
            return true;
        }
    }


}


Add the following code into your .cs file for CSS...



HtmlLink htmllnkcss = new HtmlLink();
        htmllnkcss.Href = "HttpCombiner.ashx?fileName=themes/css/abc.css,themes/css/xyz.css";
        htmllnkcss.Attributes.Add("rel", "stylesheet");
        htmllnkcss.Attributes.Add("type", "text/css");       
        hdflight.Controls.Add(htmllnkcss);


and for javascript...



HtmlGenericControl javascript = new HtmlGenericControl("script");
        javascript.Attributes["src"] = "HttpCombiner.ashx?fileName=js/jquery-1.3.2.min.js,js/blockui.js";
        javascript.Attributes["type"] = "text/javascript";
        hdflight.Controls.Add(javascript);


and now run your page and enjoy.....