Better Understanding

/*APPLY*/

/*Today I want to tell the one very good new feature of SQL 2005 -- APPLY (CROSS APPLY/OUTER APPLY)*/


/*We can use APPLY operator with table valued function. Before going to APPLY. I ll brief types of Functions*/

/*Two Main types of functions*/
--1.System Function
--2.User defined function

--1.System Functions

/*These Functions already defined in SQL server for various operations*/

/*We can split system functions to two types*/
--1.Scalar Functions
--2.Aggregate Functions

--1.Scalar Functions

/*It works only for single value/variable and returns single value*/
/*Ex:*/

SELECT ABS(-10) /*It will return absolute value*/

DECLARE @Name VARCHAR(10)
SET @Name = 'moov'
select UPPER(@Name) /*It will convert Lower case to Upper Case*/

/*Some more Scalar functions are : LTRIM(),RTRIM(),ROUND(),CONVERT(),....*/

--2.Aggregate Functions
/*It will accept multible rows of values and return one result*/

if object_id('tempdb..#temp') is not null
DROP table #temp
GO
create table #temp
(
sno int,
e_name varchar(100),
dept  varchar(100)
)
go
insert into #temp
select 1,'vicky','sw' union select 2,'moov','bill' union select 3,'anand','acc' union select 5,'AS','sw'

select max(sno) from #temp /*It will return maximum value from 3 rows*/

SELECT COUNT(sno) from #temp /*It will return count of table*/

/*Some more Aggregate functions are : MIN(),AVG(),....*/

--2.User defined function
/*This functions are defined by SQL developers*/

/*We can split User defined functions to two types*/

--1.Scalar Functions
--2.Table valued Functions

--1.Scalar Functions
/*Again it accepts single values return single value*/


IF OBJECT_ID('dbo.FN_ADD_NAME') IS NOT NULL
DROP FUNCTION dbo.FN_ADD_NAME
GO

CREATE FUNCTION FN_ADD_NAME
(
@Name1 VARCHAR(100), /*Inputs*/
@Name2 VARCHAR(100)
)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN (@Name1 + ' From ' + @Name2 + ' Team.')
END

/*We use the above function in select list of table*/
GO
SELECT sno,e_name,dept,dbo.FN_ADD_NAME(e_name,dept) as msg from #temp

/*Or we can use simple select list*/

SELECT dbo.FN_ADD_NAME('BS','Claims')

--2.Table valued Functions
/*It will return table values*/

IF OBJECT_ID('dbo.TEMP_SALARY') IS NOT NULL
DROP table dbo.TEMP_SALARY

go
CREATE TABLE TEMP_SALARY
(
SNO INT,
SALARY MONEY
)

INSERT INTO TEMP_SALARY
SELECT 1,'1000' UNION SELECT 2,'2000' UNION SELECT 3,'1500' UNION SELECT 4,'3000'


IF OBJECT_ID('dbo.FN_RETURN_SALARY') IS NOT NULL
DROP FUNCTION dbo.FN_RETURN_SALARY
go

CREATE FUNCTION FN_RETURN_SALARY
(
@sno INT, /*inputs*/
@AllValue INT
)
RETURNS @Salary TABLE
(
SNO INT,
SALARY MONEY
)
AS
BEGIN

INSERT INTO @Salary
SELECT * FROM TEMP_SALARY WHERE SNO =@sno OR 1=@AllValue /*Simple Trick to restrict output rows*/

RETURN
END

GO
/*We use the above function in simple select*/

SELECT * FROM FN_RETURN_SALARY(1,1) /*All values - 1  -- Return Sno 1*/

SELECT * FROM FN_RETURN_SALARY(4,2) /*Specific Value -- Return Sno 4*/

/*Now I want to get Salar with each name from these two tables using table function*/

SELECT * FROM #TEMP
SELECT * FROM TEMP_SALARY

/*You cant use inner join here. But you can use APPLY here- This is the new feature in SQL 2005*/

SELECT * FROM #TEMP
CROSS APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/


SELECT * FROM #TEMP
OUTER APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/

/*It will return NULL if function reurn nothing*/


/*So You can use APPLY operator with table valued function effectively. We can use this feature extensivly in our project*/

/*
Note:

/*BAS Team : You cant use this feature in our SQL 2005. Because this feature requires db compatability level should be 90. Our level is 80
So I created DB "KSCOPE" with db compatability 90 in dtdbtest server. You can use this feature in "KSCOPE" db. If you want to know about db compatability level
Please contact me.

/*If you want to see the error try to run this in dtdbtest..*/
*/

