How to pass multiple parameter dynamically to the SqlCommand.

Question

0.00/5 (No votes)

See more:

Hi,
I want to pass multiple parameters (cmd.Parameters.AddWithValue) to the SqlCommand dynamically.

For example:-
1. some times i want to pass single parameters.
2. some times Parameters may be 2 or more.

Note:- I want to pass multiple parameters in a single Function based on condition.

Here is the Code.
-----------------

C#

private DataSet SelectQuery(string query, string param)
        {
            try
            {
                using (con)
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.Parameters.AddWithValue("@Emp_No", param); // here i want to pass parameters dynamically to the SqlCommand.
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        sda.SelectCommand = cmd;
                        using (DataSet ds = new DataSet())
                        {
                            sda.Fill(ds);
                            con.Close();
                            return ds;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

Posted 17-Jun-15 21:12pm

Merajuddin Ansari

Updated 17-Jun-15 21:20pm

Abhishek Pant

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Praveen_Kumar Gupta · Accepted Answer · 2015-06-17T21:28:00

Solution 1

You Can Change your method like this

C#

private DataSet SelectQuery(string query, params IDataParameter[] sqlParams)
       {
           try
           {
               var con=new SqlConnection(connectionString);
               using (con)
               {
                   con.Open();
                   var cmd = new SqlCommand(query, con);
                   if (sqlParams != null)
                   {
                       foreach (IDataParameter para in sqlParams)
                       {
                           cmd.Parameters.Add(para);
                       }
                   }
                   using (var sda = new SqlDataAdapter())
                   {
                       sda.SelectCommand = cmd;
                       using (DataSet ds = new DataSet())
                       {
                           sda.Fill(ds);
                           con.Close();
                           return ds;
                       }
                   }
               }
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
       }

And Pass multiple parameters

Posted 17-Jun-15 21:28pm

Praveen_Kumar Gupta

Comments

Merajuddin Ansari 18-Jun-15 4:07am

can please tell me how to pass value for params IDataParameter[] sqlParams while calling function (SelectQuery).?

Merajuddin Ansari 18-Jun-15 7:45am

required little bit modification.

SqlConnection con1;
public DataSet fnSelectQuery(string query, SqlParameter[] sqlParams)
{
try
{

using (con1)
{
con1.Open();
var cmd = new SqlCommand(query, con1);
if (sqlParams != null)
{
foreach (SqlParameter para in sqlParams)
{
if(para != null)
cmd.Parameters.Add(para);
}
}
using (var sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
con1.Close();
return ds;
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}

and to pass the parameters
----------------------------
SqlParameter[] parametr =
{
new SqlParameter("@testcol1", SqlDbType.VarChar, 50) { Value = testcol1.ToString() },
new SqlParameter("@testcol2", SqlDbType.VarChar, 50) { Value = testcol2.ToString() }
};

ddl.DataSource = sq.fnSelectQuery("SELECT [test] FROM [testtable] where testcol1 = @testCol1 and testcol2 = @testCol2", "hrms", parametr);
ddl.DataTextField = "test";
ddl.DataValueField = "test";
ddl.DataBind();

[no name] 19-Jun-15 0:10am

IDataParameter is an interface and this accepts several rdbms parameter types
if you pass this as
var parametr =
{
new SqlParameter("@testcol1", SqlDbType.VarChar, 50) { Value = testcol1.ToString() },
new SqlParameter("@testcol2", SqlDbType.VarChar, 50) { Value = testcol2.ToString() }
};
then this will also accepted by IDataParameter .

How to pass multiple parameter dynamically to the SqlCommand.

1 solution

Solution 1

Add your solution here

Preview 0

How to pass multiple parameter dynamically to the SqlCommand.

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us