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.....


Microsoft Ajax tool kit Problem Solved.

I have solved the problem associated with Microsoft Ajax tool kit script manager property "CombineScript = true".


Add the following code into web.config file.
<httpHandlers>
<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
</httpHandlers>


and make a handler named CombineScriptsHandler.ashx


add the toolkit script manager like..


<cc1:ToolkitScriptManager ID="toolkitScriptMgr" EnablePartialRendering="false" CombineScripts="true" CombineScriptsHandlerUrl="~/CombineScriptsHandler.ashx" ScriptMode="Release" runat="server"  LoadScriptsBeforeUI="false">


and enjoy...

Wednesday, May 25, 2011

Microsoft Ajax tool kit Problem

I have used Microsoft Ajax tool kit controls like auto extender, extender calender and flyout in my project.


For using ajax functionality i have used ASP.NET Script Manager but while using Script manager .NET Framework generate script resource file of size 255 kb on client side for ajax functionality.


I have solved this problem by using toolkitScriptManager that inherit ASP.NET Script Manager and its reduced around 300 kb of script resource file but while i m using property CombineScripts = "true" .NET Framework combine script into one script resource file and compress it also but a java script error occured that "AjaxControlToolkit" is undefined in IE7.


any one have any better solution to minimize Script Resource file.

Wednesday, May 18, 2011

Trigger is not working with bulk update??

I have written a trigger like below...
-------------------------------------------------------------------------------------------------

IF UPDATE (GROUPCODE)
BEGIN
DECLARE @GROUPCODE_OLD VARCHAR(100),
@GROUPCODE_NEW VARCHAR(100)


SELECT @GROUPCODE_OLD = GROUPCODE FROM DELETED
SELECT @GROUPCODE_NEW = GROUPCODE FROM INSERTED
IF @GROUPCODE_OLD = @GROUPCODE_NEW
BEGIN
RETURN
END
ELSE
BEGIN


  SELECT  @GRADECODE=GRADECODE,       
  @COMPANYID = COMPANYID,      
  @COMPANYTYPECODE=COMPANYTYPECODE ,       
  @USER_CODE = LAST_UPD_BY,        
  @MASTERCODE = MASTERCODE
  FROM  INSERTED


  SELECT  @MASTER_COMPANYTYPECODE = COMPANYTYPECODE      
  FROM  TBL_COMPANY_MAS (NOLOCK)       
  WHERE  COMPANYID =@MASTERCODE 


  UPDATE TBL_COMMISSION_MAS SET ACTIVE=0 WHERE  COMPANYID=@COMPANYID

  SELECT  SERVICECODE AS SERVICE_CODE ,TMPL_ID    
  INTO  #TMP_DATA_GROUP  
  FROM  TBL_COMMISSION_TMPL_MAS WHERE 1=2  
        
  INSERT INTO #TMP_DATA_GROUP       
  SELECT  DISTINCT SERVICECODE as SERVICE_CODE ,TMPL_ID       
  FROM  TBL_COMMISSION_TMPL_MAS A (NOLOCK)      
  WHERE  CREATED_BY_COMPANY =@MASTERCODE      
  AND  CONVERT(NVARCHAR(10),FROM_DATE,111) <= 
(
SELECT MAX(CONVERT(NVARCHAR(10),FROM_DATE,111))       
FROM  TBL_COMMISSION_TMPL_MAS (NOLOCK)      
WHERE  CREATED_BY_COMPANY = @MASTERCODE      
AND SERVICECODE = A.SERVICECODE      
and CONVERT(NVARCHAR(10),FROM_DATE,111)<=CONVERT(NVARCHAR(10),GETDATE(),111)      
and GRADE_CODE =@GRADECODE
AND GROUPCODE = @GROUPCODE_NEW       
)        
  AND GRADE_CODE=@GRADECODE
  AND GROUPCODE = @GROUPCODE_NEW    
  AND ACTIVE =1          
     
  UNION      
     
  SELECT DISTINCT SERVICECODE as SERVICE_CODE ,TMPL_ID       
  FROM TBL_COMMISSION_TMPL_MAS A (NOLOCK)      
  WHERE  CREATED_BY_COMPANY =@MASTERCODE      
  AND CONVERT(NVARCHAR(10),FROM_DATE,111) > CONVERT(NVARCHAR(10),GETDATE(),111)               
  AND GRADE_CODE=@GRADECODE
  AND GROUPCODE = @GROUPCODE_NEW   
  AND ACTIVE =1    
          
--   DECLARE @TMPL_ID   VARCHAR(50)      
--   DECLARE @SERVICE_CODE VARCHAR(3)      
--   DECLARE @MSG_OUT int      
       
  WHILE EXISTS (SELECT TOP 1 '1' FROM  #TMP_DATA_GROUP)      
BEGIN      
SELECT @TMPL_ID = TMPL_ID FROM #TMP_DATA_GROUP ORDER BY TMPL_ID      
    
EXEC USP_SET_MARKUP_COMMISSION_WL @MASTERCODE, @MASTER_COMPANYTYPECODE,@TMPL_ID, @USER_CODE ,@MSG_OUT out,@COMPANYID,@GROUPCODE_NEW          
IF @@ERROR <> 0      
BEGIN      
ROLLBACK TRAN      
RETURN      
END      
    
DELETE  FROM #TMP_DATA_GROUP WHERE TMPL_ID= @TMPL_ID       
END 
END
END
-------------------------------------------------------------------------------------------------
but when i update this column either by query or by front end on by one it works fine, but when i write update query in bulk then its not working.

Any suggestion???