0

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++;
}
  • Have you tried using the SQL [COUNT() function](https://www.w3schools.com/sql/func_sqlserver_count.asp)? `select count(*) from subscribe` – Xerillio Oct 11 '20 at 18:43
  • Does this answer your question? [How to get number of rows using SqlDataReader in C#](https://stackoverflow.com/questions/1383315/how-to-get-number-of-rows-using-sqldatareader-in-c-sharp) – eshirvana Oct 11 '20 at 18:49
  • No actually I have already opened a datareader now it give me an exception when I execute the line ``` var totalRow = cmd.ExecuteScalar(); Console.WriteLine(totalRow);```.That the datareader is already open you need to clode that. – Steven John Oct 11 '20 at 18:56
  • I want to execute multiple SQL queries so how can I do that. – Steven John Oct 11 '20 at 19:08

2 Answers2

0

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}");
}
Cosmin Sontu
  • 1,034
  • 11
  • 16
0

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"));
}
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51