Solved

Sql Parameter function help

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

792 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