How can I know or get the Array Size before a SqlDataReader? and return it as a json

Question

I want to know the size of my array to loop just the needed times but I don't know how to do it in an efficient way, the only way that i can think is to put it inside of the reader and count, then use another time the reader and insert the values :( I'm a student

Here is the code

myConnection.Open();
SqlCommand sqlCommand = new SqlCommand(" SELECT * FROM table ", myConnection);

SqlDataReader dr = sqlCommand.ExecuteReader();

Calendar[] loArrayRegistrosCalendario = new Calendar[200]; //200 As an example

if (dr != null)
{
    int i = 0;

    while (dr.Read())
    {
        Calendar loRegistroDia = new Calendar();
        loRegistroDia.User = (dr["user"].ToString());

        loArrayRegistrosCalendario[i] = loRegistroDia;
        i = i + 1;
    }
}

return loArrayRegistrosCalendario;

Why not use a class (like List<>()) that supports a dynamic number of items, then if you have to use an array you can call .ToArray()? — Erik Philips
– Erik Philips, Commented Sep 5, 2014 at 19:21
i'have already do it with list but i need a json object that's why i a create a class :C — Carlos Mtz
– Carlos Mtz, Commented Sep 5, 2014 at 19:22
You can serialize a List to a JSON array as easily as an Array. — Erik Philips
– Erik Philips, Commented Sep 5, 2014 at 19:24
Really? D: I' ve been investigating for 3 hours to know that i need to make a class and do it as i have to have it serialized :s — Carlos Mtz
– Carlos Mtz, Commented Sep 5, 2014 at 19:30
Here is an example where I've serialize both a list and an array, they are exactly the same. Be aware your question was a bad question because it was previous to a recent edit, an XY Problem. — Erik Philips
– Erik Philips, Commented Sep 5, 2014 at 19:36

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

You simply do not need an array at all.

myConnection.Open();

var loArrayRegistrosCalendario = new List<Calendar>();

using (SqlCommand sqlCommand = new SqlCommand(" SELECT * FROM table ", 
  myConnection);)
{

  using (SqlDataReader dr = sqlCommand.ExecuteReader())
  {

    if (dr != null)
    {
      while (dr.Read())
      {
        Calendar loRegistroDia = new Calendar();
        loRegistroDia.User = (dr["user"].ToString());

        loArrayRegistrosCalendario.Add(loRegistroDia);
      }
    }
  }
}

return loArrayRegistrosCalendario;

(As a side note, please dispose of IDisposable object properly, for example the SqlCommand and SqlDataReader should be Disposed of properly. I would recommend using the using() statement for both.)

Serializing an Array of Calendar and List of Calendar will produce the same results: DotNetFiddle Example.

public class Program
{
    public static void Main()
    {
        var myobject = new MyObject();
        myobject.Calendars1 = new List<Calendar>();
        for (DateTime dt = new DateTime(1980,1,1); dt <= new DateTime(1981,1,1); dt = dt.AddMonths(1))
        {
            myobject.Calendars1.Add(new Calendar() { Name =  dt.ToString("MMMM") });
        }
        
        myobject.Calendars2 = myobject.Calendars1.ToArray();
        
        var json1 = JsonConvert.SerializeObject(myobject.Calendars1);
        var json2 = JsonConvert.SerializeObject(myobject.Calendars2);
        
        Console.WriteLine(json1);
        Console.WriteLine(json2);
    }
}

public class MyObject
{
    public List<Calendar> Calendars1 { get; set; }
    public Calendar[] Calendars2 { get; set; }
}

public class Calendar
{
    public string Name { get; set; }
}

Results:

[{"Name":"January"},{"Name":"February"},{"Name":"March"},{"Name":"April"},{"Name":"May"},{"Name":"June"},{"Name":"July"},{"Name":"August"},{"Name":"September"},{"Name":"October"},{"Name":"November"},{"Name":"December"},{"Name":"January"}]

[{"Name":"January"},{"Name":"February"},{"Name":"March"},{"Name":"April"},{"Name":"May"},{"Name":"June"},{"Name":"July"},{"Name":"August"},{"Name":"September"},{"Name":"October"},{"Name":"November"},{"Name":"December"},{"Name":"January"}]

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 5, 2014 at 19:40

Erik Philips

54.9k11 gold badges133 silver badges158 bronze badges

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

3 Comments

AaronLS Over a year ago

List<Calendar[200]> a list of arrays? Is that what you really intended?

Erik Philips Over a year ago

@AaronLS Definitely! :) Thank you.

Carlos Mtz Over a year ago

@Erik Philips Awesome, efficient, complete and pretty answer! thanks a lot :)

Andrew · Accepted Answer · 2014-09-05 19:23:19Z

-1

Just create a SQL command to get the count as follows:

SqlCommand sqlCommand = new SqlCommand(" SELECT Count (*) FROM table ", myConnection);

int rows = sqlCommand.ExecuteScalar();

Calendar[] loArrayRegistrosCalendario = new Calendar[rows];

answered Sep 5, 2014 at 19:23

Andrew

2,3253 gold badges27 silver badges42 bronze badges

4 Comments

Andrew Over a year ago

I'm confused as to the downvotes here. If he creates his array to the size of the data set then it'll only loop for the correct no of items surely...or am I missing something here?

Russ Over a year ago

Perhaps because this adds another call to the DB. Also, what if rows are added or deleted from table between getting the count and selecting the rows?

Andrew Over a year ago

I'd partly agree with your first point, although I'd argue the overhead would be minimal in this example. However the adding/deletion of rows I can't see being an issue unless it happens in the split second between opening the reader and getting the count.

Russ Over a year ago

In my experience, things happen in that split second much more often than one may think. Race conditions can be very difficult to track down and it is best to just avoid creating the possibility of one whenever possible.

Collectives™ on Stack Overflow

How can I know or get the Array Size before a SqlDataReader? and return it as a json

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related