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

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

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