sqlhelper

May 11, 2012 at 6:08 PM

I am using the helper to create a dynamic sql command text. When I use the helper to create the helper class, I do not have the sql command text.

How can I create the helper and add parameters, then add the command text later?

 

May 11, 2012 at 6:55 PM

Set the Command property to whatever query you need. However it's going to clear out the parameters. So you're going to have to add any parameters after you set the Command property. Note that until you dispose of the Helper object (or call Close), it's going to keep that connection to the database alive. So what I'll routinely do is create the object and set the initial command text to "" and then run through 4 or 5 commands by setting the Command property, adding my parameters, and calling the appropriate execute function.

May 11, 2012 at 7:26 PM
Edited May 11, 2012 at 7:45 PM

Yes I found that all my parameters are gone after i use the command method, but if I have to add the parameters again may I ask why should I create a "dummy" helper in the first place? 

I am having difficulties adding the parameters as each of them are different dbtype.

anyway, tks for the response, very generous of you for the library.

May 11, 2012 at 10:36 PM

To be honest, I have no idea what you're trying to do without seeing code. But generally speaking using the SQLHelper class usually looks like this:

 

using(SQLHelper Helper=new SQLHelper("SQL Command","ConnectionString",CommandType))
{
      Helper.AddParameter("ParamName",DataValue);
      Helper.ExecuteReader();
      .
      .
      .
      Helper.Command="NewCommand";
      Helper.AddParameter("ParamName",DataValue);
      Helper.ExecuteReader();
      .
      .
      .
      Helper.Command="NewCommand2";
      Helper.AddParameter("ParamName",DataValue);
      Helper.ExecuteReader();
      .
      .
      .
}

As far as dbtypes, you can either use the generic AddParameter that I have above. Those functions will automatically convert them to an appropriate DbType. Or you can use another function AddParameter(string ID,SqlDbType Type, object Value=null,...) or AddParameter(string ID,DbType Type, object Value=null,...) if you're not using SQL Server if you need to set the type yourself.