如何从存储过程中获取某些选定值的值

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

我刚开始使用ASP.Net，我只知道PHP，这意味着我有点顽固。我需要真实的例子。

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.

继承了我的问题，我在一个名为Class1的类库中得到了这个类，它连接到数据库并要求验证用户登录。

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.

我需要一套例子。

2 个解决方案

#1

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.

在连接和命令对象周围使用子句。这将为您以后节省很多痛苦。请参阅：为示例调用带有返回值的存储过程。和SqlConnection SqlCommand SqlDataReader IDisposable的原因有关。提示：如果现在的代码已经发布到生产中，很可能你会开始看到随机数据库相关的异常开始在各个地方弹出。所以这非常重要。
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.

SqlCommand中的proc名称（“checkid”）与存储过程的实际名称（“logincheck”）不匹配。改变其中一个。你现在拥有的将导致执行时出现sql错误。
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.

考虑更改变量类型的名称。 Type是System命名空间中的一个类，读取方式有点令人困惑。也许是accountType，loginType，userType或类似的东西。你当然可以把它当作类型;只有跟随你的人会质疑它。
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 *?

更改select语句以实际命名要返回的列。因为它很脆弱。请参阅：不使用select *的原因是什么？
I used an if statement instead of a while because you really only want the first row.

我使用了if语句而不是一段时间，因为你真的只想要第一行。

#2

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

假设“UserType”是您要查找的列（无法判断，因为您正在使用Select *）该行将是

type = dr["UserType"] as string

#1