?
Solved

Sql Parameter function help

Posted on 2003-10-23
4
Medium Priority
?
246 Views
Last Modified: 2010-04-16
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.
0
Comment
Question by:jayrod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9612459
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
 

Expert Comment

by:KamranFallah
ID: 9612518
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
 
LVL 5

Accepted Solution

by:
mondayblueboy earned 2000 total points
ID: 9613273
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
 
LVL 3

Author Comment

by:jayrod
ID: 9614487
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

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question