Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
C#
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;
}
Posted
Updated 6-Jan-15 3:58am
v2
Comments
ZurdoDev 6-Jan-15 10:09am    
I don't think you need to close the reader to get the output parameter value do you?

1 solution

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.[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900