sql case statement

select * from tbl_vote
truncate table tbl_vote

select answer, count(*) * 100/(select count(*) from tbl_vote) as Percentage,
Votes=  
case answer
when 1 then 'Dog'
when 2 then 'Cat'
when 3 then 'Bird'
when 4 then 'Man'
end
from tbl_vote
group by answer

create table tbl_vote
(
vote_id int not null primary key identity,
answer int,
u_id int,
createdOn datetime
)

Set identity column - existing table

Create TABLE DataTable(id int, name varchar(20) )
Insert into DataTable(id, name)
Values(1, 'test 0'),
(4, 'test 1'),
(5, 'test 2'),
(6, 'test 3')
GO

--Create a temp table with an identity column
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_DataTable
(
id int NOT NULL IDENTITY (1, 1),
name varchar(20) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable ON
GO
IF EXISTS (SELECT 1 FROM dbo.DataTable)
INSERT INTO dbo.Tmp_DataTable (id, name)
SELECT id, name FROM dbo.DataTable WITH (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable OFF
GO
DROP TABLE dbo.DataTable
GO
EXECUTE sp_rename N'dbo.Tmp_DataTable', N'DataTable', 'OBJECT'
GO
COMMIT

select * from DataTable

insert into datatable(name)values('arun')

Export tables to excel sheet

create columns name in the excel sheet n save it.

then execute this


--Enables component 'Ad Hoc Distributed Queries' by using sp_configure
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO


INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=e:\Book1.xls;','Select * from [sheet1$]')
SELECT * FROM tbl_images

SQL Table to Raw XML

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="wordLib.aspx.cs" ContentType="text/xml" Inherits="wordLib" %>

<%
string query = "select * from tbl_sender";

System.Data.DataSet ds = new System.Data.DataSet();
ds = connect.GetDataFromDB(query);

if (ds.Tables.Count > 0)
{
foreach (System.Data.DataRow row in ds.Tables[0].Rows)
{

string conversion_tag = " ";
%>" desc="<% Response.Write(Server.UrlEncode(row["senderDescription"].ToString())); %>"><%
}
}

%>

Populate data controls

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnecS"].ConnectionString);
SqlCommand cmd = new SqlCommand("select * from fast_collection where pid=@pid", con);
cmd.Parameters.AddWithValue("@pid", productID);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
con.Open();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ddlcollection.DataSource = ds.Tables[0];
ddlcollection.DataTextField = "collection";
ddlcollection.DataValueField = "cid";
ddlcollection.DataBind();
ddlcollection.Items.Insert(0, "--Select--");
}

Adding Constraints

Add foreign key to existing table

alter table Empdetails add constraint fk_cat foreign key (cat_id) references tbl_category(cat_id)

Add constraint in create table

create table table2
(
cat_id int not null,
category varchar(100),
empid int,
primary key(cat_id),
constraint fk_hi foreign key (empid) references empdetails(empid)
)

Access html controls page from codebehind (c#)

1. Declare your variables as public

public string amount, paymentid, paymentname, paymentaddress, paymentcity, paymentstate, paymentzip, paymentmobile, paymentemail;

protected void Page_Load(object sender, EventArgs e)
{
if (Session["PaymentID"] != null)
{
amount = Session["gvtotal"].ToString();
paymentid = Session["direct_pay_id"].ToString();
paymentname = Session["paymentname"].ToString();
paymentaddress = Session["paymentaddress"].ToString();
paymentcity = Session["paymentcity"].ToString();
paymentstate = Session["paymentstate"].ToString();
paymentzip = Session["paymentzip"].ToString();
paymentmobile = Session["paymentmobile"].ToString();
paymentemail = Session["paymentemail"].ToString();
}
else
{
Response.Redirect("default.aspx");
}
}

2. In the aspx page,

value="<%=paymentname %>