Sql Parameter function help

jayrod
jayrod used Ask the Experts™
on
Ok I've had it with trying to figure this one out :P

What I need is a generic function that will  accept a t-sql string like the following:

sql = "select * from contest where field = @field and field2 = @field2";

it will then accept a hashtable that contains the values for the parameters like so:

Hashtable ht = new Hashtable();
ht.Add("field", 1);
ht.Add("field2", "String");

So far I've gotten the following two functions.

public void Insert(Hashtable ht)
{
      SqlDataAdapter sd = new SqlDataAdapter();
      sd.InsertCommand = this.myConnection.CreateCommand();
      sd.InsertCommand.CommandText = this.sql;
      this._addParams(sd.InsertCommand, ht);
      sd.InsertCommand.ExecuteNonQuery();

}

private void _addParams( SqlCommand cmd, Hashtable ht)
{
      IDictionaryEnumerator myEnum = ht.GetEnumerator();
      while(myEnum.MoveNext())
      {
            cmd.Parameters.Add("@" + myEnum.Key.ToString(), myEnum.Key.ToString());
            cmd.Parameters["@" + myEnum.Key.ToString()].Value = myEnum.Value.ToString();
      }
}

I believe the second function works ok. It reads from the hashtable and adds the parameters correctly. But the first one is too specific.  I would even accept several functions i.e. one for insert one for select one for delete. But I'd prefer just one.

If anyone has the code out there or could modify this please do.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
For the second function, note that you don't specify the type of the parameters which can be a problem in the future.

For the first one: what do you want to have more? It is a good idea that you put the sql as a second parameter of the function and the connection as a third one. Then what do you want to make next?
Your are to use a SqlCommand, in order to send a sql querry ...

Why don't you try this ?

SqlCommand myCommand = this.myConnection.CreateCommand();
myCommand.CommandText =
        String.Format("select * from contest where ([contest.field1] = {1}) and ([contest.field2] = {2})", 1, String);
To handle sql insert, update and delete statement in one function is a good idea, however ado.net sql select statement may return a few types of object, for example: dataset, datareader, a single constant or xml. If your intention is to generalize, the function lacks of transactional handling and cannot perform command from stored procedure. Write a sample code to explain everything here could be too long, I hope the following links can help you to get better idea to achieve what you want.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
http://aspnet.4guysfromrolla.com/articles/062503-1.aspx

Author

Commented:
great info monday...(I refuse to call you blue boy), I've read the articles that you pointed me to, and I think I'll go down this path, quick question though is could you maybe provide a paradigm or list of steps that I would go through to insert data?

And I was attempting to avoid having to use datasets to update data as well, am I missing something or is there another way to bundle up this data and use it to update a row?

Thank you all for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial