CODEWITHSUNDEEP
c#sql-server
Maysam
Maysam

Reputation: 7367

C#: Creating SQLParameter at runtime

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);

Upvotes: 0

Views: 4726

Answers (4)

pwdst
pwdst

Reputation: 13735

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.

Upvotes: 1

Yiğit Yener
Yiğit Yener

Reputation: 5986

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.

Upvotes: 1

AngryHacker
AngryHacker

Reputation: 61596

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.

Upvotes: 2

jgauffin
jgauffin

Reputation: 101130

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.

Upvotes: 4

Related Questions