How to use both parameter and function in SQL Server?

Question

I'm new to C# and parameter passing in SQL but i know it's critical to implement.

The scenario is:

I have a function built in the system already but do not know how to edit it in order to be able to use parameter passing unto it.

My function to edit via INSERT or UPDATE is:

   namespace SQLFunc
   {
   class SQLClass
   {

   public void SQLEdit(string var_SQLCommand)
    {
        using (SqlConnection myConn = new SqlConnection(this.SQLConnectString))
        using (SqlCommand var_command = new SqlCommand(var_SQLCommand, myConn))
        {
            myConn.Open();
            try
            {
                var_command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occurred: " + ex.Message + " using SQL Query: " + var_SQLCommand, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                myConn.Close();
            }
        }
    }
    ...

my common command to use my function are:

   using SQLFunc.SQLClass

   ....

   //command to variable
   var_Command = "UPDATE tbl_Table SET fld_Active = 'YES' WHERE fld_IDNo = " + var_A;

   //execute function
   var_SQLClass.SQLEdit(var_Command);

using parameter, i want to go for code:

   using SQLFunc.SQLClass

   ....

   //command to variable
   var_Command = "UPDATE tbl_Table SET fld_Active = 'YES' WHERE fld_IDNo = @var_A_";

   // need to pass this entire line after the SQLCommand in the function SQLEdit
   var_Command.Parameters.AddWithValue("var_A_", var_A );

   var_SQLClass.SQLEdit(var_Command);

I wanted to be able to utilize parameter passing in function. I could pass the variable var_A but i wanted my code to cater for adaptability like it should work for one field ( update or insert) or even 10 field (update or insert) without changing the code for function every time the number of fields changes.

Is this achievable? If so, how?

You could create a collection of SQLParameters and pass them in to the function. To call the function it would be: var_SQLClass.SQLEdit(var_Command, var_Parameters); then inside the function use the var_Command.Parameters.Add() overload to add the collection. — andrewb
– andrewb, Commented Aug 14, 2013 at 0:34
You mean aside from fldIDNo in your UPDATE command you will have another field? Or you mean to say you could pass multiple values for @var_A_ ? — Edper
– Edper, Commented Aug 14, 2013 at 0:35
@Henry Hughes well a collection is similar to an array, yes. Just build up the collection of parameters that your query has/needs and inside the SQLEdit() function add the collection to the command. Or, do it outside of the function — andrewb
– andrewb, Commented Aug 14, 2013 at 0:52

Ray Saltrelli · Accepted Answer · 2013-08-14 01:21:51Z

Here's how I would do it.

public class SqlClass
{
    public void ExecuteNonQuery(string sqlStatement, Dictionary<string, object> parameters)
    {
        using (SqlConnection connection = new SqlConnection(this.ConnectionString))
        {
            connection.Open();

            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = sqlStatement;

                foreach(var keyValuePair in parameters)
                {
                    command.Parameters.Add(new SqlParameter(keyValuePair.Key, keyValuePair.Value));
                }

                command.ExecuteNonQuery();

            }
        }
    }
}

Your call to SqlClass.ExecuteNonQuery() would look something like this.

Dictionary<string, object> parameters = new Dictionary<string, object>
{
    { "@fld_Active", "Yes" },
    { "@fld_IDNo", 1 }
};

SqlClass sql = new SqlClass();
sql.ExecuteNonQuery("UPDATE tbl_Table SET fld_Active = @fld_Active WHERE fld_IDNo = @fld_IDNo", 
    parameters);

sheauren · Accepted Answer · 2013-08-14 00:55:53Z

1

like it?

public void insert(string sql,Dictionary<stirng,object> parameters){
    using (SqlConnection myConn = new SqlConnection(this.SQLConnectString))
    using (SqlCommand var_command = new SqlCommand(var_SQLCommand, myConn))
    {
        myConn.Open();
        try
        {
            foreach(string name in parameters.Keys){
                var_Command.Parameters.AddWithValue(name, parameters[name] );
            }       
            var_command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show("An error occurred: " + ex.Message + " using SQL Query: " + var_SQLCommand, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            myConn.Close();
        }
    }
}

edited Aug 14, 2013 at 0:55

answered Aug 14, 2013 at 0:38

sheauren

962 silver badges6 bronze badges

4 Comments

fcuesta Over a year ago

List<stirng,object>? you meant Dictionary?

Ace Caserya Over a year ago

How do i implement it? Can you give a sample code using two variable passing?

sheauren Over a year ago

sorry, that's need dictionary for multi-parameters, thanks fcuesta!

Alejandro Over a year ago

Would it be better to use an IEnumerable<DbParameter> instead? That would allow to specify all the parameter's details and datatypes too, and to avoid AddWithValue.

Ben · Accepted Answer · 2013-08-14 01:01:02Z

0

If I understand you correctly, you would like some function SQLEdit(command, /* params */), such that params would "just work" and get plugged in to the right places in your query. Is that so?

If so, it's tricky. See, to use parameterized queries, you have to provide SqlParameter objects. They, in turn, need to know the SQL Server datatype of the argument, which can't be automatically inferred. A DateTime, for instance, might be one of Time, DateTime, or DateTime2. Strings could be char, nchar, varchar, nvarchar, text, ntext... you get the idea.

Your best (if most verbose) bet really is to make params something like params SqlParameter parameters, and build up the individual parameters yourself, where they're used. Trying to build your own mapping algorithms will cost you dearly.

If you want ease of use, use any of the various ORMs out there (Entity Framework, Dapper, etc). If you want the performance of raw SQL, be prepared to write some code.

To make things less verbose, you could write a helper function to create the parameter objects for you, something akin to the following, which I did from memory and haven't tested:

public static SqlParameter Create(string name, SqlDbType type, object value)
{
    var p = new SqlParameter();
    p.ParameterName = name;
    p.SqlDbType = type;

    // If type is a fixed-length type, maybe set the correct string length here

    if (value == null)
    {
        p.SqlValue = DBNull.Value;
    }
    else
    {
        p.SqlValue = value;
    }

    return p;
}

edited Aug 14, 2013 at 1:01

answered Aug 14, 2013 at 0:43

Ben

6,0591 gold badge27 silver badges40 bronze badges

2 Comments

D Stanley Over a year ago

Not even Microsoft recommends Linq to SQL anymore.

Ben Over a year ago

Frankly, whatever MS suggests these days, I tend to prefer the opposite. Entity Framework has a much higher PITA factor, in my experience. Regardless, point taken.

Collectives™ on Stack Overflow

How to use both parameter and function in SQL Server?

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related