Generic method To add sql parameters dynamically for executing sql parameterized query for ado.net

Question

How to add sql parameters dynamically for executing sql parameterized query:

let us say i have a querytext :" select a,b,c from table1 where y=@yvalue and
i need to pass this query and the params to one of the db function method and there i have to fetch the data by executing this parameterized query.

at the method call : dbfun.fetchdata(connection ,querytext,params(note:have to send the parameters and values here i.e @xvalue and its value like that here))

at the dbfun class write one method called fetchdata to call the query using parameterized query format.I just wanted to know,how we add those params and values in this method dynamically for adding like

command.parameter.add("@xvalue",its value);
parameter.type=sqldbtype.its data type
parameter.value=some value...

Im interested in knowing the method of adding these params and its values and dbtype dynamically at runtime depending on the param list ..please help on this.

please note that it should be a generic query,so that if you add some extra parameters later to this query,this method should be able to add those params and fetch the data effectively.

Vulpes · Answer

You could do something like this: DataSet FetchData(SqlConnection connection, string queryText, params SqlParameter[] parameters) { // ... command.Parameters.Clear(); // if needed command.Parameters.AddRange(parameters); // ... } and then call it, for example, with something like this: SqlParameter param1 = new SqlParameter("Name", SqlDbType.VarChar); param1.Value = "Mahesh"; SqlParameter param2 = new SqlParameter("Id", SqlDbType.Int); param1.Value = 1; DataSet ds = FetchData(conn, queryText, param1, param2); As the FetchData is using a parameter array ('params' keyword) you can either pass it the parameters individually (as in the above example) or you could create and pass an array instead.

Vulpes · Answer

If you do it like that, then I'm not sure whether the Add method is clever enough to deduce the type of the parameter from the value passed in or whether it always assumes it's SqlDbType.VarChar - the MSDN documentation doesn't say. To be on the safe side, I think it's better therefore to create your SqlParameter objects first and pass them into the method which can just then add them to the Parameters collection.

mahesh kumar B M · Answer

thank you very much.But how ever i have written a method of this format below. Public ExecuteQueryValue( string connectionString, string commandText, Hashtable hash ) { IDictionaryEnumerator en = hash.GetEnumerator(); var i = 0; while (en.MoveNext()) { var command=sqlcommand command; var p = command.CreateParameter(); p.ParameterName = en.Key.ToString(); p.Value = en.Value; command.Parameters.Add(param); } cmd.executequery(......) **************************** at the time of calling the method,its called like below: hasttable ht=new hashtable(); ht.add("@name","mahesh"); ht.add("@age",27); fetchdata(conn, stringtext,ht); ..Then also it works,but here im not specifying the datatype of mahesh and 27 as sqldbtype.varchar or sqldbtype.int ..... because the type of p.value will be of type object when i recieve it as a parameter in the fetchdata method..hence i wont be specifying it as sqldbtype.varchar or sqldbtype.int ... Is it fine if i donot specify the datatype while adding these parameters because this is gonna be a generic query and has to wrk properly for all possible cases.

Sukesh Marla · Answer

Create Your Fucntion like this Public ....ExecuteQuery(string query,Dictionary values) { SqlCommand cmd;..... foreach(string s in values) { cmd.Paramters.AddWithValues(s,Values[s]); } . . . } Check this is correct answer if it helped

mahesh kumar B M · Answer

Question to @@@@ sukesh marla @@@@ thanks...btw if i need to the below parameters like SqlParameter param1 = new SqlParameter("Name", SqlDbType.VarChar); param1.Value = "Mahesh"; SqlParameter param2 = new SqlParameter("Id", SqlDbType.Int); param1.Value = 1; ....in this case how do i pass the params from calling method,as i need to specify name,sqldb.type and value for the sql parameter to add. IF i use ur function,like u have given before ,always im not sure what the addwithvalues() is gonna take the type " sqldb type", as whether always varchar or not...