Getting values from a database using a stored procedure?

Question

I have a stored procedure for getting last row value of a UserId:

ALTER PROCEDURE [dbo].[get_LoginStatusQ]  
    @UserId int
AS   
BEGIN  
   SELECT MAX(UserTimeId), LoginStatus 
   FROM UserTime 
   WHERE UserId = @UserId 
   GROUP BY UserId, LoginStatus, Day, HoursWorked, Date, CheckIn,UserTimeId 
END

In C#, I want to get the value of LoginStatus. How to do I do this?

I tried:

public void GetLogin(object sender, EventArgs e)
{ 
   db.get_LoginStatusQ(Convert.ToInt32(Session["userid"])));

   if( LoginStatus ='In') // How do I get the field LoginStatus from the database?
   {
   }
}

You should try writing some code to actually connect to and query your database. It doesn't look like you have any code that attempts to do that. — Justin Helgerson, May 05 '14 at 21:38
you are right. I am novice and do not know how to implement(code) this. How to get LoginStatus in If clause? — user3590485, May 05 '14 at 21:39
A quick search yields quite a few results: http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program, http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp, https://www.google.com/search?q=c%23+execute+stored+procedure&oq=c%23+execute+sto&aqs=chrome.1.69i57j0j69i58j0l3.2605j0j7&sourceid=chrome&es_sm=93&ie=UTF-8 — Justin Helgerson, May 05 '14 at 21:40
There are plenty of tutorials on the net that teach you basic coding. StackOverflow isn't meant for that. — Gigi, May 05 '14 at 21:40
have you looked up how to create and or use Parameterized query's along with Sql.DataClient you need to do a little more coding to get the results you're seeking — MethodMan, May 05 '14 at 22:08

score 0 · Answer 1 · answered May 05 '14 at 22:06

Your stored procedure needs some fixing see below:

ALTER PROCEDURE [dbo].[get_LoginStatusQ]  
@UserId int
AS   
BEGIN  
 SET NOCOUNT ON;

    SELECT TOP 1 UserTimeId, LoginStatus 
    FROM UserTime 
    WHERE UserId = @UserId 
    ORDER BY UserTimeId DESC
END

score 0 · Accepted Answer · answered May 05 '14 at 22:18

for your C# code try something like this make sure that in your using you have the following you can change the declaration of the Method signature to Private if you do not have a DAL Class set up of your own.

using System.Data;
using System.Data.SqlClient;

    public static DataSet GetLogin(Int32 userId)
    {
       DataSet logStatusDs = new DataSet();
        //load the List one time to be used thru out the intire application
        var ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
        using (SqlConnection connStr = new SqlConnection(ConnString))
        {
            using (SqlCommand cmd = new SqlCommand("get_LoginStatusQ", connStr))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserId", userId);
                cmd.Connection.Open();
                new SqlDataAdapter(cmd).Fill(logStatusDs);
            }
        }
        return logStatusDs;
    }

Thanks. How get LoginStatus from logStatusDs ? Ex : if(logStatusDs='In') — user3590485, May 05 '14 at 22:58
if you need to change your query to something like `M Ali` had stated and then in your return DataSet `logStatusDs` you would have that field so something like `var logStatus = logStatusDs.Tables[0]["LoginStatus"].ToString();` — MethodMan, May 06 '14 at 13:32

Getting values from a database using a stored procedure?

2 Answers2