Problem with SqlDataReader !
This is in response to the question I asked earlier, since I can't post it there before 8 hours, I've created a new question, with the problem I'm facing,
Here's the code:
if (context.Request.QueryString["id"] != null)
{
int id = int.Parse(context.Request.QueryString["id"].ToString());
string connectionString = "Data Source=tarun-PC\\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True";
string commandString = "Select fsImage from WHATSNSTORE_FEATURED_STORES where imageId=" + id;
SqlConnection oConnection = new SqlConnection(connectionString);
SqlCommand oCommand = new SqlCommand(commandString, oConnection);
if (oConnection.State != ConnectionState.Open)
{
oConnection.Open();
}
SqlDataReader myDataReader = oCommand.ExecuteReader();
//myDataReader.Read();
try
{
if (myDataReader.Read())
{
context.Response.ContentType = "image/jpg";
context.Response.BinaryWrite((byte[])myDataReader["fsImage"]);
}
}
catch (Exception ex)
{
context.Response.Write(ex.Message);
}
My Table
create table WHATSNSTORE_FEATURED_STORES
(
imageId int primary key identity,
fsImage image
)
The only problem now is, while 开发者_开发问答debugging it skips the if(myDataReader.Read())
part, which indicates that there is no data present !
How do I solve the issue?
If your reader is empty it means your query did not return a result.
Looking at your code i'd say the image is missing from the database. You should probably assume that that can and will happen and either stream a blank image to the browser or return a HTTP 404 error.
[Update]
Did you check the generated SQL string? When you add the id to the SQL string it is cast back to a string. If the id is over 1000 you may get a localized representation of the integer (ie. 1.000 instead of 1000). That would explain why your query in SSMS does return a result while the page does not.
Original answer:
I noticed you are using int.Parse
; you may want to use int.TryParse
instead. That will just return false
if the value is not a valid int
.
Additionally, you're building your SQL by string concatenation. This is considered a bad practice. You should use parameters.
Another best practice is to wrap the connection and the reader in a using
statement. That will ensure the connection and reader are closed when they are no longer needed, especially in case of an exception.
Finally, you may want to consider moving the connection string to your web.config file.
You should use oCommand.ExecuteScalar instead of reader since you only want one value. After that you can check if the value returned is null or not.
Are you sure you've got the debugger setup correctly? Are you attaching to process and therefore the deployed .dbg files do not match the .dll files in the debugged bin directory? Have you tried assigning the myDataReader.Read() to a bool to see whether there is any database information present?
精彩评论