Reading int values from SqlDataReader

Question

hi can anyone please help me with this fetching from database of int values im having difficulty in fetching int values , it works for varchar but not int can someone help me out please

if (int.TryParse(TxtFarmerCode.Text, out intValue))
{
   using (SqlConnection sqlConn = new SqlConnection("Data Source=TANYA-PC;Initial Catalog=biore1;Integrated Security=True")) //here goes connStrng or the variable of it
   {
      sqlConn.Open();
      string sqlQuery = @"SELECT farmername,villagename,gender,farmsize FROM cottonpurchase WHERE farmercode = @code";

      using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
      {
         cmd.Parameters.Add("@code", SqlDbType.Int).Value = intValue;
         using (SqlDataReader reader = cmd.ExecuteReader())
         {;
            if (reader.Read())
            {
               TxtFarmerName.Text = (string)reader[0];
               TxtVillageName.Text = (string)reader[1];
               TxtGender.Text = (string)reader[2];
            }
            else
               MessageBox.Show("For Farmer Code " + intValue.ToString() + " there is no farmer in the database.");
         }
      }
   }
}

i want to fetch txtfarmersize which is int but dont know how to do it please help me?

Does using (SqlDataReader reader = cmd.ExecuteReader()) {; throw a syntax error? is that ; supposed to be there? — Brad
– Brad, Commented Sep 12, 2011 at 13:21
txtfarmersize is the name of a field or the ID of a textbox control on your form? — Claudio Redi
– Claudio Redi, Commented Sep 12, 2011 at 13:23
I feel duty-bound to point out that you are accessing a database directly from UI code, which is a terrible way of doing things. The app will freeze while the DB is being accessed, if there's a DB error your whole app will crash, if you modify your DB query there you'll cause errors unless you remember to keep your DataReader indices in sync.. if this app is to be used by anyone can I suggest some background reading into correct app design? — Kieren Johnstone
– Kieren Johnstone, Commented Sep 12, 2011 at 13:25
@Brad: Just so you know, at least in this example, it would not cause a syntax error. A lone semicolon is just an empty statement. It doesn't really do anything special except for allowing you to use it as a breakpoint. If on the other hand the code was exactly as you have shown without anything else, then that would be a syntax error (unmatched parenthesis). — Jeff Mercado
– Jeff Mercado, Commented Sep 12, 2011 at 19:42

Sam Holder · Accepted Answer · 2011-09-12 13:26:25Z

75

you can use

reader.GetInt32(3);

to read an 32 bit int from the data reader.

If you know the type of your data I think its better to read using the Get* methods which are strongly typed rather than just reading an object and casting.

Have you considered using

reader.GetInt32(reader.GetOrdinal(columnName))

rather than accessing by position. This makes your code less brittle and will not break if you change the query to add new columns before the existing ones. If you are going to do this in a loop, cache the ordinal first.

edited Sep 12, 2011 at 13:26

answered Sep 12, 2011 at 13:21

Sam Holder

33.1k14 gold badges108 silver badges190 bronze badges

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

5 Comments

akd Over a year ago

Is there similar way to get string(varchar) ? for example reader.GetVarchar(reader.getOrdinal(columnName))

akd Over a year ago

Just found it. reader.GetString(reader.GetOrdinal(columnName))

Dan Over a year ago

@RayLoveless You'll get an exception if the value is null. You can use reader.IsDBNull(reader.GetOrdinal(columnName)) to check for a null value and act appropriately.

Dan Chase Over a year ago

This is one of the things that really bugs me about development - and I've been doing it my whole life - the hoops you still have to jump through to properly read an integer. Yes I know, dates and times are even worse. :( just give me my integer, I'm over it. sorry for the rant.

George Birbilis Over a year ago

@DanChase see the answer I just added, could use an extension method for that, would be nice if such where available out of the box

sloth · Accepted Answer · 2012-08-02 11:30:39Z

27

This should work:

txtfarmersize = Convert.ToInt32(reader["farmsize"]);

edited Aug 2, 2012 at 11:30

sloth

101k21 gold badges182 silver badges224 bronze badges

answered Sep 12, 2011 at 13:20

BrokenGlass

161k28 gold badges297 silver badges339 bronze badges

Comments

Jan Christian Selke · Accepted Answer · 2011-09-12 13:20:19Z

6

Use the GetInt method.

reader.GetInt32(3);

answered Sep 12, 2011 at 13:20

Jan Christian Selke

3703 silver badges10 bronze badges

Comments

George Birbilis · Accepted Answer · 2019-01-21 18:40:33Z

6

based on Sam Holder's answer, you could make an extension method for that

namespace adonet.extensions
{
  public static class AdonetExt
  {
    public static int GetInt32(this SqlDataReader reader, string columnName)
    {
      return reader.GetInt32(reader.GetOrdinal(columnName));
    }
  }
}

and use it like this

using adonet.extensions;

//...

int farmsize = reader.GetInt32("farmsize");

assuming there is no GetInt32(string) already in SqlDataReader - if there is any, just use some other method name instead

answered Jan 21, 2019 at 18:40

George Birbilis

2,9782 gold badges38 silver badges38 bronze badges

Comments

as-cii · Accepted Answer · 2011-09-12 13:20:43Z

3

Call ToString() instead of casting the reader result.

reader[0].ToString();
reader[1].ToString();
// etc...

And if you want to fetch specific data type values (int in your case) try the following:

reader.GetInt32(index);

answered Sep 12, 2011 at 13:20

as-cii

13.1k5 gold badges44 silver badges43 bronze badges

1 Comment

phoad Over a year ago

Do you mean casting to String will not call ToString() already?

Guilherme de Jesus Santos · Accepted Answer · 2011-09-12 13:21:35Z

1

TxtFarmerSize.Text = (int)reader[3];

answered Sep 12, 2011 at 13:21

Guilherme de Jesus Santos

5,0936 gold badges45 silver badges69 bronze badges

Collectives™ on Stack Overflow

Reading int values from SqlDataReader

6 Answers 6

5 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related