Solved

Sql Parameter function help

Posted on 2003-10-23
4
242 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
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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
New to SOAP and need some direction 10 81
C# parent child form 5 28
How can my static class become undefined?? 8 49
EF5: Question about Metadata Artifact Processing 4 15
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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