/*
DenRX Team : You can simply run this in your environment ;-)
*/

Thank You!
*/

trigger script

create table trig_parent(id int ,name varchar(20))
create table trig_child(id int ,name varchar(20))


create trigger trg_parent_id on trig_parent
for insert
as
begin

declare @id int,@name varchar(20)
select @id=id,@name=name from inserted
insert into trig_child values(@id,@name)


end

select  *from trig_parent
select * from trig_child

alter table trig_parent add sno int identity(1,1)

update trig_parent set name='vinoth'

insert into trig_parent values(4,'new')

create trigger trg_parent_upd on trig_parent
for update
as
begin

declare @id int,@name varchar(20)
select @id=id,@name=name from inserted
insert into trig_child values(@id,@name)



select @id=id,@name=name from deleted
insert into trig_child values(@id,@name)

end

select * from trig_parent where name='vinoths'

delete from trig_parent where sno in(select sno from (select ROW_NUMBER() over(partition by name order by name) sr_no,* from trig_parent)x
where sr_no<>1)

select sno from (select ROW_NUMBER() over(partition by name order by name) sr_no,* from trig_parent)x
where sr_no<>1

Filter Date of birth for same age and particular month

create table ##age (id int,dob datetime)
insert into ##age values(1,'1990-02-25 09:18:58.740')
insert into ##age values(2,'1990-07-01 09:18:58.740')
insert into ##age values(3,'1998-05-25 09:18:58.740')
insert into ##age values(4,'1990-08-25 09:18:58.740')

create table #temp (id int,dob datetime,age int,month int)

insert into #temp (id,dob,age,month)
select id,dob,DATEDIFF(yy, dob, GETDATE()) - CASE WHEN
(MONTH(dob) > MONTH(GETDATE())) OR (MONTH(dob) = MONTH(GETDATE()) AND DAY(dob) > DAY(GETDATE())) THEN 1 ELSE 0 END as age,month(dob) as [month] from ##age

select * from #temp
select * from #temp where age =26 and [month] in (1,2,3,4,5,6,7,8)

How to calculate accurate age from date of birth in sql

DECLARE @date datetime, @tmpdate datetime, @age int
SELECT @date = '02/25/91'
SELECT @tmpdate = @date
SELECT @age = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @age

Find Visitors Geographic Location-using-IP-Address-in-ASPNe

http://www.aspsnippets.com/Articles/Find-Visitors-Geographic-Location-using-IP-Address-in-ASPNet.aspx

Bind Images with DropDownList using ASP.net

Create a New Folder with Name msdropdown , click on Download and Paste the files with  jquery files to this folder.

Image Folder Name : Images

Sql Scripts :
create table ActorVotings (ID int identity(1,1) Primary Key,ActorName varchar(50),ActorImage varchar(50))

Webform1.aspx

%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="BindDropDownWithImages.WebForm1" %&gt;



<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Dropdownlist with Images</title>
<link href="msdropdown/dd.css" rel="stylesheet" type="text/css"></link>
<script src="msdropdown/js/jquery-1.6.1.min.js" type="text/javascript"></script>
<script src="msdropdown/js/jquery.dd.js" type="text/javascript"></script>
<!-- Script is used to call the JQuery for dropdown -->
<script language="javascript" type="text/javascript">
    $(document).ready(function (e) {
        try {
            $("#ddlCountry").msDropDown();
        } catch (e) {
            alert(e.message);
        }
    });
</script>
    <style type="text/css">
        .style1
        {
            height: 26px;
        }
    </style>
</head>
<body>
<form id="form1" runat="server">
<asp:panel id="Panelp" runat="server" style="height: 100%; width: 100%;">
<div id="div1" runat="server" style="height: 870px; width: 100%;">
<table>
<tr>
<td align="right" class="style1" colspan="2">
    <b>Select Your Favorite Actor: </b>&nbsp;</td>
<td class="style1" colspan="2">
<asp:dropdownlist autopostback="true" height="189px" id="ddlCountry" onselectedindexchanged="ddlCountry_SelectedIndexChanged" runat="server" width="147px"></asp:dropdownlist>
</td>
</tr>
<tr>
<td colspan="2">
    <strong>You have Selected</strong></td>
