一旦被绑定，如何为同一个查询再次打开我的DataReader对象 [英] How to open my DataReader object again for the same query once it is clased

问题描述

我的存储过程中有一个out参数，我想首先读取out参数的值，然后根据我要再次打开读取器的out参数值并从数据库中读取值。我试过以下way.Is有任何其他方式，所以我不需要再次创建读者对象并读取值。

I have a out parameter in my stored procedure,I want to read the value of the out parameter first ,then basing the outparameter value I want to open the reader again and read the value from the data base.I have tried following way.Is there any other way so that i need not to create the reader object again and read the value.

public Employee EmployeeLogIn(string userName, string password)
{
    SqlCommand command = new SqlCommand();
    Employee employee = new Employee();
    int result = 0;
    try
    {
        command.Connection = ConnectionManager.GetConnection();
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "sp_GetLogInEmployee";

        command.Parameters.Add("@UserName", SqlDbType.VarChar, 50);
        command.Parameters["@UserName"].Value = userName;
        command.Parameters["@UserName"].Direction = ParameterDirection.Input;

        command.Parameters.Add("@EncryptedPassword", SqlDbType.VarChar, 255);
        command.Parameters["@EncryptedPassword"].Value = password;
        command.Parameters["@EncryptedPassword"].Direction = ParameterDirection.Input;

        command.Parameters.Add("@result", SqlDbType.Int);
        command.Parameters["@result"].Value = result;
        command.Parameters["@result"].Direction = ParameterDirection.Output;

        SqlDataReader reader = command.ExecuteReader();

        if (reader != null && reader.HasRows == true)
        {
            //while (reader.Read())
            //{
            //    employee.ID = reader["ID"].ToString();
            //    employee.UserName = reader["UserName"].ToString();

            //}
            reader.Close();
            result = Convert.ToInt32(command.Parameters["@result"].Value);
        }
        if (result == 1)
        {

            reader = command.ExecuteReader();
            if (reader != null && reader.HasRows == true)
            {
                while (reader.Read())
                {
                    var dateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
                    var dob = dateOfBirth.ToShortDateString();
                    var dateofJoining = DateTime.Parse(reader["DateOfJoining"].ToString());
                    var doj = dateofJoining.ToShortDateString();
                    var created = DateTime.Parse(reader["Created"].ToString());
                    var cDate = created.ToShortDateString();
                    employee.ID = reader["ID"].ToString();
                    employee.FirstName = reader["FirstName"].ToString();
                    employee.MiddleName = reader["MiddleName"].ToString();
                    employee.LastName = reader["LastName"].ToString();
                    employee.DateOfBirth = dob;
                    employee.EmailID = reader["EmailID"].ToString();
                    employee.UserName = reader["UserName"].ToString();
                    //employee.EncryptedPassword = reader["EncryptedPassword"].ToString();
                    employee.PasswordSalt = reader["PasswordSalt"].ToString();
                    employee.DateOfJoining = doj;
                    employee.Designation = reader["Designation"].ToString();
                    employee.Code = reader["Code"].ToString();
                    employee.UserRole = reader["UserRole"].ToString();
                    employee.IsActive = int.Parse(reader["IsActive"].ToString());
                    employee.Created = cDate;
                    employee.LastUpdated = DateTime.Parse(reader["LastUpdated"].ToString());
                }
            }
        }
        else
        {
            employee = null;
        }

    }
    catch (Exception ex)
    {

        throw ex;
    }
    finally
    {
        if (command.Connection.State != ConnectionState.Closed ||
            command.Connection.State != ConnectionState.Broken)
        {
            command.Connection.Close();
        }
    }
    return employee;
}

推荐答案

关闭后无法再次打开DataReader：您需要创建一个新的连接并且重新运行请求。



但是我不太清楚你为什么要这样做 - 用C＃代码而不是数据库进行密码验证 - 只需返回加密密码并将其与您计算的加密值进行比较。



或者更好的是，不加密密码 - 反而哈希：密码存储：怎么做。 [ ^ ]

You can't open the DataReader again once it is closed: you need to create a new connection and re-run the request.

But I'm not quite sure why you want to - do your password validation in the C# code rather than the DB - just return the encrypted password and compare that to the encrypted value you calculated.

Or better still, not encrypt passwords - hash them instead: Password Storage: How to do it.[^]

这篇关于一旦被绑定，如何为同一个查询再次打开我的DataReader对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！