Sql Parameter function help

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.
LVL 3
jayrodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TheAvengerCommented:
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?
0
KamranFallahCommented:
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);
0
mondayblueboyCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jayrodAuthor 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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.