CODEWITHSUNDEEP
c#sql
tiffanie
tiffanie

Reputation: 89

Not getting results from database

In my C# code, I'm trying to open a connection to a SQL Server database and get a count. I've copied the code (barring the query itself) from another application I wrote that works fine. I've run the query -- both the version I constructed, and the query the code constructs (I use variables) -- and they both work fine, returning an identical count. I'm just not getting anything back.

I've put in breakpoints and checked my connection and query, and it is what I expect. When I Googled this, I just got results for people having entirely different problems, so I'm stumped. I don't know why I'm not getting anything back. Where is the error?

SqlConnection RRconnection = new SqlConnection();
RRconnection.ConnectionString = "Data Source=;Initial Catalog=;User id=;Password=";

RRconnection.Open();
string ridQuery = "SELECT COUNT (t.RxTimeStamp) FROM...";

SqlCommand query = new SqlCommand(ridQuery, RRconnection);

SqlDataReader data = query.ExecuteReader();

if (data.Read())
    transcount = Convert.ToInt32(data.GetValue(0));

In case the above paragraph wasn't clear, I'm expecting the query to return a count of 1592064 (for the specific instance I'm testing), and instead there's no data in data.

Probably not related, but my query includes three inner joins. It's the first time I've done that many, so maybe that could be the problem? Except if that was causing the problem, it wouldn't work when I run the query?

Upvotes: 2

Views: 1061

Answers (2)

Hüseyin Burak Karadag
Hüseyin Burak Karadag

Reputation: 1888

It is best to use ExecuteScalar if the query returns a single value.

ExecuteScalar; return object value so must be Convert to the correct type

int totalCount = Convert.ToInt32(cmd.ExecuteScalar());

So, to the you question

It's a ExecuteReader don't use to get a Select Count result, but there's no problem this code. You can see the total count value for this code.

Note : The SqlDataReader Object is a stream-based and forward-only retrieval of query results from the Data Source. The Read method processes only one row in memory then overwrites old record when itterate next row.

ExecureReader; In your case , reader first row and first column filled query result, other rows and column set to null.

if(reader.Read())
{
   int totalRows = Convert.ToInt32(reader.GetValue(0));  //convert returned value        
}

Note : use column name and use reader spesific type convert method is my best choice.

reader.GetInt32(reader.GetOrdinal("TotalCount"))

Advice,

  • Don't use ExecuteReader read for single value
  • Use ExecuteScalar then cast result to correct type
  • Use IDisposible objects (Connection , Command , Reader) inside to using code blog. using block close and dispose current object.
  • You should not read using code blog on column index value

Upvotes: 0

Hooman Bahreini
Hooman Bahreini

Reputation: 15559

have you tried using SqlCommand.ExecuteScalar Method

SqlCommand query = new SqlCommand(ridQuery, RRconnection);
Int32 transcount = (Int32) query.ExecuteScalar();

Update

I am not sure why your query did not work, maybe try giving the count column an alias:

string ridQuery = "SELECT COUNT (t.RxTimeStamp) AS RxCount FROM ...";

Upvotes: 3

Related Questions