Selecting columns from a stored procedure

Benjamin Allen

10y1.2k.NET

Hi everyone

I have the below stored procedure and I want to select all columns to display them on the web form using "where clause" by specifying the "regno" provided by user in a textbook named txtboxsearch or "date from and to" by user selecting time on the displayed calender. The problem am having is how to select the column names like "totalhourabsent" and "totalhourpresent" that aren't in the database table but rather in the stored procedure.

Below is my stored procedure:

CREATE PROCEDURE [dbo].[GetAttendanceByHours]

AS

BEGIN

Select *,
ISNULL(NO_HRS_PRESENT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) PRSNT_PERC,
ISNULL(NO_HRS_ABSNT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) ABSNT_PERC
from
(
select
REGNO,
FIRSTNAME,
LASTNAME,
MAX(case when status = 'P' THEN CNT end) NO_HRS_PRESENT,
MAX(case WHEN STATUS = 'A' THEN CNT END) NO_HRS_ABSNT
from
(
select
REGNO,FIRSTNAME, LASTNAME,status,
count(status) CNT from AttendanceTable
group by regno,firstname, lastname, status
) A
GROUP BY regno,firstname, lastname
) tmp

END

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vincent Maverick Durano10y ago
Accepted answer
First you need to modify your stored procedure to accept parameters such as student no or date then you can pass the values to it like this:

protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
using(SqlConnection connection = new SqlConnection("Your Connection String")){
connection.Open();
string spName = "YOURStoredProcudureName";
using(SqlCommand sqlCmd = new SqlCommand(spName, connection)){
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@StudentNo",TextBox1.Text.Trim());
sqlCmd.Parameters.AddWithValue("@SelectedDate",Calendar1.SelectedDate);
using(SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd)){
sqlDa.Fill(dt);
if (dt.Rows.Count > 0) {
//Get the data from DataTable
string hoursPresent = dt.Rows[0]["NO_HRS_PRESENT"].ToString();
string hoursAbsent= dt.Rows[0]["NO_HRS_ABSNT"].ToString();
}
}
}
}
}

NOTE: @StudentNo and @DateSelected should be the exact same name of your parameter from your SP.
Benjamin Allen10y ago
This is really helpful Mr. Vincent but I want to include a "where clause" in the select query so that a user can enter either student regno in a textbox or date(from and to) which also appeared in a textbox when user select calendar date on the web form, so that only record for that specified student or date is displayed on a gridview.
Thanks
Vincent Maverick Durano10y ago
You could easily get your data from your SP using the the traditional ADO.NET way. For example:

protected void Button1_Click(object sender, EventArgs e)
{
         DataTable dt = new DataTable();
         using(SqlConnection connection = new SqlConnection("Your Connection String")){
                  connection.Open();
                  string spName = "YOURStoredProcudureName";
                  using(SqlCommand sqlCmd = new SqlCommand(spName, connection)){
                                sqlCmd.CommandType = CommandType.StoredProcedure;
                                    using(SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd)){
                                    sqlDa.Fill(dt);
                                    if (dt.Rows.Count > 0) {
                                             //Get the data from DataTable
                                             string hoursPresent = dt.Rows[0]["NO_HRS_PRESENT"].ToString();
                                             string hoursAbsent= dt.Rows[0]["NO_HRS_ABSNT"].ToString();
                                    }
                                    }
                       }
            }
}
Manas Mohapatra10y ago
It is there in your Stored Procedure.

Select *,
ISNULL(NO_HRS_PRESENT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) PRSNT_PERC,
ISNULL(NO_HRS_ABSNT,0)*100/ ((ISNULL(NO_HRS_PRESENT,0))+(ISNULL(NO_HRS_ABSNT,0))) ABSNT_PERC

Here * represents REGNO, FIRSTNAME, LASTNAME, NO_HRS_PRESENT, NO_HRS_ABSNT columns. And also you have added two extra columns PRSNT_PERC and ABSNT_PERC.

You can fetch in code like:
SqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)
{
    while (reader.Read())
    {
        Console.WriteLine(reader["NO_HRS_PRESENT]));
    }
}
Benjamin Allen10y ago
Manas the values of the columns comes from a column named status("P" for present and "A" for absent) in my database table. The COUNT of "P" is where I got the totalpresent column and from there I calculated the percentage as seen in the stored procedure.
Thanks
Benjamin Allen10y ago
Thanks Amit but sorry I don't understand it very well. You mean I should create a new stored procedure that the one I had already created? It will be better I you can give me one short example.
Thanks once more
Manas Mohapatra10y ago
What will be the value of two temporary columns "totalhourabsent" and "totalhourpresent". Means where you will get the value of two above columns.
Amit Kumar Singh10y ago
1.Create Procedure
(
Variable Declaration
@Opt varchar(2)
)
AS
BEGIN
if(@Opt='S')
BEGIN
// Your Code Goes Here
END
END

2.
1.First Create one Class
In Class Create using dataset crate one method
2.Pass all parameters including @opt For Example
public static dataset Dbconnect(string FirstName,,,,,,,,,string Opt)
{
Connection goes here
}

3.On Button Click
Dataset ds=null;
ds=classname.MethodName('','','','S')
Note: if string value is there just put :- ''
if integer values is there just put :- 0
Benjamin Allen10y ago
Thanks Vincent for the reply. Selecting them from the stored procedure isn't what is giving me problem but select those required columns from the already created stored procedure under a "button" event is what is giving problem as the columns aren't on the database table.
Or let me put is this way, I want to execute the stored procedure from my code behind under a button event
Vincent Maverick Durano10y ago
You have to include those columns in your select query so you could use them in your code.