jayrod
asked on
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.CreateCo mmand();
sd.InsertCommand.CommandTe xt = this.sql;
this._addParams(sd.InsertC ommand, ht);
sd.InsertCommand.ExecuteNo nQuery();
}
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()].Val ue = 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.
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.CreateCo
sd.InsertCommand.CommandTe
this._addParams(sd.InsertC
sd.InsertCommand.ExecuteNo
}
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()].Val
}
}
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.
Your are to use a SqlCommand, in order to send a sql querry ...
Why don't you try this ?
SqlCommand myCommand = this.myConnection.CreateCo mmand();
myCommand.CommandText =
String.Format("select * from contest where ([contest.field1] = {1}) and ([contest.field2] = {2})", 1, String);
Why don't you try this ?
SqlCommand myCommand = this.myConnection.CreateCo
myCommand.CommandText =
String.Format("select * from contest where ([contest.field1] = {1}) and ([contest.field2] = {2})", 1, String);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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?