Last post Nov 30, 2015 12:47 PM by PatriceSc
Nov 30, 2015 07:46 AM|Yeoman|LINK
I have an IHttpHandler "ImageProvider" that loads images from a database and writes them out to the HttpResponse. I use SqlCommand.ExecuteScalar to retrieve the image.
There are aspx-pages that use it like this:
There are aspx-pages that contain a lot of images. When I then try to debug the ImageProvider class, my breakpoints are visited again and again. I then cannot step through the code to the end of the method, because the next breakpoint is hit at the beginning
again. Well, I accepted that I cannot debug this code, but it was not a problem because it worked perfectly.
However, I would like to change it, so that it uses SqlCommand.ExecuteReader instead of SqlCommand.ExecuteScalar. As soon as I make this change, I get runtime errors saying that the database connection has not been initialized.
What could be such a difference between ExecuteScalar and ExecuteReadet that could cause those exceptions?
Here is the code:
public void ProcessRequest(HttpContext ctx)
Stream s = loadImage(ctx);
if (s != null)
private Stream loadImage(HttpContext ctx)
Database dbs = Database.ins;
SqlCommand cmd = selectImage(ctx);
Object o = dbs.executeScalar(cmd);
//SqlDataReader rdr = dbs.query(cmd); // this alternative causes exceptions
Object o = rdr.GetValue(0);
MemoryStream s = (o != null) ? new MemoryStream((byte)o) : null;
private void write(Stream stm, HttpResponse rsp)
rsp.ContentType = "image/jpeg";
long n = stm.Length;
byte b = new byte[n];
int k = (int)n;
stm.Read(b, 0, k);
Nov 30, 2015 08:03 AM|vahid bakkhi|LINK
please try below code :
public void ProcessRequest(HttpContext context)
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
string sql = "select largeimage from images_temp where id=@memberid";
SqlCommand cmd = new SqlCommand(sql, myConnection);
int.TryParse(context.Request.QueryString["idx"], out param);
cmd.Parameters.Add("@memberid", SqlDbType.Int).Value = param;
cmd.CommandType = System.Data.CommandType.Text;
SqlDataReader dReader = cmd.ExecuteReader();
context.Response.ContentType = "image/png";
Nov 30, 2015 08:04 AM|oned_gk|LINK
this issue is about sqlconnection and connectionstring, make sure you you provide sqlconnection to cmd
here the sample
SqlConnection conn = new SqlConnection("connection string here");
SqlCommand cmd = new SqlCommand("select ....", conn);
Nov 30, 2015 10:24 AM|PatriceSc|LINK
Do you use a static connection or something like that? For now the code doesn"t show any obvious relation between the cmd and the connection as it seems you initialize the connection on one side and the cmd from an http context with your own layer. I would
suggest to keep a simpler API.
Nov 30, 2015 12:17 PM|Yeoman|LINK
I am doing it exactly as you recommend. The connection is encapsulated in the Database class:
public class Database
private static int semaphore = 0;
private SqlConnection con;
private SqlCommand cmd;
private static int num = 0;
public Database ()
Debug.WriteLine("Database.Database (" + num++ + ")");
public void open()
Debug.WriteLine("Database.open (" + semaphore + ")");
if (semaphore++ > 0)
cns = ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;
con = new SqlConnection(cns);
public void close()
Debug.WriteLine("Database.close (" + (semaphore - 1) + ")");
if (--semaphore > 0)
I am asking myself, why the ExecuteScalar variant works and the ExecuteReader variant doesn't.
Could it be because ExecuteScalar is one single operation while the ExecuteReader variant needs additional calls to SqlDataReader?
Nov 30, 2015 12:47 PM|PatriceSc|LINK
Connection pooling takes care of reusing efficiently connections if possible so just open a connection, use it and close it as soon as possible without trying to optimize this (it has been done already for you). See
With the semaphore (and ++, -- are not even guaranteed to be atomic) you are opening the first connection but as soon as you don't have a unique connection, you won't open the other connections (keep in mind also that for a web app, static means the value
is shared accross ALL users)).
Edit: I see this sometimes but if you have conditional code to open or close connections this is more likely that something is wrong with your design. The basic pattern is just to create, open, use and close a connection each time you need it (even multiple
times for a single http request) and connection pooling takes care of making this efficient reusing the same connection for you if this is possible.