c# populate textboxes using sqldatareader method

Question

I created the below method which i tested and does return the correct data. Where I am confused is what is the proper way to populate individual textboxes on a form with the results from this method?

Rather than using an objectdatasource and then binding a gridview to the objectdatasource that works but I need more freedom to customize the form.

public MemberDetails GetMemberDetail(int membershipgen)
   {
       SqlConnection con = new SqlConnection(connectionString);
       SqlCommand cmd = new SqlCommand("usp_getmemberdetail", con);
       cmd.CommandType = CommandType.StoredProcedure;

       cmd.Parameters.Add(new SqlParameter("@MEMBERSHIPGEN", SqlDbType.Int, 5));
       cmd.Parameters["@MEMBERSHIPGEN"].Value = membershipgen;

       try
       {
           con.Open();
           SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

           reader.Read();
           MemberDetails mem = new MemberDetails((int)reader["MEMBERSHIPGEN"], (string)reader["MEMBERSHIPID"], (string)reader["LASTNAME"],
                    (string)reader["FIRSTNAME"], (string)reader["SUFFIX"], (string)reader["MEMBERTYPESCODE"]);
           reader.Close();
           return mem;
       }
        catch (SqlException err)
       {
            throw new ApplicationException("Data error.");
        }
        finally
       {
           con.Close();
       }

Darin Dimitrov · Accepted Answer · 2012-01-06 15:37:20Z

2

Something along the lines of:

var memberDetails = GetMemberDetail(12345);
textBox1.Text = memberDetails.Prop1;
textBox2.Text = memberDetails.Prop2;
...

Also I would refactor this method and make sure that I properly dispose disposable resources by wrapping them in using statements to avoid leaking unmanaged handles:

public MemberDetails GetMemberDetail(int membershipgen)
{
    using (SqlConnection con = new SqlConnection(connectionString))
    using (SqlCommand cmd = con.CreateCommand())
    {
        con.Open();
        cmd.CommandText = "usp_getmemberdetail";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MEMBERSHIPGEN", membershipgen);
        using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
        {
            if (reader.Read())
            {
                return new MemberDetails(
                    reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN")), 
                    reader.GetString(reader.GetOrdinal("MEMBERSHIPID")),
                    reader.GetString(reader.GetOrdinal("LASTNAME")),
                    reader.GetString(reader.GetOrdinal("FIRSTNAME")),
                    reader.GetString(reader.GetOrdinal("SUFFIX")),
                    reader.GetString(reader.GetOrdinal("MEMBERTYPESCODE"))
                );
            }
            return null;
        }
    }
}

edited Jan 6, 2012 at 15:37

answered Jan 6, 2012 at 15:30

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

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

5 Comments

Tim Over a year ago

GetMemberDetail shows that it does not exist in the current context. It is located within a .DLL named CLMembers is that why it shows it does not exist. So I tried var memberDetails = CLMembers.GetMemberDetail but the getmemberdetail part does not show up still

Darin Dimitrov Over a year ago

@JawaidAkhtar, GetMemberDetail is a non static method. This means that in order to invoke it you need to have an instance of the class that contains it. So if you are saying that this method is defined in a class called CLMembers you could do this: var memberDetails = new CLMembers().GetMemberDetail(12345);. Another possibility is to make it static. This way you will be able to invoke it without an instance: var memberDetails = CLMembers.GetMemberDetail(12345);

Tim Over a year ago

@Darin Thanks for all your help I been taking classes and trying to learn c# but seems like no matter how much I read I just can not get a grasp of it. Thanks again and even though I do not understand your answer completely I do know that I need to hit the books even more :)

MethodMan Over a year ago

Darin I would wrap that awesome code sample around a Try Catch {}

Darin Dimitrov Over a year ago

@DJKRAZE, why? What would you do in the catch block? Rethrow?

Kinexus · Accepted Answer · 2012-01-06 15:29:42Z

1

Get the MemberDetails;

var memberDetails = GetMemberDetail(1);

Populate the textbox;

TextBox.Text = memberDetails.Property;

answered Jan 6, 2012 at 15:29

Kinexus

12.9k6 gold badges43 silver badges63 bronze badges

2 Comments

Tim Over a year ago

GetMemberDetail shows that it does not exist in the current context. It is located within a .DLL named CLMembers is that why it shows it does not exist. So I tried var memberDetails = CLMembers.GetMemberDetail but the getmemberdetail part does not show up still

Adam Rackis Over a year ago

@JawaidAkhtar - then you'll have to add a reference to that dll. This answer (and Darin's) answer the question you asked.

MethodMan · Accepted Answer · 2012-01-06 15:44:49Z

0

Jawaid outside of the correct answers that were provided below I would also set SqlConnection con = null; and

SqlCommand cmd = null; outside the try and inside the try put the following 
con = new SqlConnection(connectionString);

this way if there is an Error when doing cmd.Parameters.Add -- you can trap that exception also dispose of the reader object

if (reader != null)
{
  ((IDisposable)reader).Dispose(); 
  // somthing like that .. do the same for con and cmd objects or wrap them in a using() {}
}

cmd = new SqlCommand("usp_getmemberdetail", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@MEMBERSHIPGEN", SqlDbType.Int, 5));
cmd.Parameters["@MEMBERSHIPGEN"].Value = membershipgen;

answered Jan 6, 2012 at 15:44

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

2 Comments

Tim Over a year ago

Thanks for your advice I am reading a c# book and it did not have that in there it showed the way I did it. I appreciate your help I will change it and Thanks for your explanation really appreciate it

MethodMan Over a year ago

Your Welcome I give you props for being able to at least be resourceful enough to attempt this on your own vs many others whom just want a quick answer without understanding why we post what we do in regards to suggestions advise and answers.. you will become a great programmer especially if you are reading the books to gain further knowledge good luck in your coding ventures.

Collectives™ on Stack Overflow

c# populate textboxes using sqldatareader method

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related