How to safely and effectively cache ADO.NET commands?

Welcome to Programming Tutorial official website. Today - we are going to cover how to solve / find the solution of this error How to safely and effectively cache ADO.NET commands? on this date .

I want to use this pattern:

SqlCommand com = new SqlCommand(sql, con);
com.CommandType = CommandType.StoredProcedure;//um
com.CommandTimeout = 120;
//com.Connection = con;  //EDIT: per suggestions below

SqlParameter par;
par = new SqlParameter("@id", SqlDbType.Int);
par.Direction = ParameterDirection.Input;


HttpContext.Current.Cache["mycommand"] = com;

Obviously I don’t want to run into odd problems like person A retrieving this from the cache, updating param1, person 2 getting it from the cache and updating param2 and each user running the command with a blend of the two.

And cloning the command taken out of the cache is likely more expensive that creating a new one from scratch.

How thread safe is the ASP.NET Cache? Am I missing any other potential pitfalls? Would this technique work for parameterless commands despite threading issues?

Clarefication: If I want to metaphorically shoot myself in the foot, how do I aim? Is there a way to lock access to objects in the cache so that access is serialized?


I should have asked how to lock an item in ASP.NET cache, instead of saying what I was intending to put in the cache.

    // do something with cache that otherwise wouldn't be threadsafe