DataTable result = new DataTable();
    const string CONNECTION_STRING = "...";
    SqlConnection conn = null;
    SqlDataAdapter adapter = null; 

    string input = "bicchiere plastica";
    string[] paramsArray = input.Split(' ');
    string sql = "select * from prodotti where 1=1 ";

    try
    {
        conn = new SqlConnection(CONNECTION_STRING);
        adapter = new SqlDataAdapter();
        adapter.SelectCommand.Connection = conn;
        int index = 0;
        foreach (string parValue in paramsArray)
        { 
            string parName = "@par_" + index;
            adapter.SelectCommand.Parameters.AddWithValue(parName, parValue);
            sql += string.Format(" AND nome like '%{0}%'", parName);
            index++;
        }
        adapter.SelectCommand.CommandText = sql;
        conn.Open();
        adapter.Fill(result);
        conn.Close();
        //a questo punto dovresti avere 
        //i risultati voluti nel datatable result
    }
    catch 
            
    { 
            
    }
    finally 
    {
        if (adapter != null)
            adapter.Dispose();
        if (conn != null)
            conn.Dispose();
    }