get Record from DB using stored procedure in c#

Question

I'm trying to fetch a record from the database using a stored procedure, but it returns null in SqlDataReader object.

Here is my code:

public Buybest_Liberary.Data.UserManagement getUser(string email)
{
    Buybest_Liberary.Data.UserManagement obj = new Buybest_Liberary.Data.UserManagement();
    string conString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\ahmadshair\Documents\Buybest.mdf;Integrated Security=True;Connect Timeout=30";

    SqlConnection connection = new SqlConnection(conString);
    connection.Open();

    SqlCommand _cmd = new SqlCommand("getUserRecord", connection);
    _cmd.CommandType = CommandType.StoredProcedure;
    _cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;

    SqlDataReader dr = _cmd.ExecuteReader();

    if (dr.HasRows)
    {
        obj.UId = Convert.ToInt32(dr[0]);
        obj.Email = dr[1].ToString();
        obj.Password = dr[2].ToString();
    }

    return obj;
}

What is the definition of getUserRecord? Are you really sure it returns some data? — Soner Gönül
– Soner Gönül, Commented Nov 27, 2015 at 11:45

marc_s · Accepted Answer · 2015-11-27 12:33:35Z

5

You need to call dr.Read() before accessing the data! Also: put your disposable objects (SqlConnection, SqlCommand, SqlDataReader) into using(..) { ... } blocks to ensure proper disposal:

public Buybest_Liberary.Data.UserManagement getUser(string email)
{
    Buybest_Liberary.Data.UserManagement obj = new Buybest_Liberary.Data.UserManagement();

    // You should read the connection string from a config file 
    // don't specify it explicitly in code!
    string conString = ConfigurationManager.ConnectionStrings["-your-connection-string-name-here-"].ConnectionString;

    using (SqlConnection connection = new SqlConnection(conString))
    using (SqlCommand _cmd = new SqlCommand("getUserRecord", connection))
    {
        _cmd.CommandType = CommandType.StoredProcedure;
        _cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;

        connection.Open();

        using (SqlDataReader dr = _cmd.ExecuteReader())
        { 
            // the SqlDataReader could return *multiple* rows - how are you
            // going to deal with that? Create an object for each row of data
            // and add them to a list to return? 
            while (dr.Read())
            {
                 obj.UId = Convert.ToInt32(dr[0]);
                 obj.Email = dr[1].ToString();
                 obj.Password = dr[2].ToString();
            }

            dr.Close();
        }

        connection.Close();
    }

    return obj;
}

Also: what do you do if your stored procedure returns multiple rows of data? You need to somehow deal with that, too

edited Nov 27, 2015 at 12:33

answered Nov 27, 2015 at 11:51

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jesús López Over a year ago

Also, don't hard code the connection string. read it from config file. No need to call dr.Close() nor connection.Close() because dr and connection are enclosed in using blocks

marc_s Over a year ago

@JesúsLópez: agreed - the connection string was copied from the question - not my doing. And yes - there's no strict need to call dr.Close() or connection.Close() - but doing so doesn't hurt, either - I prefer to be explicit.

Jesús López Over a year ago

that's fine. However I think it's better to teach best practices to newcomers rather than simply answer directly the question.

Ahmed Shair Over a year ago

Thanks, It is helpful.

Sascha · Accepted Answer · 2015-11-27 11:50:33Z

4

If I am not mistaken you are missing a dr.Read() to fetch the first record.

answered Nov 27, 2015 at 11:50

Sascha

1,2181 gold badge17 silver badges34 bronze badges

Comments

CMadhu · Accepted Answer · 2015-11-27 11:52:58Z

2

HasRows is simply telling you whether or not there are any rows to read, whereas Read() advances the internal cursor to the next row in the data, and incidentally returns False if there are no more rows to read, or True if there was another row available. We need dr.read()

answered Nov 27, 2015 at 11:52

CMadhu

3461 silver badge8 bronze badges

Collectives™ on Stack Overflow

get Record from DB using stored procedure in c#

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related