How to write sql query in asp.net code behind

Posted by sbatchaksa-27552 under ASP.NET on 7/16/2014 | Points: 10 | Views : 717 | Status : [Member] | Replies : 3
How to write this select query in asp.net code behind


DECLARE @calls_table TABLE
(
ID INT ,
Division CHAR(1) ,
[STATUS] VARCHAR(50)
)
INSERT INTO @calls_table
( ID, Division, STATUS )
VALUES ( 1001, 'A', 'Open' ),
( 1002, 'B', 'Closed' ),
( 1003, 'A', 'Closed' ),
( 1004, 'C', 'Open' ),
( 1005, 'C', 'Open' )


SQL Query:


SELECT Division ,
SUM(CASE WHEN STATUS = 'Open' THEN 1
ELSE 0
END) AS Open_Calls ,
SUM(CASE WHEN STATUS = 'Closed' THEN 1
ELSE 0
END) AS Closed_Calls ,
COUNT(*) AS 'Total_Calls' ,
CONVERT(DECIMAL(5, 2), SUM(CASE WHEN STATUS = 'Closed' THEN 1
ELSE 0
END)) / COUNT(*) AS Closed_Calls_Perc
FROM @calls_table
GROUP BY Division

Please kindly give me the answer as soon as possible.

Thanks
Sathik




Responses

Posted by: kgovindarao523-21772 on: 7/16/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,

Write above statements as a stored procedure in sql server.
if your Stored Proc Name is "SP_XYZ",
the code is like

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");

SqlCommand cmd = new SqlCommand();


cmd.CommandText = "SP_XYZ";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

SqlDataReader rows = cmd.ExecuteReader();//Instead you can fill it to a data table.

if(rows.HasRows)
{
while(rows.Read())
{
//your Logic Here
}
}

sqlConnection1.Close();


Thank you,
Govind

sbatchaksa-27552, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/16/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer
for calling SP in code, http://www.codeproject.com/Questions/224585/how-to-call-stored-procedure-in-asp-net-csharp-cod
For writing SQL query inside the asp code, http://www.codeproject.com/Questions/273440/sql-command-in-code-behind-Csharp-net

http://www.onlinebuff.com/article_step-by-step-select-insert-update-and-delete-using-aspnet-c-and-adonet_32.html

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

sbatchaksa-27552, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manimaddu on: 8/11/2014 [Member] Starter | Points: 25

Up
0
Down
Hi..
Learn full details about stored procedures before writing queries in code behind. Because stored procedures are very effective and useful for writing queries..

Thanks & Regards,
Mani Kumar

sbatchaksa-27552, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response