C#: Creating SQLParameter at runtime

Question

I'm curious if it's possible to create a SQLParameter in runtime and add it to SQLCommand or not.

What I'm trying to do is:

public void addParameters(string paramName, paramType, SqlParameter paramCommand)
{
    SqlParameter myParameter = new SqlParameter("@" + paramName, paramType);
    paramCommand.Parameters.Add(myParameter);
}

The problem is passing paramType, it's SqlDbType and I don't know:

1.how to implement/use it here
2.when to calling this function.

NOTE: I just want to add parameters to my SQLCommand object. It's something like this:

SqlParameter myParameter = new SqlParameter("@user", SqlDbType.Char);

jgauffin · Accepted Answer · 2013-01-14 07:38:19Z

Something like this:

public static class CommandExtensions
{
    public static void AddParameter(this IDbCommand command, string name, object value)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (name == null) throw new ArgumentNullException("name");

        var p = command.CreateParameter();
        p.ParameterName = name;
        p.Value = value ?? DBNull.Value;
        command.Parameters.Add(p);
    }
}

which allows you to:

using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM Users WHERE FirstName LIKE @name";
    command.AddParameter("name", "J%");

    // [...]
}

That code is also independent of the ADO.NET driver and can therefore be reused in most projects that use ADO.NET. IF you want more information about writing reusable ADO.NET code, read my article: http://blog.gauffin.org/2013/01/ado-net-the-right-way/

The problem is passing paramType, it's SqlDbType and I don't know

Typically you do not have to specify the db type.

AngryHacker · Accepted Answer · 2013-01-14 07:39:29Z

2

Your approach is correct:

cmd.Parameters.Add(new SqlParameter("@Name", "Frank Rizzo"));

One thing to note is that SqlParameter constructor provides a number of overloads, making creating of a separate function to return new SqlParameter largely unnecessary.

answered Jan 14, 2013 at 7:39

AngryHacker

62.2k112 gold badges364 silver badges636 bronze badges

Comments

Yiğit Yener · Accepted Answer · 2013-01-14 07:42:29Z

1

In addition to jgauffin's answer:

When constructing an SqlParameter, if you are not sure about the SqlDbType of the parameter, you can use the SqlParameter constructor which only takes, parameter name and value as parameters. Namely; SqlParameter Constructor (String, Object).

In order to MSDN documentation,

When you specify an Object in the value parameter, the SqlDbType is inferred from the Microsoft .NET Framework type of the Object.

answered Jan 14, 2013 at 7:42

Yiğit Yener

6,0062 gold badges25 silver badges26 bronze badges

Comments

pwdst · Accepted Answer · 2013-01-14 09:46:01Z

There are a number of ways to add SQL parameters in ADO.NET. Generally it is not necessary to provide a SQL database type as data is sent as a string and implicitly converted by SQL server.

MSDN quote the following examples-

command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
                                                command.Parameters.AddWithValue("@demographics", demoXml);

The first example could also be written as-

command.Parameters.Add("@ID", SqlDbType.Int).Value = customerID;

See http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.100).aspx for more.

Obviously providing data which does not match the SQL data type will cause a SQL exception, you should set length restrictions and validate input, converting it to a compatible type locally using the "as" syntax can be useful as can regular expressions.

jgauffin's answer could easily be adapted to use the syntax that includes the DB type but I'm not sure what the advantage is (except as a reminder to you) as you get no type warnings regardless of values assigned, or even a exception before an attempt to execute the command.

Collectives™ on Stack Overflow

C#: Creating SQLParameter at runtime

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related