<td colspan="2">
<asp:label id="lbltext" runat="server"></asp:label>
</td>
</tr>
<tr>
<td colspan="2">
    <strong>Your IP Address is </strong>
</td>
<td colspan="2">
    <asp:label id="Label1" runat="server"></asp:label>
</td>
</tr>
<tr>
<td>
    &nbsp;</td>
<td>
    &nbsp;</td>
<td>
    &nbsp;</td>
<td>
    &nbsp;</td>
</tr>
<tr>
<td>
    &nbsp;</td>
<td>
    &nbsp;</td>
<td>
    &nbsp;</td>
<td>
    <asp:button height="26px" id="Button1" onclick="Button1_Click" runat="server" style="font-weight: 700;" text="Vote" width="97px">
</asp:button></td>
</tr>
</table>
<asp:panel height="112px" id="Panel1" runat="server">
    <asp:label font-bold="True" font-italic="True" font-size="XX-Large" forecolor="#993300" id="Label2" runat="server" text="Thanks For Casting Your Vote...."></asp:label>
</asp:panel>

    <br />

    </div>
</asp:panel>
</form>
</body>
</html>--%&gt;

Webform1.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace BindDropDownWithImages
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Panel1.Visible = false;
                BindDropDownList();
                BindTitles();
                lbltext.Text = ddlCountry.Items[0].Text;
            }
        }

        private void BindTitles()
        {
            if (ddlCountry != null)
            {
                foreach (ListItem li in ddlCountry.Items)
                {
                    li.Attributes["title"] = "Images/" + li.Value; // setting text value of item as tooltip
                }
            }
        }

        private void BindDropDownList()
        {
            //DataTable objdt = new DataTable();
            //objdt = GetDataForChart();
            //ddlCountry.DataSource = objdt;
            //ddlCountry.DataTextField = "Actor";
            //ddlCountry.DataValueField = "Id";
            //ddlCountry.DataBind();
            SqlConnection con = new SqlConnection("Data Source=GOKUL-PC\\GOKUL;Initial Catalog=Test;Integrated Security=true");
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from ActorVotings", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            ddlCountry.DataTextField = "ActorName";
            ddlCountry.DataValueField = "ActorImage";
            ddlCountry.DataSource = ds;
            ddlCountry.DataBind();
            con.Close();
        }
        protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
        {
            lbltext.Text = ddlCountry.SelectedItem.Text;
            BindTitles();
            if (lbltext.Text.Contains("rajini"))
            {
                Panelp.BackImageUrl = "~/Images/rajinifull.jpg";
           
            }
            else if(lbltext.Text.Contains("vijay"))
            {
                Panelp.BackImageUrl = "~/Images/vijayfull.jpg";
            }
            else if(lbltext.Text.Contains("kamal"))
            {
                Panelp.BackImageUrl = "~/Images/kamlfull.jpg";
            }
            else if(lbltext.Text.Contains("ajith"))
            {
                Panelp.BackImageUrl = "~/Images/ajithfull.png";
            }
            else if (lbltext.Text.Contains("vikram"))
            {
                Panelp.BackImageUrl = "~/Images/vikramfull.jpg";
            }
        }

        public DataTable GetDataForChart()
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("Actor", typeof(string));
            dt.Columns.Add("Id", typeof(string));

            dt.Columns.Add("LabelValue");
            var _objrow = dt.NewRow();
            _objrow["Actor"] = "rajini";
            _objrow["Id"] = "rajini.jpg";
         

            dt.Rows.Add(_objrow);
            _objrow = dt.NewRow();
            _objrow["Actor"] = "kamal";
            _objrow["Id"] = "kamal.jpg";
         
            dt.Rows.Add(_objrow);

            _objrow = dt.NewRow();
            _objrow["Actor"] = "vijay";
            _objrow["Id"] = "vijay.jpg";
         
            dt.Rows.Add(_objrow);

            _objrow = dt.NewRow();
            _objrow["Actor"] = "ajith";
            _objrow["Id"] = "ajith.jpg";
         
            dt.Rows.Add(_objrow);

            _objrow = dt.NewRow();
            _objrow["Actor"] = "vikram";
            _objrow["Id"] = "vikram.jpg";
            dt.Rows.Add(_objrow);
            return dt;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Panel1.Visible = true;
        }
    }
}

Simple-encrypting-and-decrypting-data-in-C#

http://www.codeproject.com/Articles/5719/Simple-encrypting-and-decrypting-data-in-C#