Build SQL query string using user input

Question

I have to make a string by using the values which the user selects on the webpage,

Suppose I need to display files for multiple machines with different search criteria...

I currently use this code:

DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager
               .ConnectionStrings["DBConnectionString"].ConnectionString;
connection.Open();
SqlCommand sqlCmd = new SqlCommand
  ("SELECT FileID FROM Files
    WHERE MachineID=@machineID and date= @date", connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

sqlCmd.Parameters.AddWithValue("@machineID", machineID);
sqlCmd.Parameters.AddWithValue("@date", date);

sqlDa.Fill(dt);

Now this is a fixed query where the user just has one machine and just selects one date...

I want to make a query in which the user has multiple search options like type or size if he/she wants depending on what he/she selects.

Also if he/she can select multiple machines...

SELECT FileID FROM Files
WHERE (MachineID=@machineID1 or MachineID = @machineID2...)
AND (date= @date and size=@size and type=@type... )

All of this happens at runtime... otherwise I have to create a for loop to put multiple machines one by one... and have multiple queries depending on the case the user selected...

This is quite interesting and I could use some help...

Answer 1

If you are going to do this via dynamic SQL, you need to build a call to the IN function. (e.g. In(id1, id2, id3...)

private string GetSql( IList<int> machineIds )
{
    var sql = new StringBuilder( "SELECT FileID FROM Files Where MachineID In(" );
    for( var i = 0; i < machineIds.Count; i++ )
    {
        if ( i > 0 )
            sql.Append(", ")
        sql.Append("@MachineId{0}", i);
    }

    sql.Append(" ) ");

    //additional parameters to query
    sql.AppendLine(" And Col1 = @Col1" );
    sql.AppendLine(" And Col2 = @Col2 ");
    ...

    return sql.ToString();
}

private DataTable GetData( IList<int> machineIds, string col1, int col2... )
{
    var dt = new DataTable();
    var sql = GetSql( machineIds );
    using ( var conn = new SqlConnection() )
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        using ( var cmd = new SqlCommand( sql, conn ) )
        {
            conn.Open();

            for( var i = 0; i < machineIds.Count; i++ )
            {
                var parameterName = string.Format("@MachineId{0}", i );
                cmd.Parameters.AddWithValue( parameterName, machineIds[i] );
            }

            cmd.Parameters.AddWithValue( "@Col1", col1 ); 
            cmd.Parameters.AddWithValue( "@Col2", col2 ); 
            ...

            using ( var da = new SqlDataAdapter( cmd ) )
            {
                da.Fill( dt );
            }
        }
    }

    return dt;
}