Store the result of count in a variable in SQL

Question

In this code I want to store the count of rows that are there in the table subscribe into a variable using a SQL query. I can do that using 'select count(*) from subscribe;' but I don't know how to execute multiple sql queries in this code because if I try to execute multiple queries it gives me an exception that you need to close the datareader first. can anyone just help me?

sqlcon.Open();

SqlCommand cmd = new SqlCommand("select email from subscribe", sqlcon);

SqlDataReader da = cmd.ExecuteReader();

string[] arr = new string[4];
int i = 0;

while (da.Read())
{
    arr[i] = da.GetValue(0).ToString();
    Console.WriteLine(arr[i]);
    i++;
}

Answer 1

Attempting to edit the provided sample without having it tested.

You will need to add a second query returning the count. Then you need to use cmd.ExecuteReader() and da.NexResult() to be able to read the count (from second resultset).

sqlcon.Open();

SqlCommand cmd = new SqlCommand("select email from subscribe; select @@rowcount;", sqlcon);

SqlDataReader da = cmd.ExecuteReader();

string[] arr = new string[4];
int i = 0;

while (da.Read())
{
    arr[i] = da.GetValue(0).ToString();
    Console.WriteLine(arr[i]);
}

da.NextResult();

while (da.Read())
{
    i = (int) da.GetValue(0);
    Console.WriteLine($"Count: {i}");
}

Answer 2

Well, if you just needed the count(*), then you could query that. But, since you need both the list of values and also a row count?

Then I would send the results of the data reader to a standard table. That would now give you a nice row list that you can do anything you want with, and also you can get the row count from the table object.

So, this would work:

{
var rstTable = new DataTable();
using (var cmd = new SqlCommand("select email from subscribe", sqlcon))
{
    cmd.Connection.Open();
    rstTable.Load(cmd.ExecuteReader);
}

int EmailCount = rstTable.Rows.Count;
Debug.Print("Rows = " + EmailCount);
// optional show data
foreach (DataRow dr in rstTable.Rows)
    Debug.Print(dr("email"));
}