how to get the value of certain selected value out from Stored Procedure

Question

i just got started with ASP.Net, i only knew PHP so which means im a little stubborn. i need real examples.

heres my problem, i got this class in a class library called Class1, it connects to the database and ask for verification of user login.

public string userlogin(string loginuser,string loginpass)
    {
        string type = null;
        myCon.Open();
        SqlCommand logincheck = new SqlCommand("CheckID", myCon);
        logincheck.CommandType = CommandType.StoredProcedure;
        logincheck.Parameters.Add("@userid", SqlDbType.NVarChar).Value = loginuser;
        logincheck.Parameters.Add("@password", SqlDbType.NVarChar).Value = loginpass;
        SqlDataReader dr;
        dr = logincheck.ExecuteReader();
        while (dr.Read())
        {
            type = //here i want to get the value of type in my database
            break;

        }
        myCon.Close();
        return type;
    }

here's my stored procedure

ALTER PROCEDURE dbo.logincheck
@userid nchar(10),
@password nchar(20)
AS
Select * from users Where userid = @userid and password = @password
RETURN

i need a set of examples please.

score 1 · Accepted Answer · edited May 23 '17 at 10:25

Without knowing how your users table is structured, the following is a guess:

while (dr.Read()) {
...  
}

should be changed to:

if (dr.Read()) {
  type = dr["type"].ToString();
}

A couple of recommendations.

Use using clauses around your connection and command objects. This will save you a lot of pain later. See: Calling stored procedure with return value for an example. And SqlConnection SqlCommand SqlDataReader IDisposable for the reasons why. Hint: if the code as you have it now was released into production it is highly likely you will begin to see random database related exceptions start popping up in various places. So this is pretty important.
The name of the proc in your SqlCommand ("checkid") doesn't match the actual name of your stored procedure ("logincheck"). Change one of them. What you have right now will result in a sql error when executed.
Consider changing the name of the variable type. Type is a class in the System namespace and the way that reads is a bit confusing. Maybe accountType, loginType, userType or something similar. You can certainly leave it as type; just people following you will question it.
Change your select statement to actually name the columns you want back. As it stands it's brittle. See: What is the reason not to use select *?
I used an if statement instead of a while because you really only want the first row.

score 0 · Answer 2 · answered Sep 05 '13 at 17:00

0

Assuming "UserType" is the column you are looking for (can't tell because you are using a Select *) that line would be

type = dr["UserType"] as string

answered Sep 05 '13 at 17:00

Otávio Décio

73,752
17
161
228

how to get the value of certain selected value out from Stored Procedure

2 Answers2