How to pass unknown number of parameters to sql query from C#?

Question

I have a simple query where in FROM I've got something like:

WHERE ([Comment]  LIKE '%something%'
      OR [Comment] LIKE '%aaaa%'
      OR [Commnet] LIKE '%ccc') 
AND Replace([Number], ' ', '') = @number

Unfortunetly this is now hardcoded in code so if anything changes (more OR's on the [Comment] field I have to go and change it in the code. Is it possible to pass [Comment] like a parameter with multiple values (unknown to me) so I would create some SQL Table with every comment I want to use in that query (and users would be able to add new ones from within program so it would be taken care of automatically?

  using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetails)) 
        using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) {
            sqlQuery.Parameters.AddWithValue("@number", varNumber);
            using (var sqlQueryResult = sqlQuery.ExecuteReader())
                while (sqlQueryResult.Read()) {
                    string varID = sqlQueryResult["ID"].ToString();

                }
        }

Oded · Accepted Answer · 2010-08-05 08:13:49Z

2

You can use table value parameters in SQL Server 2008.

For earlier versions, you could pass in an XML parameter and parse the data with XPath.

For good in depth analysis of the different options, read this article by Erland Sommarskog (SQL Server 2005) and the additional article about SQL Server 2008.

answered Aug 5, 2010 at 8:13

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MadBoy Over a year ago

Seems like a great article resources :-) Lots of reading. Thanx

Oliver · Accepted Answer · 2010-08-05 09:09:56Z

1

Even if the solution of Oded seems to be the best way to go (which i also didn't know till now), i currently built up the command and parameters in some way automatically like this (which doesn't currently match your problem, cause you're using like):

// Some sql statement with a placeholder for all parameters (@ID0, @ID1, @ID2, ...)
string sql = "select * from table where ID in ( {0} )";

// Create a list of items of @ID0, 3; @ID1, 8; ...
var parameters = myList.Where(item => item.MatchesSomeCondition())
                       .Select((item, index) => new
                               {
                                   Name = "@ID" + index,
                                   Value = item.ID
                               });

// Add all parameters to the sqlCmd
foreach(parameter in parameters)
{
    sqlCmd.Parameters.AddWithValue(parameter.Name, parameter.Value);
}

// Insert all @IDx into the sql statement
// Result: "select * from table where ID in ( @ID0, @ID1, @ID2, ... )"
sqlCmd.CommandText = String.Format(sql, String.Join(", ", parameters.Select(parameter => parameter.Name).ToArray()

answered Aug 5, 2010 at 9:09

Oliver

45.4k8 gold badges101 silver badges164 bronze badges

2 Comments

MadBoy Over a year ago

What about SQL Injection? Will it not be a problem in this case?

Oliver Over a year ago

@MadBoy: Not really, due to the fact that you work with real parameters. The only what will be "injected" into the statement will be the names of the parameters not their content. And that a parameter can't "break out" is a problem of the SqlCommand class and the sql server itself.

Collectives™ on Stack Overflow

How to pass unknown number of parameters to sql query from C#?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related